MySQL Running Total By Group, Time Interval

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

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