FinOps: full scan for 1 day of events

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Filtering. About 20 minutes. Part of the Pro drill bank.

Ticket from FinOps: 'This dashboard tile only needs 1 day of events, but the query reads the entire events table every time.' On a big table that costs money and time. If the query has a filter on the date, the engine can skip the data it does not need. This is called partition pruning, or predicate pushdown.

Count the clickstream events that happened on 2026-08-15. The current query counts the whole ecommerce_events table. Add a WHERE condition on event_time so that only that 1 day is counted. Return 1 row with a single column, event_count. To pass, the count must be right and the query must pass the efficiency check, which looks for a WHERE on the event date.

Requirements

• Count only events whose event_time is on 2026-08-15.
• Return 1 row with 1 column named event_count.
• The filter must be in a WHERE clause on event_time (or a cast of it).

Constraints

• The day has 24 hours: from 00:00:00 on 2026-08-15 up to, but not including, 2026-08-16.
• Use CAST(event_time AS DATE) = DATE '2026-08-15', or a time range, in the WHERE clause.
• Do not count the whole table and filter afterwards.

Examples

Input: event_id | event_time E00042 | 2026-08-14 22:10:00 E00043 | 2026-08-15 08:05:00 E00044 | 2026-08-16 01:12:00 Output: event_count 46 Why this passes: Only rows whose event_time falls on 2026-08-15 count. The full-table COUNT(*) includes every other day.

Topics: finops, partition prune, filtering.

More SQL interview questions · All interview problems · Learn data engineering