# Weekend Warriors

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

Canonical URL: <https://datadriven.io/problems/weekend_warriors>

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

A mobile fitness app logs events with timestamps. Break down the total number of events by event type, separated into weekday and weekend buckets. Show which event types are more popular on weekends.

## Worked solution and explanation

### Why this problem exists in real interviews

Product analytics routinely slice events by "weekend vs weekday" to find usage patterns. The question tests whether the candidate reaches for conditional aggregation (`SUM(CASE WHEN ... THEN 1 END)`) to produce a wide layout, or falls back to a long layout that ships the wrong shape to the dashboard.

> **Trick to Solving**
>
> "Two side-by-side columns from one table" is the conditional-aggregation signal. Classify each row with `strftime('%w', ...)` (SQLite returns '0' for Sunday, '6' for Saturday), then count with `SUM(CASE WHEN weekend THEN 1 ELSE 0 END)` and its complement. Group by `event_type`, sort by weekend count descending.

---

### 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 weekend; the rest are weekday.

#### 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. Its complement (`NOT IN ('0','6')`) is the weekday count. `SUM` with 1/0 is a row counter that respects the condition.

#### Step 3: Group by event type

`GROUP BY event_type` gives one row per event type with both pivoted columns populated.

#### Step 4: Sort by weekend activity

`ORDER BY weekend_count DESC` surfaces the most weekend-driven events at the top.

---

### The solution

**Conditional aggregation for weekend/weekday pivot**

```sql
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
```

> **Cost Analysis**
>
> `event_data` models 150M rows. This is a single full scan with a hash aggregate over ~20 event types, producing at most 20 rows. The `strftime` calls run per row but are cheap. There is nothing an index can do: every row contributes to the answer.

> **Interviewers Watch For**
>
> The key signal is choosing the wide pivot layout instead of a long layout with a `day_category` column. Dashboards expect side-by-side counts. A strong candidate also asks which timezone to use (the prompt says UTC) and whether events exactly on a boundary should round up or down.

> **Common Pitfall**
>
> Producing the long shape (one row per `event_type` x `day_category`) looks correct but fails: the prompt and the dashboard both need two columns side by side. The other trap is using `COUNT(CASE WHEN ... THEN 1 END)` without `ELSE 0`. `COUNT` ignores NULLs, which is what you want there, but writing `SUM(CASE WHEN ... THEN 1 END)` without an ELSE propagates NULLs through the SUM and returns NULL for event types with zero matching rows.

---

## Common follow-up questions

- How would you add a weekend/weekday ratio column? _(Divide `weekend_count * 1.0 / NULLIF(weekday_count, 0)` to avoid integer truncation and divide-by-zero. Place it after the two counts.)_
- What if timestamps came in multiple timezones and the `%w` extraction had to be local time? _(Store the timezone with each row, convert with `datetime(event_timestamp, tz_offset)` before `strftime`, or pre-materialize a local-time column in the load job.)_
- How would the plan change at 10B events? _(Partition by month and precompute a daily `event_type, weekend_count, weekday_count` table. The dashboard reads from the aggregate, not the raw events.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/weekend_warriors)
- [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.