# Event Count on Key Days > Key days. Key event volumes. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem The infrastructure team is sizing weekend versus weekday capacity and wants to start with a simple comparison. Show the total number of events that occurred on Saturdays versus Mondays, with the day number and event count for each. ## Worked solution and explanation ### Why this problem exists in real interviews Capacity-planning queries probe two reflexes at once: do you know the day-of-week convention your engine uses (SQLite is Sunday=0), and do you spot that wrapping `event_timestamp` in `strftime` defeats partition pruning on a 150M row table? Both decisions surface in the first sentence you should say out loud. --- ### Break down the requirements #### Step 1: State the convention Say out loud: `strftime('%w', ts)` returns a string `'0'`-`'6'` with Sunday=0, Monday=1, Saturday=6. Cast to integer so `IN (1, 6)` is type-clean. #### Step 2: Filter to the two days `WHERE CAST(strftime('%w', event_timestamp) AS INTEGER) IN (1, 6)`. Use `IN`, not two `OR` branches: clearer intent and identical plan. #### Step 3: Group by the same expression Project the cast expression as `day_of_week`, then `GROUP BY day_of_week`. SQLite resolves the alias in `GROUP BY`; other engines may require the full expression repeated. #### Step 4: Aggregate `COUNT(*)` per bucket. Two output rows: one for Monday, one for Saturday. No `ORDER BY` was asked for, so don't invent one. --- ### The solution **EVENT COUNT ON KEY DAYS** ```sql SELECT CAST(strftime('%w', event_timestamp) AS INTEGER) AS day_of_week, COUNT(*) AS event_count FROM event_data WHERE CAST(strftime('%w', event_timestamp) AS INTEGER) IN (1, 6) GROUP BY day_of_week ``` > **Cost Analysis** > > 150M rows, partitioned on `event_timestamp`. Wrapping the partition key in `strftime` blocks pruning, so the planner does a full scan. Hash aggregate on 2 buckets is free; scan dominates. Production fix: materialize a `day_of_week SMALLINT` generated column inside the partition and filter on that. > **Interviewers Watch For** > > Ask before writing: which day-of-week convention does the engine use? SQLite and Postgres `EXTRACT(DOW ...)` agree (Sunday=0). MySQL `DAYOFWEEK` is 1-indexed Sunday=1. Snowflake is configurable. Name the convention before you write or you'll get bitten on the off-by-one. > **Common Pitfall** > > `strftime` returns text, so `WHERE strftime('%w', event_timestamp) IN (1, 6)` silently matches zero rows: `'1'` ne `1`. Either cast to integer on both sides or compare to `'1', '6'` as strings. Pick one and stay consistent across the `SELECT` and `WHERE`. --- ### COMMON FOLLOW-UP QUESTIONS ## Common follow-up questions - How would you rewrite this to preserve partition pruning on `event_timestamp`? _(Tests whether you reach for a generated `day_of_week` column or a range-rewrite (one `BETWEEN` per target week).)_ - Bucket events by hour-of-day for Saturday only. _(Tests reusing the same `strftime` pattern with `%H` and combining a where-filter with a different group key.)_ - Compute the Saturday-to-Monday ratio in a single query. _(Tests conditional aggregation: `SUM(CASE WHEN day_of_week = 6 THEN 1 END) * 1.0 / SUM(CASE WHEN day_of_week = 1 THEN 1 END)`.)_ - What changes on Postgres versus MySQL versus Snowflake? _(Tests cross-engine date-function fluency and indexing-on-expression versus generated columns.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/event_count_on_key_days) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.