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

with  t1 as     (
       select  products.product_id,product_name,sum(sales_amount ) as total_sales_amount,
            sum(sales_quantity) as total_sales_quantity
    from products join sales_records
    on products.product_id=sales_records.product_id
    where  sales_date between '2024-01-01' and '2024-12-31'
    group by products.product_id, product_name
),t2 as (

    select  product_id,dense_rank() over (  order by total_sales_quantity desc  ) as rk
    from t1

)
select t1.product_id,product_name,total_sales_amount,total_sales_quantity
from t1 left join  t2
on t1.product_id=t2.product_id
where  rk=1