I have a site that has multiple courses and each course has multiple lessons. I would like to select the highest score per LESSON for a given user_id and course_id ordered by lesson_order. TABLE lesson id course_id lesson_order ---|-----------|-------------- 1…
I am Working on project, where I am using two queries to get the result i wanted to process. Query 1 SELECT MID FROM PMS.MACHINE WHERE STATUS=1 AND MID !=0; Query 2 SELECT MID,COUNT(STATUS) AS QUEUE FROM PMS.QUEUE WHERE STATUS=0…
I'm trying to figure out how to get a sum of value by transaction dates over a 90 day period and repeated over a single year's worth of transaction dates. This is what I have transaction_date value 01-01-2024 20 02-01-2024…
I'm trying to create a SQL script that finds codes in two different databases. I want to set a parameter that has a numeric wildcard. Codes are often written like this for example, ABC108, ABC109, DEF47213, etc. I set the…
I want to join two tables to get the output as shown in Table4. Below are the details: Table1: SalesData Date PlatformId UnitsSold Revenue ChannelId 2024-07-01 ABCD1 12 1200 1 2024-07-02 ABCD1 11 1000 1 2024-07-01 ABCD2 4 1200 2…
I want to ask a question, imagine i have this query: SELECT COUNT(DISTINCT (`patient_id`)) AS `num` FROM `visits` AS `Visit` INNER JOIN `patients` AS `patient` ON `Visit`.`patient_id` = `patient`.`id` WHERE `Visit`.`doctor_id` = 21293 AND `Visit`.`office_id` = 14712 AND `Visit`.`started_at` IS…
I don't know if I'm using the correct terminology, but I'm trying to find the cumulative sum of each node in a tree structure, but the children node only contribute a percentage of its cumulative sum to its parent node's…
Why this: with j(jval) AS ( select JSON_SERIALIZE('{"contract" :{"contract-type" : "P", "contract-id" : 123451, "proposal-no" : "123456", "serial-no" : "serialasd922", "product-code" : "s093", "contract-sign-dt" : "2016-12-01T00:00:00", "contract-start-dt" : "2016-12-09T00:00:00" } }' pretty) from dual ) SELECT json_transform(jval, insert '$.contract[*].x' =…
So I have 2 tables - Videos and Watches (with datetime). I want to order the videos by the last time watched by a given user, with the never before watched videos coming first and the rest being sorted by…
I am new into databases and PostgreSQL and today I wanted to import this dataset: https://www.kaggle.com/datasets/wilmerarltstrmberg/recipe-dataset-over-2m/data into a PostgreSQL so I can make a recipe finder app. I started by creating a new database, then creating a table and then…