Top 3 Customers in terms of total amount purchased in each month with rank in each month-CodePudding

I want to trying to find Top 3 Customers name in terms of total amount purchased in each month with rank in each month

from 3 table 1 table

Product

Product_id  product_name product_price
P1           ABC           20
P2           DEF           30

2nd Table

Sales

Cust_id     Date     Quantity    Product_id
C1         1-Jan-21     3           P1
C1         2-Feb-21     4           P2
C2         5-Jan-21     6           P1
C2         7-Jan-21     1           P1
C3         9-feb-21     5           P2

thrid table

Customer

ID     Name
C1     JKL
C2     MNO
C3     OPQ

CodePudding user response：

First calculate the monthly purchase amounts of customers (CTE t) then rank the monthly amounts (CTE r) and finally filter by rank and order.

with t as 
(
 select 
    s.cust_id,
    date_trunc('month', "Date") as period,
    s.quantity * p.product_price as amount 
 from sales s join product p on s.product_id = p.product_id
 group by period, s.cust_id
), 
r as
(
 select *,
    row_number() over (partition by period order by amount desc) as rank
 from t
)
select r.period, c.name as cust_name, r.rank as period_rank
from r join customer c on r.cust_id = c.id
where rank <= 3 
order by r.period, r.rank;

This query can be written less verbose but I think that clarity and readability will suffer.