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

with
    tt as (
        select
            p.product_id product_id,
            p.product_name product_name,
            sum(s.sales_amount) total_sales_amount,
            sum(s.sales_quantity) total_sales_quantity
        from
            products p
            join sales_records s on p.product_id = s.product_id
        where
            (
                s.sales_date >= '2024-01-01'
                and s.sales_date <= '2024-12-31'
            )
        group by
            1,
            2
    ),
    tt_with_rk as (
        select
            product_id,
            product_name,
            total_sales_amount,
            total_sales_quantity,
            rank() over (
                order by
                    total_sales_quantity desc
            ) as rk
        from
            tt
    )
select
    product_id,
    product_name,
    total_sales_amount,
    total_sales_quantity
from
    tt_with_rk
where
    rk = 1

1. 计算总销售额
2. rank over排名
3. 过滤排名