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

with
tb1 as(
    select sales_records.product_id, product_name, sales_date, sales_amount, sales_quantity 
    from sales_records
    left join products on sales_records.product_id = products.product_id
    where sales_date like '2024%'
),
tb2 as(
    select product_id, product_name, sum(sales_amount) as total_sales_amount, sum(sales_quantity)
        as total_sales_quantity
    from tb1
    group by product_id, product_name
),
tb3 as(
    select *, rank() over(
        order by total_sales_quantity desc
    ) as rank_quantity
    from tb2
)
select product_id, product_name, total_sales_amount, total_sales_quantity from tb3
where rank_quantity = 1