Weekend Warriors

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

Problem

event_data holds one row per app event, each carrying its event_type and the event_timestamp it happened at. For every event type, show how many of its events fell on a weekend and how many fell on a weekday as two counts side by side, most weekend events first, with types that share a weekend count in any order.

Weekdays vs. weekends. When does the action really happen?

Tables

event_data

Columns: event_id INTEGER, event_type TEXT, user_id INTEGER, event_timestamp TEXT, payload TEXT, tags TEXT, properties TEXT

Expected output

Worked solution and explanation

What this problem really is

This is a pivot dressed up as product analytics: one table goes in, and each event type comes out as one row carrying two counts side by side. Anyone can count events by type. What separates candidates is splitting that count into a weekend bucket and a weekday bucket on the same row, and reading the weekend correctly off the day-of-week encoding. SQLite's strftime('%w', ...) makes Sunday '0' and Saturday '6'. Assume the days run 1 to 7, or that the weekend is '6' and '7', and Sunday silently lands in the weekday bucket. Every weekend count comes out low, and the weekend-first ordering, the whole point of the question, comes out wrong.

Break down the requirements

Step 1: Classify each event by day of week

strftime('%w', event_timestamp) returns '0' for Sunday and '6' for Saturday. Those two values are the weekend; '1' through '5' are weekdays. Say the mapping out loud before writing it: this is where most wrong answers start.

Step 2: Pivot with conditional sums

SUM(CASE WHEN strftime('%w', event_timestamp) IN ('0', '6') THEN 1 ELSE 0 END) is the weekend count, and the same expression with NOT IN is the weekday count. A SUM over 1s and 0s is a row counter that respects the condition, and both buckets fill in the same pass.

Step 3: Group by event_type

GROUP BY event_type gives one row per type with both counts populated. A type with no weekend events would still appear, with weekend_count at 0, which a weekend-only filter would have dropped.

Step 4: Sort by weekend activity

ORDER BY weekend_count DESC puts the most weekend-driven types first. The grader checks that rows are sorted by the weekend count, so no tiebreaker is owed.

The solution

SELECT event_type,
       SUM(CASE WHEN strftime('%w', event_timestamp) IN ('0', '6')
                THEN 1 ELSE 0 END) AS weekend_count,
       SUM(CASE WHEN strftime('%w', event_timestamp) NOT IN ('0', '6')
                THEN 1 ELSE 0 END) AS weekday_count
FROM event_data
GROUP BY event_type
ORDER BY weekend_count DESC

Two filtered counts, joined back

A weekend CTE with WHERE ... IN ('0', '6') and a weekday CTE with NOT IN, each grouped by event_type, then joined on event_type. It returns the right answer, but it scans 150M rows twice and needs an outer join to keep a type missing from one side.

One pass, condition inside the count

SUM(CASE WHEN ... THEN 1 ELSE 0 END) lets the condition pick the bucket instead of deciding which rows survive. One scan, one hash aggregate, no join to get wrong.

Common follow-up questions

• How would you add a weekend-to-weekday ratio column? (Use `weekend_count * 1.0 / NULLIF(weekday_count, 0)` to avoid integer division and a zero divisor.)
• What if timestamps came in multiple timezones and the `%w` extraction had to use local time? (Store the offset with each row and shift with `datetime(event_timestamp, tz_offset)` before `strftime`, or materialize a local-time column in the load job.)
• How would the plan change at 10B events? (Precompute a daily rollup of `event_type`, `weekend_count` and `weekday_count`, partitioned by date, so the dashboard reads the aggregate instead of the raw events.)