How to get the top name for each category from a Table? Postgresql

0
votes

I have 3 Tables named Transactions,Customers and Tickets. Each ticket has a category (Theater,Cinema or Concert). I want to find the name of the person that made the most purchases foreach category:

For example: 

Category | Name |  Purchases
 Theater   George      5
 Cinema     Tom        12
 Cinema     Mary       12
 Concert    John       7

WITH PurchasesSum AS (
    SELECT Category,Customers.Name,COUNT(Transactions.Customer_ID) AS Purchases,
        RANK() OVER (ORDER BY COUNT(Transactions.Customer_ID) DESC) AS Rank
    FROM Transactions
    INNER JOIN Customers
        ON Transactions.Customer_ID= Customers.ID
    INNER JOIN Tickets
    ON Transactions.Ticket_num = Tickets.Ticket_num
    GROUP BY Name,Category
)

SELECT Name, Purchases,Category
FROM PurchasesSum 
ORDER BY Category ASC;

So far I got to this point, but it returns all of them
For example: 

Category | Name |  Purchases
 Cinema     Tom        12
 Cinema     Mary       12
 Cinema     John       3
 Concert    John       7
 Concert    Tom        1
 Theater   George      5
 Theater   Mary        2
2
sqlpostgresql

2 Answers

0
votes

You're very close. Extend the OVER clause of rank() to PARTITION BY category so that the rank is relative to the category. Then put a WHERE clause to the outer query filtering for a rank of one.

WITH
purchasessum
AS
(
SELECT category,
       customers.name,
       count(transactions.customer_id) purchases,
       rank() OVER (PARTITION BY category
                    ORDER BY count(transactions.customer_id) DESC) rank
       FROM transactions
            INNER JOIN customers
                       ON transactions.customer_id = customers.id
            INNER JOIN tickets
                       ON transactions.ticket_num = tickets.ticket_num
       GROUP BY name,
                category
)
SELECT name,
       purchases,
       category
       FROM purchasessum 
       WHERE rank = 1
       ORDER BY category ASC;
0
votes

In Postges, I recommend using DISTINCT ON. Not only is it more concise, but it is often faster:

SELECT DISTINCT ON (Category) Category, c.Name, 
       COUNT(*) AS Purchases,
FROM Transactions t JOIN
     Customers c
     ON t.Customer_ID = c.ID JOIN
     Tickets ti
     ON t.Ticket_num = ti.Ticket_num
GROUP BY Category, c.Name
ORDER BY Category, COUNT(*) DESC;