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 account_id,month,cnt,row_number() over(partition by month order by cnt desc) as rn from ( select account_id,month,count(*) as cnt from ( select distinct account_id,to_char(date,'month') as month,patient_id from patient_logs) x group by account_id,month ) a where a.rn < 3
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear