I am working with the ml100k dataset to write a query that can fetch me the most rated movie for every age. Here's how my tables are defined. users id | age | gender | occupation | zipcode ratings userid…
I am trying to get all the movies from the latest 20 years of the document. I am trying like this but doesnt work. It´s the first time I use mongoDB so I am a bit lost, it may be…
I have table with data: create table measure_app_scalemeasurement ( store_code text, certificate_id text, audit_ending date); insert into measure_app_scalemeasurement values ('K010','vwv', '10.12.2023'), ('K010','cert1','12.12.2023'), ('K054','vwv', '14.12.2023'), ('K054','cert1','20.01.2024'); I want to select min audit_ending date for each market store_code, also getting its corresponding…
I have a query that filters the emails of people who "have a quantity" bigger than the median over all quantities found in the table: SELECT AVG(quantity) AS avg_quantity, email FROM table GROUP BY email HAVING AVG(quantity) > (SELECT PERCENTILE_DISC(0.5)…
Edited to clarify and simplify I have the following query select Signups.user_id, Count(timeoutTable.user_id) as timeoutCount, Count(confirmedTable.user_id) as confirmedCount from Signups left join ( select * from Confirmations where action = 'timeout' ) as timeoutTable on signups.user_id = timeoutTable.user_id left join…
A_id B_id L M N 1 1 1 1 0 1 2 4 3 2 1 3 2 2 1 2 4 2 2 1 2 5 3 2 1 2 6 1 1 0 3 7 4 3 1…
Imagine a table that looks like | sender_ID | Receiver_ID | message_ID | , say table name is **tb** now I want to write an SQL query that gives me : user_id, Total messages exchanged by that user . So…
I am trying to get a single row for each group by clause in the query. Each SELECT output has a json in it. I am trying this: SELECT MIN(C.json_column->'key') AS Key, A.field1, B.field2 FROM json_table AS C LEFT JOIN…
Below is the data table - del_no | Pkt | direction | Env | start_datetimestamp | ---------+---------+------------+-------+---------------------+ H_00002 | 02 | SOUTH | PROD | 2022-10-29 16:20:57 | E20 | 20 | NORTH | PROD | 2022-10-30 16:41:37 | H_00002…
I have table which has store number and Fruits in it like below Store Number fruit 111 Apple 121 Orange 111 Pinable 132 Apple 145 Grapes 121 Apple 111 Apple I want to to add a generated column "Number of…