CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
SpK
SpK

Reputation: 23

Select the 2 highest count(value) by year

I have a postgresql-9.6 database, let's name it sales, similar as following :

sale_id   customer_id   sale_date    price
1         20            2017-01-05   2000
2         150           2017-05-26   1500
3         121           2017-07-07   2560
4         121           2017-12-25   3000
5         214           2018-02-11   2550
6         17            2018-04-21   2500
7         20            2018-07-01   3000
8         121           2019-07-01   2568

I need to find the top 2 customers every year.

I'm stuck on something like this :

SELECT 
   date_part('year', sale_date) AS year, 
   customer_id, 
   sum(price) AS Total 
FROM 
   sales 
GROUP BY 1,2 
ORDER BY 1,3 DESC 
LIMIT 2

I'm trying to get something like this :

year     customer_id      Total
2017     121              5560
2017     20               2000
2018     20               3000
2018     214              2550
2019     121              2568

Upvotes: 1

Views: 75

Answers (1)

GMB
GMB

Reputation: 222502

You can use aggregation to compute the total sales per customer and year, and then use window function ROW_NUMBER (available since Postgres 9.4) to filter the top 2 customers per year:

SELECT
    sale_year,
    customer_id,
    total_price
FROM (
    SELECT 
        x.*,
        ROW_NUMBER() OVER(PARTITION BY sale_year ORDER BY total_price DESC) rn
    FROM (
        SELECT
            date_part('year', sale_date) AS sale_year, 
            customer_id, 
            sum(price) AS total_price 
        FROM sales
        GROUP BY date_part('year', sale_date), customer_id
    ) x
) y 
WHERE rn <= 2
ORDER BY sales_year, rn

Upvotes: 1

Related Questions