MySQL Running Total By Group, Time Interval

Question

I would like to take a table of customer orders like this:

customer_id | order_date | amount
0           | 2020-03-01 | 10.00
0           | 2020-03-02 |  2.00
1           | 2020-03-02 |  5.00
1           | 2020-03-02 |  1.00
2           | 2020-03-08 |  2.00
1           | 2020-03-09 |  1.00
0           | 2020-03-10 |  1.00
0           | 2020-03-16 |  1.00

And create a table calculating a cumulative running total by week, segmenting the weeks by 7 days starting at the earliest date (2020-03-01, 2020-03-08, etc.). Something like:

customer_id | week_0 | week_1 |  week_2
0           | 12.00  | 13.00  |  14.00 
1           |  6.00  | 7.00   |   7.00
2           |  0.00  | 2.00   |   2.00

Thanks for the help!

Answer 1

You can use aggregation and window functions (this requires MySQL 8.0). It is easier and more scalable to put the weeks in rows than in columns:

select
    customer_id,
    year_week(order_date) order_week,
    sum(sum(amount)) over(partition by customer_id order by year_week(order_date)) running_amount
from mytable
group by customer_id, year_week(order_date)
order by customer_id, year_week(order_date)

You can pivot this to columns as well - but you need to enumerate the weeks:

select
    customer_id,
    max(case when order_week = 202001 then running_amount end) week_01,
    max(case when order_week = 202002 then running_amount end) week_02,
    max(case when order_week = 202003 then running_amount end) week_03,
    ...
from (
    select
        customer_id,
        year_week(order_date) order_week,
        sum(sum(amount)) over(partition by customer_id order by year_week(order_date)) running_amount
    from mytable
    group by customer_id, year_week(order_date)
) t
order by customer_id