Microsoft-style Company ChallengeMediumVerified answerSQLite live

Subscription Start Order

List every subscription record from earliest to latest, including its user and product.

  • Date analysis
  • Sorting

Challenge brief

Understand the request

Finance Operations is auditing subscription onboarding timelines and needs all subscriptions listed in the order they were started.

Show user_id and start_date from subscriptions, ordered by start_date ascending.

Return

  • subscription_id
  • user_id
  • product_id
  • start_date

Constraints

  • Return every subscription record, including repeated subscriptions by the same user
  • Order by start date and subscription ID

Data you will use

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

subscriptions

  • subscription_idINTEGER
  • user_idINTEGER
  • product_idINTEGER
  • start_dateDATE
  • end_dateDATE
  • priceINTEGER

Hints, when you need them

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

Hint 1

All data is in subscriptions. No JOIN or GROUP BY needed. Just SELECT user_id and start_date, then ORDER BY start_date.

Hint 2

SELECT user_id, start_date FROM subscriptions ORDER BY start_date.

Hint 3

Build question 17 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 subscription_id, user_id, product_id, start_date FROM subscriptions ORDER BY start_date, subscription_id;

Why this works

Return subscription-grain identifiers so repeat subscriptions by one user remain distinguishable, then sort by start date and subscription ID for stability.

Success check

12 subscriptions — earliest is user 1 (Jan 1), latest is user 12 (Mar 22)

Expected result

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

subscription_iduser_idproduct_idstart_date
1112024-01-01
2212024-01-10
13122024-01-20
3322024-02-05
4432024-02-15
5522024-03-05
6612024-03-06
7732024-03-10
8822024-03-12
9912024-03-15

Previewing 10 of 13 expected rows. Run the query in the editor to inspect the full result.

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.