SQLize | PHPize | SQLtest

with all_cte as ( select distinct gr.group_name, y.year, r.rn from mcc_groups gr cross join (select generate_series(2019, 2020) as year) y(year) cross join (select generate_series(1, 3) as rn) r(rn) ) ,rn_cte as ( select mg.group_name, to_char(transaction_date,' YYYY')::int as year, row_number() over (partition by mg.group_name, to_char(transaction_date,' YYYY') order by transaction_value desc) as rn, p.transaction_value from purchases p join mcc_codes mc on p.mcc_code_id = mc.mcc_code_id and p.transaction_date between valid_from and valid_to join mcc_groups mg on mc.group_id = mg.group_id where to_char(transaction_date, 'YYYY') = '2019' or to_char(transaction_date, 'YYYY') = '2020' order by mg.group_name, rn, year ) select a.group_name, a.year, a.rn, r.transaction_value from all_cte a left join rn_cte r on a.group_name = r.group_name and a.year = r.year and a.rn = r.rn order by a.group_name, a.year, a.rn