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