Write a query to return the top 10% of sales reps by revenue generated in Q1.

Answer

Rank sales reps by Q1 revenue as a percentile, then keep those within the top 10%.

Solution

SELECT rep_id, q1_revenue
FROM (
  SELECT rep_id, SUM(revenue) AS q1_revenue,
         PERCENT_RANK() OVER (ORDER BY SUM(revenue) DESC) AS pct_rank
  FROM Sales
  WHERE sale_date BETWEEN '2025-01-01' AND '2025-03-31'
  GROUP BY rep_id
) x
WHERE pct_rank <= 0.10;

Step by Step

1. The inner query sums each rep's revenue during Q1 only, using the date range filter.
2. PERCENT_RANK() OVER (ORDER BY SUM(revenue) DESC) assigns each rep a value from 0 (highest revenue) to 1 (lowest), representing their relative position.
3. Filtering to pct_rank <= 0.10 keeps the top 10% of reps by revenue.

Alternative: NTILE(10) for a Simpler Equal-Bucket Approach

SELECT rep_id, q1_revenue FROM (
  SELECT rep_id, SUM(revenue) AS q1_revenue,
         NTILE(10) OVER (ORDER BY SUM(revenue) DESC) AS decile
  FROM Sales
  WHERE sale_date BETWEEN '2025-01-01' AND '2025-03-31'
  GROUP BY rep_id
) x
WHERE decile = 1;

NTILE(10) splits reps into 10 equal-sized groups and takes the top group - simpler to explain, but (as covered in the percentile question earlier in this set) it's an approximation, not a true percentile calculation, and bucket sizes can be uneven if the rep count doesn't divide evenly by 10.

Key Exam Traps

• PERCENT_RANK() vs NTILE(): PERCENT_RANK() gives an exact relative position for every rep; NTILE() forces reps into equal-sized buckets regardless of how their actual revenue values are distributed - these can disagree at the boundary when revenue is clustered.
• Q1 date range must be explicit: Forgetting the date filter would rank reps by their full-year (or all-time) revenue instead of specifically Q1, silently answering the wrong question.
• Reps with zero Q1 sales: If a rep had no sales at all in Q1, they wouldn't appear in the grouped result unless a LEFT JOIN from a full reps table is used - worth clarifying whether such reps should be included with 0 revenue or excluded entirely.