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

with
    t1 as (
        select
            product_id,
            sum(sales_quantity) as total_sales_quantity,
            sum(sales_amount) as total_sales_amount
        from
            sales_records
        where
            year(sales_date) = 2024
        group by
            product_id
    ),
    t2 as (
        select
            product_id,
            total_sales_amount,
            total_sales_quantity,
            rank() over (
                order by
                    total_sales_quantity desc
            ) as ranks
        from
            t1
    )
select
    t2.product_id,
    product_name,
    total_sales_amount,
    total_sales_quantity
from
    t2
    left join products using(product_id)
where ranks=1;