select
    product_name,
    total_sales,
    row_number() over (
        partition by
            category
    ) as category_rank
from
    (
        select
            p.name as product_name,
            sum(quantity) as total_sales,
             p.category as category
        from
            products p,
            orders o
        where
            p.product_id = o.product_id
        group by
            o.product_id
        order by
            p.category asc,
            total_sales desc
    ) t