Top Query Per Day
For each day, which search query was submitted the most times? When multiple queries tie, show all tied queries.
- Window functions
- Subqueries
- Aggregation
- Date analysis
- Filtering
Challenge brief
Understand the request
Search Trends is building a daily trending topics dashboard and needs the top-ranked query for each day.
Return search_date, query_text in the declared deterministic order.
Return
- search_date
- query_text
Constraints
- Compare query counts within each search date
- Return every query tied for the daily maximum
- Order by date, then query text
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
search_queries
query_idINTEGERuser_idINTEGERquery_textVARCHAR(200)search_dateDATE
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Count each date-and-query combination before comparing daily leaders.
Hint 2
Assign positions within each date while preserving equal maxima.
Hint 3
Retain the leading position and sort by date and query.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH query_counts AS (SELECT search_date, query_text, COUNT(*) AS query_count FROM search_queries GROUP BY search_date, query_text), ranked_queries AS (SELECT search_date, query_text, query_count, RANK() OVER (PARTITION BY search_date ORDER BY query_count DESC) AS rnk FROM query_counts) SELECT search_date, query_text FROM ranked_queries WHERE rnk = 1 ORDER BY search_date, query_textWhy this works
RANK() OVER (PARTITION BY search_date ...) resets the rank counter for each day. Since every query in the dataset is unique (count=1 for all), every query is rank 1 on its day. On days with 2 queries, both get rank 1 (RANK uses ties). On Jan 10, both 'python tutorial' and 'best restaurants near me' appear.
Success check
Returns the complete deterministic result for top query per day
Expected result
Use this output to verify values, aliases, ordering, and row count.
| search_date | query_text |
|---|---|
| 2024-01-05 | samba music |
| 2024-01-10 | best restaurants near me |
| 2024-01-10 | python tutorial |
| 2024-01-11 | weather forecast |
| 2024-01-12 | flights to paris |
| 2024-01-12 | machine learning course |
| 2024-01-13 | sql interview questions |
| 2024-01-14 | best laptop 2024 |
| 2024-01-14 | news today |
| 2024-01-15 | big ben tours |
Previewing 10 of 41 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
Meta
Independent Meta-style social-product, engagement, content, community, messaging, and advertising SQL practice.
Microsoft
Independent Microsoft-style cloud, productivity, subscription, usage, support, and customer analytics SQL practice.
Netflix
Independent Netflix-style streaming, subscription, catalog, ratings, and engagement SQL practice.
Return to the complete interview preparation experience.