Rewrite a Date Filter for Index Use
Find events that occurred on February 14, 2025.
- Date analysis
- Filtering
- Sorting
Exercise brief
Understand the request
Database performance analyst A daily event export must preserve index access on the raw timestamp column.
Return every event that occurred on February 14, 2025 without applying a function to occurred_at.
Return
- Return event_id, account_id, occurred_at in this exact left-to-right order.
Constraints
- Use a half-open day range and do not apply a date function to occurred_at.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
filter_events
event_idINTEGERaccount_idINTEGERoccurred_atDATETIME
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
An index-friendly day filter compares the raw timestamp with a start boundary and the next day's start.
Hint 2
Use an inclusive lower bound at midnight on February 14 and an exclusive upper bound at midnight on February 15.
Hint 3
SELECT event_id, account_id, occurred_at FROM filter_events WHERE occurred_at /* lower operator */ '2025-02-14 00:00:00' AND occurred_at /* upper operator */ '2025-02-15 00:00:00' ORDER BY occurred_at;
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT event_id, account_id, occurred_at FROM filter_events WHERE occurred_at >= '2025-02-14 00:00:00' AND occurred_at < '2025-02-15 00:00:00' ORDER BY occurred_at;Why this works
The half-open timestamp range includes midnight, midday, and the final recorded second of February 14 while excluding the exact start of February 15. Unlike `DATE(occurred_at) = ...`, it leaves the filtered column unwrapped, making the predicate eligible for a normal timestamp index. The actual plan still depends on datatype, indexes, statistics, selectivity, parameters, and the engine optimizer; this lab does not promise identical plans. The start and next-day boundary rows make both operators observable and avoid fragile end-of-day precision assumptions.
Success check
Every event from the target calendar day is returned, including both observable time boundaries.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| event_id | account_id | occurred_at |
|---|---|---|
| 3 | 102 | 2025-02-14 00:00:00 |
| 4 | 103 | 2025-02-14 12:30:00 |
| 5 | 104 | 2025-02-14 23:59:59 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
SELECT Statements
Select columns, filter rows, remove duplicates, and order query results.
CASE Statements & Conditional Logic
Build NULL-aware classifications, precedence-safe decisions, flags, scores, and guarded calculations with portable CASE expressions.
Date Operations & Time-Based Analytics
Practice date arithmetic, safe timestamp ranges, calendar bucketing, dense time series, rolling windows, growth, and cohort analysis.
Open the interactive workspace and practice across SQL topics.