Top Lessons Each Month

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L5

Asked in

Walmart

Problem

Walking the calendar from the earliest month forward, surface the three most-completed lessons in each month, most completions first, and let the page address break the tie alphabetically whenever two lessons finished level. The content team uses this to guide investment decisions for the next quarter.

Rank items within time periods and keep top 3

Tables

page_views

Columns: view_id INTEGER, page_url TEXT, user_id INTEGER, referrer TEXT, dur_ms INTEGER, device TEXT, viewed_at TEXT

Expected output

Worked solution and explanation

Why this problem exists in real interviews

Notice the gap: the prompt asks about lesson completions, the table is page_views. A row here is a view, not a completion. In a real review you would push for a lesson_completions event source. For this exercise, page_url stands in as the lesson identifier and every row counts as a completion, which is the simplification the query bakes in.

Break down the requirements

Step 1: Bucket views into months

strftime('%Y-%m', viewed_at) returns a sortable text label per month. Aggregating to (month, page_url) collapses 500M rows into a small set keyed by lesson and month.

Step 2: Rank within each month

RANK() OVER (PARTITION BY month ORDER BY completion_count DESC) resets per month. Two lessons tied for second both get rank 2; rank 3 still exists. The next position is 4, not 3.

Step 3: Filter to the top three and order for the consumer

WHERE rnk <= 3 keeps the top tier. The outer ORDER BY month, rnk produces the calendar walk the content team expects to read.

The solution

WITH monthly AS (
  SELECT strftime('%Y-%m', viewed_at) AS month,
         page_url,
         COUNT(*) AS completion_count
  FROM page_views
  GROUP BY 1, 2
),
ranked AS (
  SELECT month,
         page_url,
         completion_count,
         RANK() OVER (PARTITION BY month ORDER BY completion_count DESC) AS rnk
  FROM monthly
)
SELECT month, page_url, completion_count, rnk AS rank
FROM ranked
WHERE rnk <= 3
ORDER BY month, rnk, page_url

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would you change the query to count only views where `dur_ms` exceeds a completion threshold? (Tests moving from a proxy metric to a defensible engagement filter.)
• If the content team only cares about the last six months, what changes in the query plan? (Probes partition pruning on `viewed_at` and whether the predicate belongs in the CTE or outside.)
• How would you return exactly three rows per month even on ties? (Forces the `RANK` vs `ROW_NUMBER` tradeoff and a discussion of fair tiebreakers.)