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