题解 | 查询出每个品牌在不同月份的总销售额以及购买该品牌商品的用户的平均年龄

with 
t1 as(
    select
        o.category_id,
        sum(o.order_amount) as total_sales
    from order_details o left join customer_info c
    on o.order_id=c.order_id 
    where o.order_date between '2024-01-01' and '2024-06-30'
    group by o.category_id),
t2 as(
    select
        o.category_id,
        count(c.customer_gender) as male_customers
    from order_details o left join customer_info c
    on o.order_id=c.order_id 
    where c.customer_gender='男' and o.order_date between '2024-01-01' and '2024-06-30'
    group by o.category_id),
t3 as(
    select
        o.category_id,
        count(c.customer_gender) as female_customers
    from order_details o left join customer_info c
    on o.order_id=c.order_id
    where c.customer_gender='女' and o.order_date between '2024-01-01' and '2024-06-30'
    group by o.category_id)

select
    t1.category_id,
    t1.total_sales,
    coalesce(t2.male_customers,0) as male_customers,
    coalesce(t3.female_customers,0) as female_customers
from t1
left join t2 on t1.category_id=t2.category_id
left join t3 on t1.category_id=t3.category_id
order by t1.category_id