Netflix-style Company ChallengeHardVerified answerSQLite live

Monthly Signup Trend

Return month (YYYY-MM), new_users signed up that month, and cumulative_users (running total) ordered by month ascending.

  • Window functions
  • Subqueries
  • Aggregation
  • Date analysis
  • Sorting

Challenge brief

Understand the request

Growth — User Acquisition The growth team monitors cumulative user growth month-over-month to track acquisition momentum and forecast milestones.

Show new signups per month with a running cumulative user total.

Return

  • month
  • new_users
  • cumulative_users

Constraints

  • Group signups by calendar month
  • Calculate a cumulative total in chronological month order
  • Use an explicit row-based frame from the first month through the current month
  • Order by month

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

users

  • user_idINTEGER
  • signup_dateDATE

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

First count new users at month grain.

Hint 2

Apply a cumulative aggregate across chronological months.

Hint 3

Declare the physical frame through the current row and return month order.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH monthly_signups AS (SELECT strftime('%Y-%m', signup_date) AS month, COUNT(*) AS new_users FROM users GROUP BY month), running AS (SELECT month, new_users, SUM(new_users) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_users FROM monthly_signups) SELECT month, new_users, cumulative_users FROM running ORDER BY month

Why this works

The first CTE groups signups by calendar month using strftime. The second CTE applies a running SUM window function ordered by month — this is the classic cumulative total pattern. The result gives the growth team both new-user intake and total platform scale per month.

Success check

Returns the complete deterministic result for monthly signup trend

Expected result

Use this output to verify values, aliases, ordering, and row count.

monthnew_userscumulative_users
2023-0111
2023-0212
2023-0313
2023-0414
2023-0515
2023-0627
2023-0729
2023-08110
2023-09111

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.