how do I join two tables and then find the find the top 5 custmer_names based on sales

Basically I want the top 5 custmer_name with maximum sales_amount grouped by custmer_name

with results as (
SELECT custmer_name, sales_amount from sales.customers inner join sales.transactions
on sales.customers.customer_code = sales.transactions.customer_code 
)

select *, dense_rank() over(partition by custmer_name order by sales_amount desc) as ranking  from results

customers table

transactions table

Result after join

CodePudding user response：

You should compute the dense rank inside the CTE or subquery, and the filter on it outside:

WITH results AS (
    SELECT c.custmer_name, t.sales_amount,
           DENSE_RAN() OVER (PARTITION BY c.custmer_name
                             ORDER BY t.sales_amount DESC) AS ranking
    FROM customers c
    INNER JOIN transactions t
        ON c.customer_code = t.customer_code 
)

SELECT *
FROM results
WHERE ranking <= 5;