For each month in 2024, find the top 3 search queries by total search volume. If two queries tie, include all tied queries. Return the month (as the first day of the month), the query, the total number of searches, and the rank.
Schema
search_logs
log_idINTEGER
queryVARCHAR
search_dateDATE
user_idINTEGER
countryVARCHAR
Target output
Target output
| month | query | total_searches | search_rank |
|---|
| 2024-01-01 | iphone 15 | 4 | 1 |
| 2024-01-01 | super bowl | 3 | 2 |
| 2024-01-01 | wordle today | 3 | 2 |
| 2024-01-01 | nba scores | 2 | 3 |
| 2024-02-01 | valentine gifts | 4 | 1 |
| 2024-02-01 | olympics | 3 | 2 |
| 2024-02-01 | oscars 2024 | 2 | 3 |
What this question tests
Tests top-N-per-group with ties, the textbook ranking-function decision. The interviewer is watching which one you pick: ROW_NUMBER drops tied queries, RANK skips positions after a tie, and the prompt as written wants the behaviour only DENSE_RANK gives.
More SQL interview questions
