How to get top 10 items based on category in joined table

user_7290915 0
Viewed 46

I can get the top 1000 selling items from a sales table in a day (and display item info from another table) with the following query:

 select
    cs.item_no, sum(quantity) as quantity, i.item_type, i.item_name, i.group_name
    FROM `sales` cs
    left join 
    (
    SELECT 
                item_no,
                MIN(item_name) as item_name,
                MIN(item_type) as item_type,
                MIN(group_name) as group_name,
                
        FROM 
                `items` 
        WHERE group_name!='UNKNOWN'
        GROUP BY
                item_no 
    ) i
    on cs.item_no = i.item_no 
    where date BETWEEN '2022-08-21' AND '2022-08-22'
    group by item_no,  i.item_type, i.item_name, i.group_name
    order by sum(quantity) desc
    limit 1000

The query displays top 1000 items by quantity sold and also shows the item's type, name and group_name. How can I modify this query so that I can see the top 10 items for each group_name? So instead of top 1000 items overall, I want to see the top 10 items in each group_name.

Thank you

google-bigquery sql
1 Answers

Does below SQL works ? Basically i created a subquery to calculate rank over partition on item group and then ordered by quantity. A filter on rank column will display top 10 records for each group.

select item_no,item_type, item_name, group_name
FROM (
select subq.*, rank() over (partition by group_name order by group_name,quantity desc) as rn 
FROM ( select
    cs.item_no, sum(quantity) as quantity, i.item_type, i.item_name, i.group_name
    FROM `sales` cs
    left join 
    (
    SELECT 
                item_no,
                MIN(item_name) as item_name,
                MIN(item_type) as item_type,
                MIN(group_name) as group_name,
                
        FROM 
                `items` 
        WHERE group_name!='UNKNOWN'
        GROUP BY
                item_no 
    ) i
    on cs.item_no = i.item_no 
    where date BETWEEN '2022-08-21' AND '2022-08-22'
    group by item_no,  i.item_type, i.item_name, i.group_name) subq) outerqry
WHERE rn<=10
user_64305230
Related