Google-style Company ChallengeHardVerified answerSQLite live

User Retention

Which users searched on at least one pair of consecutive days?

  • Joins
  • Date analysis
  • Sorting
  • Distinct values

Challenge brief

Understand the request

Search Retention is measuring search habit formation — users who searched on two consecutive days are considered to have formed a search habit.

Return user_id in the declared deterministic order.

Return

  • user_id

Constraints

  • Return users who searched on at least one pair of adjacent calendar dates
  • Return each qualifying user once
  • Order by user ID

Data you will use

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

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

Compare search rows for the same user across adjacent dates.

Hint 2

Avoid returning the same user more than once when several pairs qualify.

Hint 3

Order the qualifying identifiers.

Verified SQL answer

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

Reveal solution and explanation
SELECT DISTINCT s1.user_id FROM search_queries s1 INNER JOIN search_queries s2 ON s1.user_id = s2.user_id AND s2.search_date = DATE(s1.search_date, '+1 day') ORDER BY s1.user_id

Why this works

DATE(search_date, '+1 day') computes the next calendar day. The self-join finds pairs where the same user has searches on consecutive dates. User 1 searched Jan 10 and 11, Jan 11 and 12, etc. — many consecutive pairs qualify.

Success check

Returns the complete deterministic result for user retention

Expected result

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

user_id
1

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.