-- Query 9:

--Find the top 2 accounts with the maximum number of unique patients on a monthly basis.

---Note: Prefer the account if with the least value in case of same number of unique patients

--Table Structure:


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);

SELECT z.months, z.account_id,z.unique_patients
from
(SELECT y.months, y.account_id, y.unique_patients, ROW_NUMBER() over(PARTITION by y.months ORDER by y.unique_patients desc, account_id)
as row_num
from(
Select x.months, x.account_id, count(*) as unique_patients
from(
select DISTINCT to_char(date, 'month') as months, account_id, patient_id
from patient_logs)x
Group by x.months, x.account_id)y)z
where z.row_num<3