Google-style Company ChallengeMediumVerified answerSQLite live

Search to Signup Gap

Which users performed a search on the exact same day they signed up?

  • Joins
  • Date analysis
  • Filtering
  • Sorting
  • Distinct values

Challenge brief

Understand the request

Onboarding Analytics is studying immediate post-signup engagement — users who searched on their signup day are considered highly activated.

Return user_id in the declared deterministic order.

Return

  • user_id

Constraints

  • Match searches whose date equals the user signup date
  • Return each matching user once
  • 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

Connect users with their searches through the user key.

Hint 2

Compare the two business dates after the join and deduplicate users.

Hint 3

Return matching identifiers in stable order.

Verified SQL answer

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

Reveal solution and explanation
SELECT DISTINCT u.user_id FROM users u INNER JOIN search_queries s ON u.user_id = s.user_id WHERE u.signup_date = s.search_date ORDER BY u.user_id

Why this works

User 15 signed up 2024-01-05 and has query_id 41 (samba music) on 2024-01-05 — same day. User 17 signed up 2024-01-15 and has query_id 42 (big ben tours) also on 2024-01-15. DISTINCT is needed in case a user searched multiple times that day.

Success check

Returns the complete deterministic result for search to signup gap

Expected result

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

user_id
15
17

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.