CODEWITHSUNDEEP
sql
user16717325
user16717325

Reputation: 1

Find top N in each subgroup

I know I am far from being the first person to ask this kind of question, but all the answers I have found are so vague, and I can't figure out how to use them in my very example. It would be very helpful to get specific answer for this question, so I can figure it out effectively. So, I have a database looking like this:

create table client
(
    id int primary key not null, 
    name varchar(100)  not null 

);

create table product
(
    id int primary key not null,
    name varchar(100)  not null

);

create table product_sales
(
    id int primary key not null,
    sale_date date not null,
    id_client int not null,  -- client.id
    id_product int  not null, -- product.id
    total_sum decimal(10, 2) not null 

);

I simplified it significantly for this example. So it is basically the table looking like this: date/client/product/daily_sum. And I need to find the top N purchased products by each client in one year, 2020 for example.

Without any TOP I can make query like this:

SELECT client.name as client_name,
product.name AS product_name,
SUM(product_sale.total_sum) AS total_sum
 
FROM product_sale
INNER JOIN client
ON product_sale.id_client = client.id
INNER JOIN product
ON product_sale.id_product = product.id
WHERE YEAR(product_sale.sale_date) = 2020 
GROUP BY client.name, product.name
ORDER BY client.name ASC, total_sum DESC

And it works fine returning all the yearly sales for each product to each customer. But the condition of getting TOP N for each client is the thing I need help with.

Upvotes: 0

Views: 167

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270793

You can use window functions:

SELECT cp.*
FROM (SELECT c.name as client_name, p.name AS product_name,
             SUM(ps.total_sum) AS total_sum,
             ROW_NUMBER() OVER (PARTITION BY c.name ORDER BY SUM(ps.total_sum) DESC) as seqnum
      FROM product_sale ps JOIN
           client c
           ON ps.id_client = c.id JOIN
           product p
           ON ps.id_product = p.id
      WHERE YEAR(ps.sale_date) = 2020 
      GROUP BY c.name, p.name
     ) cp
WHERE seqnum <= 10;  -- or whatever

Upvotes: 1

Related Questions