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
Telegram: DB developers channel