PostgreSQL Query

Question

PostgreSQL Query

Navigation

#1 by (0 votes)

0

I have a sales table (id, date, store, customer, value), which is the SQL command to return me the 3 largest customers (highest value) of each store. Anyone know?

postgresql

asked by anonymous 12.08.2018 / 00:44

1 answer

Allow only trace number (hyphen), slash and dot in inpult [duplicate] What is the C / C ++ volatile operator?

score 0 · Answer 1

For troubleshooting, follow these steps:

1. Total sales revenue per customer and store

SELECT  loja,
        cliente,
        sum(valor) AS total_vendas
FROM vendas
GROUP BY cliente,
         loja
ORDER BY loja,
         sum(valor) DESC

2. Performed the classification of customers per store

...
row_number() OVER ( 
      PARTITION BY loja
      ORDER BY total_vendas DESC 
) AS rank
....

3.Exhibition of top 3 customers with highest sales per store

SELECT *
FROM
  ( SELECT *,
           row_number() OVER ( PARTITION BY loja
                              ORDER BY total_vendas DESC ) AS rank
   FROM
     ( SELECT loja,
              cliente,
              sum(valor) AS total_vendas
      FROM vendas
      GROUP BY cliente,
               loja
      ORDER BY loja,
               sum(valor) DESC ) AS total_vendas_cliente ) AS top_vendas_loja
WHERE rank <= 3

If you want to display the top 5, top 10 just change the rank condition.