Postgresql Query

Asked

Viewed 46 times

-1

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

postgresql

  • Search by window Function in the documentation. https://www.postgresql.org/docs/current/static/tutorial-window.html

1 answer

1

To solve the problems I followed the following steps:

1.Obtained total sales 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.Held customer rating per store 

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

3.Display of top 3 customers with highest sale 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.

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.