SQL count number partition by id and displaying only top 3 of each id


Hi everyone,

i tried attempting this SQL question but am facing difficulty in this question. particularly counting the number of deals and summing the total deals per merchant. and then i also do not know how to display only the first 3 entries as well.

should this problem be resolved via the partition by function? i have no idea how to resolve this.

subsequently how do i display only the top 3?

>Solution :

You can use the following SQL query to solve this problem:

WITH cte AS (
  SELECT merchant_id, 
         COUNT(*) AS num_deals, 
         SUM(deal_value) AS total_value
  FROM deals
  GROUP BY merchant_id
SELECT merchant_id, num_deals, total_value
  SELECT merchant_id, num_deals, total_value,
         ROW_NUMBER() OVER (ORDER BY num_deals DESC, total_value DESC) AS rn
  FROM cte
) subq
WHERE rn <= 3;


  • The query uses a Common Table Expression (CTE) to pre-compute the
    number of deals and the sum of deal values per merchant.
  • The CTE is
    then joined with another query to select the top 3 merchants based on
    num_deals and total_value. The ranking is done using the ROW_NUMBER
    function, which assigns a unique number to each row within the result
    set. The WHERE clause is used to filter the top 3 rows.

Leave a Reply