Partition Pruning with Range Predicates

The events table holds two years of data (6,000 rows) with an index idx_events_date on event_date (text such as '2024-03-15'). Reports usually ask about one month.

A first attempt is:

SELECT COUNT(*) FROM events WHERE strftime('%Y-%m', event_date) = '2024-03';

It is correct but slow: the function hides the column from the index, so SQLite scans all 6,000 rows. Rewrite it so the engine can read only the March slice of the index (the same idea as partition pruning in partitioned databases).

Return the number of events whose event_date is in March 2024.

Constraints

• Return a single count
• Compare event_date itself with a start date and an end date; do not call functions on it
• The plan must be a SEARCH using idx_events_date

Hints