Google-style Company ChallengeMediumVerified answerSQLite live

Inactive Users

Which users have not performed any search on or after January 15, 2024?

  • Joins
  • Date analysis
  • NULL handling
  • Filtering
  • Sorting

Challenge brief

Understand the request

Re-engagement Team is targeting lapsed users for a win-back campaign and needs users who have not searched since January 14.

Return user_id in the declared deterministic order.

Return

  • user_id

Constraints

  • Treat 2024-01-15 as inclusive
  • Return users with no search on or after that date, including users with no searches at all
  • Order by user ID

Data you will use

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

users

  • user_idINTEGER
  • countryVARCHAR(50)
  • device_typeVARCHAR(20)
  • signup_dateDATE

search_queries

  • query_idINTEGER
  • user_idINTEGER
  • query_textVARCHAR(200)
  • search_dateDATE

Hints, when you need them

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

Hint 1

Preserve every user while matching only searches inside the active window.

Hint 2

Keep the date condition with the optional match so users without qualifying rows remain visible.

Hint 3

Filter on the missing activity key and order user IDs.

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 LEFT JOIN search_queries s ON u.user_id = s.user_id AND s.search_date >= '2024-01-15' WHERE s.user_id IS NULL ORDER BY u.user_id

Why this works

The date filter in ON narrows which rows JOIN — users with searches only before Jan 15 still appear from the left side with NULL on the right. WHERE s.user_id IS NULL then keeps only those with no Jan-15+ search. All 3 inactive users (21, 22, 23) never searched at all.

Success check

Returns the complete deterministic result for inactive users

Expected result

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

user_id
21
22
23
24

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.