Microsoft-style Company ChallengeEasyVerified answerSQLite live

Active Subscriptions

Which users have active subscriptions right now, and which products are they subscribed to?

  • Date analysis
  • NULL handling
  • Filtering
  • Sorting

Challenge brief

Understand the request

Subscriptions Operations needs a roster of all currently active subscriptions — those with no end date — to reconcile billing records.

List all active subscriptions (end_date IS NULL) showing user_id and product_id.

Return

  • user_id
  • product_id

Constraints

  • A null end date identifies a currently active subscription
  • Order by user ID and product 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

Active subscriptions have no end date — end_date IS NULL in the subscriptions table. Filter to those rows. No JOIN needed since we only need user_id and product_id.

Hint 2

SELECT user_id, product_id FROM subscriptions WHERE end_date IS NULL.

Hint 3

Build question 4 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 user_id, product_id FROM subscriptions WHERE end_date IS NULL ORDER BY user_id, product_id;

Why this works

end_date IS NULL is the standard pattern for "currently active" in SCD tables. All 12 subscriptions in this dataset are active (no end dates set), so all rows are returned.

Success check

12 active subscriptions — every user in the dataset has exactly one active subscription

Expected result

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

user_idproduct_id
11
21
32
43
52
61
73
82
91
101

Previewing 10 of 12 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.