-- Query 3: "" (sic)
-- From the screenshots, the query should be the same as query 2,
-- but with years introduced

SELECT
    calendar_year_lookup.year,
    outlet_lookup.shop_name,
    SUM(shop_facts.amount_sold) AS total_amount_sold,
    SUM(shop_facts.quantity_sold) AS total_quantity_sold,
    RANK()
        OVER (
            PARTITION BY calendar_year_lookup.year
            ORDER BY total_amount_sold DESC
        )
        AS amount_rank,
    RANK()
        OVER (
            PARTITION BY calendar_year_lookup.year
            ORDER BY total_quantity_sold DESC
        )
        AS quantity_rank
FROM outlet_lookup
INNER JOIN shop_facts ON shop_facts.shop_code = outlet_lookup.shop_code
INNER JOIN calendar_year_lookup ON shop_facts.week_key = calendar_year_lookup.week_key
GROUP BY calendar_year_lookup.year, outlet_lookup.shop_code
ORDER BY calendar_year_lookup.year, amount_rank;