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

question

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
FROM (
  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;

Explanation:

• 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.