题解 | 哪些产品在特定时间段内表现最为出色

with 
t1 as(
    select
        s.product_id,
        p.product_name,
        sum(s.sales_amount) as total_sales_amount,
        sum(s.sales_quantity) as total_sales_quantity
    from
        sales_records s
    left join
        products p on s.product_id=p.product_id
    where
        s.sales_date between '2024-01-01' and '2024-12-31'
    group by
        s.product_id),
t2 as(
    select
        t1.*,
        dense_rank() over(order by t1.total_sales_quantity desc) as quantity_rank
    from t1)

select
    t2.product_id,
    t2.product_name,
    t2.total_sales_amount,
    t2.total_sales_quantity
from t2
where t2.quantity_rank=1
order by t2.product_id