Top Two Courses per Subject

An online learning platform wants to feature the two highest-earning courses in every subject on its home page.

A course's revenue is the sum of price_paid over its enrollments; a course with no enrollments has revenue 0 and is still eligible. Within each subject rank courses by revenue, highest first. When two courses have the same revenue, the one with the smaller course_id ranks higher, so every subject shows at most two courses.

Return subject, course_title, revenue and subject_rank (1 or 2), ordered by subject then subject_rank. A subject with a single course shows one row. No courses means no rows.

Tables

courses — One row per course in the catalogue.

enrollments — One row per learner enrollment.

Example

courses

enrollments

Expected output

Return the rows in exactly the order described. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Window Functions · problem 40 of 70 · SQLite

The idea

This is the classic top-N per group pattern. Step one turns enrollments into one revenue figure per course. A LEFT JOIN keeps courses with no enrollments, and COALESCE(SUM(...), 0) turns their NULL sum into 0.

Step two numbers the courses inside each subject with ROW_NUMBER() OVER (PARTITION BY subject ORDER BY revenue DESC, course_id). ROW_NUMBER never repeats a number, and adding course_id to the ordering makes the numbering deterministic when revenues tie. Filtering subject_rank <= 2 in an outer query (window functions cannot appear in WHERE) leaves at most two rows per subject.

The alternative computes the same rank without window functions: a course's rank is one plus the number of courses in the same subject that beat it, either on revenue or on a smaller id at equal revenue.

Solution

WITH course_revenue AS (
  SELECT c.course_id,
         c.subject,
         c.title,
         COALESCE(SUM(e.price_paid), 0) AS revenue
  FROM courses AS c
  LEFT JOIN enrollments AS e ON e.course_id = c.course_id
  GROUP BY c.course_id, c.subject, c.title
),
ranked AS (
  SELECT subject, title, revenue,
         ROW_NUMBER() OVER (
           PARTITION BY subject
           ORDER BY revenue DESC, course_id
         ) AS subject_rank
  FROM course_revenue
)
SELECT subject,
       title AS course_title,
       revenue,
       subject_rank
FROM ranked
WHERE subject_rank <= 2
ORDER BY subject, subject_rank;

Another way

WITH course_revenue AS (
  SELECT c.course_id, c.subject, c.title,
         (SELECT COALESCE(SUM(e.price_paid), 0)
          FROM enrollments AS e
          WHERE e.course_id = c.course_id) AS revenue
  FROM courses AS c
),
ranked AS (
  SELECT r.subject, r.title, r.revenue,
         1 + (SELECT COUNT(*)
              FROM course_revenue AS o
              WHERE o.subject = r.subject
                AND (o.revenue > r.revenue
                     OR (o.revenue = r.revenue AND o.course_id < r.course_id))) AS subject_rank
  FROM course_revenue AS r
)
SELECT subject, title AS course_title, revenue, subject_rank
FROM ranked
WHERE subject_rank <= 2
ORDER BY subject, subject_rank;

Why common answers fail

RANK gives tied courses the same rank, so a tie can return three or more courses for one subject and ignores the course_id tie-break.

WITH course_revenue AS (
  SELECT c.course_id, c.subject, c.title, COALESCE(SUM(e.price_paid), 0) AS revenue
  FROM courses AS c LEFT JOIN enrollments AS e ON e.course_id = c.course_id
  GROUP BY c.course_id, c.subject, c.title
), ranked AS (
  SELECT subject, title, revenue,
         RANK() OVER (PARTITION BY subject ORDER BY revenue DESC) AS subject_rank
  FROM course_revenue
)
SELECT subject, title AS course_title, revenue, subject_rank
FROM ranked WHERE subject_rank <= 2
ORDER BY subject, subject_rank;

An inner join drops courses with no enrollments, so a subject can lose a zero-revenue course that should fill a top-two slot.

WITH course_revenue AS (
  SELECT c.course_id, c.subject, c.title, SUM(e.price_paid) AS revenue
  FROM courses AS c JOIN enrollments AS e ON e.course_id = c.course_id
  GROUP BY c.course_id, c.subject, c.title
), ranked AS (
  SELECT subject, title, revenue,
         ROW_NUMBER() OVER (PARTITION BY subject ORDER BY revenue DESC, course_id) AS subject_rank
  FROM course_revenue
)
SELECT subject, title AS course_title, revenue, subject_rank
FROM ranked WHERE subject_rank <= 2
ORDER BY subject, subject_rank;

Without COALESCE, SUM over no enrollments is NULL rather than 0, so unsold courses report NULL revenue.

WITH course_revenue AS (
  SELECT c.course_id, c.subject, c.title, SUM(e.price_paid) AS revenue
  FROM courses AS c LEFT JOIN enrollments AS e ON e.course_id = c.course_id
  GROUP BY c.course_id, c.subject, c.title
), ranked AS (
  SELECT subject, title, revenue,
         ROW_NUMBER() OVER (PARTITION BY subject ORDER BY revenue DESC, course_id) AS subject_rank
  FROM course_revenue
)
SELECT subject, title AS course_title, revenue, subject_rank
FROM ranked WHERE subject_rank <= 2
ORDER BY subject, subject_rank;

Breaks ties in the wrong direction (largest course_id first), which the statement rules out.

WITH course_revenue AS (
  SELECT c.course_id, c.subject, c.title, COALESCE(SUM(e.price_paid), 0) AS revenue
  FROM courses AS c LEFT JOIN enrollments AS e ON e.course_id = c.course_id
  GROUP BY c.course_id, c.subject, c.title
), ranked AS (
  SELECT subject, title, revenue,
         ROW_NUMBER() OVER (PARTITION BY subject ORDER BY revenue DESC, course_id DESC) AS subject_rank
  FROM course_revenue
)
SELECT subject, title AS course_title, revenue, subject_rank
FROM ranked WHERE subject_rank <= 2
ORDER BY subject, subject_rank;

What the interviewer is checking

Checks that you know the top-N-per-group pattern and can choose between ROW_NUMBER, RANK and DENSE_RANK based on how the business wants ties handled.

Mistakes to avoid

• Using RANK or DENSE_RANK when the spec says exactly two rows per group.
• Inner-joining enrollments and losing unsold courses.
• Filtering on the window column in the same SELECT's WHERE clause, which is not allowed.
• Leaving the tie-break out, so results change between runs.

Follow-ups to expect

• Show every course tied for the top two revenue values instead (DENSE_RANK() <= 2).
• Return the top 2 per subject as one comma-separated line per subject (group_concat over the ranked rows, ordered by rank).
• Add each course's share of its subject's revenue (revenue * 100.0 / SUM(revenue) OVER (PARTITION BY subject)).

In real work

Merchandising pages, "best sellers per category" widgets and per-region leaderboards all run this query, and their tie rules decide what customers actually see.

Practice next