select
        name as product_name,
        sum_quantity as total_sales,
        row_number() over(partition by category order by sum_quantity desc) category_rank
from (
    select
        product_id,
        sum(quantity) as sum_quantity
    from 
            orders
    group by 
            product_id
)a
inner join 
        products as pd
on 
        a.product_id = pd.product_id
order by 
        category,
        sum_quantity desc