SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
drop table patient_logs; create table patient_logs ( account_id int, date date, patient_id int ); insert into patient_logs values (1, to_date('02-01-2020','dd-mm-yyyy'), 100); insert into patient_logs values (1, to_date('27-01-2020','dd-mm-yyyy'), 200); insert into patient_logs values (2, to_date('01-01-2020','dd-mm-yyyy'), 300); insert into patient_logs values (2, to_date('21-01-2020','dd-mm-yyyy'), 400); insert into patient_logs values (2, to_date('21-01-2020','dd-mm-yyyy'), 300); insert into patient_logs values (2, to_date('01-01-2020','dd-mm-yyyy'), 500); insert into patient_logs values (3, to_date('20-01-2020','dd-mm-yyyy'), 400); insert into patient_logs values (1, to_date('04-03-2020','dd-mm-yyyy'), 500); insert into patient_logs values (3, to_date('20-01-2020','dd-mm-yyyy'), 450); -- Find the top 2 accounts with the maximum number of unique patients -- on a monthly basis. SELECT month, account_id,cnt as no_of_unique_patients FROM ( SELECT account_id, month, cnt, ROW_NUMBER() OVER (PARTITION BY month ORDER BY cnt DESC) AS rn FROM ( SELECT account_id, to_char(date, 'month') AS month, COUNT(DISTINCT patient_id) AS cnt FROM patient_logs GROUP BY account_id, month ) x ) a WHERE rn < 3
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear