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

with
t1 as(
    select
        product_id,
        product_name,
        sum(sales_amount) as total_sales_amount,
        sum(sales_quantity) as total_sales_quantity
    from
        sales_records left join products using(product_id)
    where
        sales_date between '2024-01-01' and '2024-12-31'
    group by
        product_id,
        product_name
)
,
t2 as(
    select
        product_id,
        product_name,
        total_sales_amount,
        total_sales_quantity,
        rank()over(order by total_sales_quantity desc) as tsqrank
    from
        t1
)

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