Microsoft-style Company ChallengeHardVerified answerSQLite live

Power Microsoft Users

Which users appear in all three activity dimensions: subscriptions, usage logs, AND support tickets?

  • Subqueries
  • Filtering
  • Sorting

Challenge brief

Understand the request

Account Management is identifying the most engaged Microsoft customers — those actively using products, subscribed, and reaching out for support — for a VIP relationship programme.

Find users present in all three tables by joining all four tables (users + 3 activity tables).

Return

  • user_id

Constraints

  • A qualifying user must have at least one subscription, usage log, and support ticket
  • Return each user once in user-ID order
  • Independent activity dimensions must not multiply one another

Data you will use

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

users

  • user_idINTEGER
  • countryTEXT
  • signup_dateDATE

subscriptions

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

usage_logs

  • log_idINTEGER
  • user_idINTEGER
  • product_idINTEGER
  • usage_dateDATE
  • usage_minutesINTEGER

support_tickets

  • ticket_idINTEGER
  • user_idINTEGER
  • product_idINTEGER
  • created_dateDATE
  • statusTEXT

Hints, when you need them

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

Hint 1

INNER JOIN users to all three activity tables on user_id. INNER JOIN automatically excludes users missing from any table. DISTINCT prevents duplicate rows if a user has multiple records in any table.

Hint 2

FROM users u JOIN subscriptions s ON u.user_id=s.user_id JOIN usage_logs ul ON u.user_id=ul.user_id JOIN support_tickets st ON u.user_id=st.user_id. SELECT DISTINCT u.user_id.

Hint 3

Build question 25 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 u.user_id FROM users u WHERE EXISTS (SELECT 1 FROM subscriptions s WHERE s.user_id = u.user_id) AND EXISTS (SELECT 1 FROM usage_logs ul WHERE ul.user_id = u.user_id) AND EXISTS (SELECT 1 FROM support_tickets st WHERE st.user_id = u.user_id) ORDER BY u.user_id;

Why this works

Test each activity dimension independently with existence predicates. This expresses intersection membership directly without constructing and deduplicating a multiplied three-fact join.

Success check

12 users — all users in this dataset are in all three activity tables

Expected result

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

user_id
1
2
3
4
5
6
7
8
9
10

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.