Google-style Company ChallengeEasyVerified answerSQLite live

Searches by User

How many searches has each user performed in total?

  • Aggregation
  • Sorting

Challenge brief

Understand the request

Search Analytics is profiling per-user search behaviour to identify power users and detect query-volume anomalies.

Return user_id, search_count in the declared deterministic order.

Return

  • user_id
  • search_count

Constraints

  • Return only users who have searched
  • Count all search records per user
  • Order by search count descending, then 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

Search events already carry the user key.

Hint 2

Aggregate records at user grain.

Hint 3

Use the count and user key as the two ordering levels.

Verified SQL answer

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

Reveal solution and explanation
SELECT user_id, COUNT(*) AS search_count FROM search_queries GROUP BY user_id ORDER BY search_count DESC, user_id

Why this works

GROUP BY user_id creates one group per user who searched. Users 21, 22, 23 don't appear because they have no rows in search_queries. User 1 searched 14 times — the most by far.

Success check

Returns the complete deterministic result for searches by user

Expected result

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

user_idsearch_count
114
24
44
32
52
62
152
172
71
81

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