select product_id,product_name,total_sales_amount,total_sales_quantity
from 
(
    select product_id,product_name,sum(sales_amount) as total_sales_amount,sum(sales_quantity) as total_sales_quantity,
    rank() over (order by sum(sales_quantity) desc) as rk
from products
join sales_records using(product_id)
where sales_date between '2024-01-01' and '2024-12-31'
group by product_id,product_name
order by product_id
) a 
where rk =1