Q2 Search Volume
An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.
- Domain
- SQL
- Difficulty
- easy
- Seniority
- L3
- Asked in
- Meta
Problem
How many queries in search_queries were received from April 1 through June 30 2026, the last day included, for the search team's Q2 benchmark against the prior quarter?
Q2 search volume. The numbers.
Tables
search_queries
Columns: query_id INTEGER, user_id INTEGER, search_term TEXT, results_count INTEGER, clicked_result INTEGER, query_time TEXT
| query_id | user_id | search_term | results_count | clicked_result | query_time |
|---|---|---|---|---|---|
| 2071 | 100 | laptop deals | 17 | 2 | 2026-02-02 01:11:00 |
| 2142 | 197 | phone case iphone 15 | 34 | 3 | 2026-03-03 02:22:00 |
| 2213 | 294 | usb-c cable 6ft | 51 | NULL | 2026-04-04 03:33:00 |
| 2284 | NULL | gaming mouse | 68 | 5 | 2026-05-05 04:44:00 |
| 2355 | 488 | mechanical keyboard | 85 | 6 | 2026-06-06 05:55:00 |
| 2426 | 585 | 4k monitor | 102 | NULL | 2026-07-07 06:06:00 |
| 2497 | 682 | standing desk | 119 | 8 | 2026-08-08 07:17:00 |
| 2568 | NULL | webcam 1080p | 136 | 9 | 2026-09-09 08:28:00 |
| 2639 | 876 | bluetooth speaker | 153 | NULL | 2026-01-10 09:39:00 |
| 2710 | 973 | smart watch | 170 | 1 | 2026-02-11 10:50:00 |
Expected output
| q2_volume |
|---|
| 40 |
Worked solution and explanation
What this really is
This is a timestamp boundary problem dressed up as a quarterly count. The COUNT(*) is free. The skill is bounding query_time, which carries a time of day, so that "through June 30 inclusive" keeps all of June 30. The reflex is BETWEEN '2026-04-01' AND '2026-06-30', and that upper bound means midnight: every query stamped later that day drops out, and the quarter quietly loses most of a day with no error to warn you.
This sample happens to hold no queries late on June 30, so here the shortcut and the correct filter return the same number. A production log will not be so kind, which is why the interviewer listens to how you write the bound and not just whether the count matches.
Building it
Step 1: Read the column before the calendar
query_time holds values like '2026-04-04 03:33:00': a date plus a time. Any bound written as a bare date therefore compares against the very first instant of that day, not the whole day.
Step 2: Close the range at the next quarter's start
query_time >= '2026-04-01' AND query_time < '2026-07-01' is inclusive at midnight on April 1 and excludes exactly July 1. Every instant of June 30 sits between them, whatever precision the timestamps carry.
Step 3: Count rows, not users
Volume is every received query. A quarter of the rows have a NULL user_id (signed-out searches), so COUNT(user_id) would understate the total. COUNT(*) counts the rows themselves.
The solution
SELECT COUNT(*) AS q2_volume
FROM search_queries
WHERE query_time >= '2026-04-01'
AND query_time < '2026-07-01'- Closed range on a date
BETWEEN '2026-04-01' AND '2026-06-30'stops at '2026-06-30 00:00:00'. Stored as text, '2026-06-30 14:05:00' also sorts after '2026-06-30', so either way the last day is cut to its midnight.- Half-open range
>= '2026-04-01'and< '2026-07-01'hold every instant of the quarter, and the same pair of bounds works unchanged for any quarter you plug in.
Common follow-up questions
- How would you return Q1 and Q2 side by side so the benchmark is a single query? (Conditional counting with the same half-open bounds for each quarter.)
- If `query_time` is stored in UTC but the quarter is defined in Pacific time, what changes? (The boundaries shift by the offset, and a naive cast moves queries across quarters.)
- A client retry can log the same search twice. How would you keep that from inflating `q2_volume`? (Deduplicating on a stable key such as `query_id` before counting.)