-- Query 4: amount sold for shop_code 351

(
    SELECT
        calendar_year_lookup.year,
        calendar_year_lookup.month,
        SUM(shop_facts.amount_sold) AS "amount sold",
        SUM(SUM(shop_facts.amount_sold))
            OVER(ORDER BY calendar_year_lookup.week_key)
            AS "running amount sold",
        SUM(SUM(shop_facts.amount_sold))
            OVER(
                PARTITION BY calendar_year_lookup.year
                ORDER BY calendar_year_lookup.week_key
            )
            AS "running year amount sold"
    FROM shop_facts
    INNER JOIN calendar_year_lookup ON shop_facts.week_key = calendar_year_lookup.week_key
    WHERE shop_facts.shop_code = 351
    GROUP BY calendar_year_lookup.year, calendar_year_lookup.month
    ORDER BY calendar_year_lookup.year, calendar_year_lookup.month
) UNION (
    SELECT
        NULL AS "year",
        NULL AS "month",
        SUM(shop_facts.amount_sold) AS "amount sold",
        -- Because we are computing the last row by hand (grouping sets being unavailable in MySQL),
        -- the last row's running amount is defined manually here. I have chosen to set it to the total sum
        SUM(shop_facts.amount_sold) AS "running amount sold",
        SUM(shop_facts.amount_sold) AS "running year amount sold"
    FROM shop_facts
    WHERE shop_facts.shop_code = 351
);

/* == TEST ==
assert(result[12]["running amount sold"] === 807190.1);
assert(result[12]["running year amount sold"] === 85616.4);
assert(result.length === 37);
assert(result[36]["amount sold"] === 3258640.5);
*/