Microsoft-style Company ChallengeBeginnerVerified answerSQLite live

Monthly User Signups

How many users signed up each month?

  • Aggregation
  • Date analysis
  • Sorting

Challenge brief

Understand the request

Growth Analytics is building a monthly acquisition dashboard and needs user signup counts broken down by calendar month.

Group users by signup month using strftime and count registrations per month.

Return

  • signup_month (YYYY-MM format)
  • user_count

Constraints

  • Group signups by calendar month
  • Order months chronologically

Data you will use

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

users

  • signup_dateDATE

Hints, when you need them

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

Hint 1

strftime('%Y-%m', signup_date) extracts the year-month as a string like '2024-01'. GROUP BY that expression and COUNT users per month.

Hint 2

SELECT strftime('%Y-%m', signup_date) AS signup_month, COUNT(*) AS user_count FROM users GROUP BY signup_month ORDER BY signup_month.

Hint 3

Build question 27 from the required result grain: choose the driving table, add only the joins and filters needed for that grain, then apply aggregation and deterministic ordering.

Verified SQL answer

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

Reveal solution and explanation
SELECT strftime('%Y-%m', signup_date) AS signup_month, COUNT(*) AS user_count FROM users GROUP BY signup_month ORDER BY signup_month

Why this works

Extract a calendar-month key, aggregate users at that grain, and order the normalized month keys chronologically.

Success check

3 months — Jan (2 users), Feb (2 users), Mar (8 users). March was the strongest acquisition month.

Expected result

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

signup_monthuser_count
2024-012
2024-022
2024-038
2024-041

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.