题解 | 获取指定客户每月的消费额

with
    Tom_id as(
        select c_id as tom_id
        from customer
        where c_name='Tom'
    ),
    Tom_trade_record as(
        select t_amount, DATE_FORMAT(t_time, '%Y-%m') as time
        from trade
        where t_cus in (
            select tom_id
            from Tom_id
        )
        and t_time between '2023-01-01' and '2023-12-31'
        and t_type=1
    ),
    Tom_month_total_consumption_amount as(
        select time, sum(t_amount) as total
        from Tom_trade_record
        group by time
    )
select time, total
from Tom_month_total_consumption_amount
order by time asc