# Weekly Transaction Day Split

> Transactions by day of week.

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

Domain: SQL · Difficulty: hard · Seniority: L4

## Problem

For each week, compute what percentage of that week's transaction volume occurred on Monday versus Sunday. Round to the nearest whole number and return results by week.

## Worked solution and explanation

### What this is really asking

`strftime('%w', ...)` returns 0 for Sunday and 1 for Monday, as text. Cast to INTEGER, sum `total_amount` per (week, dow), then express each week's Monday and Sunday slice as a share of that week's total.

---

### Break down the requirements

#### Step 1: Bucket by week and weekday

Group transactions by `strftime('%W', transaction_date)` for week number and the INTEGER cast of `strftime('%w', ...)` for day-of-week. Sum total_amount inside each bucket.

#### Step 2: Compute the per-week denominator

A second CTE sums daily_total back up to a single week_total per week_num. Doing it as a separate CTE keeps the percentage formula readable and avoids a self-aggregate.

#### Step 3: Pick Monday and Sunday with FIRST_VALUE

FIRST_VALUE with `ORDER BY CASE WHEN dow = 1 THEN 0 ELSE 1 END` pulls the Monday row to the front of each week partition. Repeat with `dow = 0` for Sunday.

---

### The solution

**MONDAY AND SUNDAY SHARE PER WEEK**

```sql
WITH weekly AS (
  SELECT
    strftime('%W', transaction_date) AS week_num,
    CAST(strftime('%w', transaction_date) AS INTEGER) AS dow,
    SUM(total_amount) AS daily_total
  FROM transactions
  GROUP BY week_num, dow
),
week_totals AS (
  SELECT week_num, SUM(daily_total) AS week_total
  FROM weekly
  GROUP BY week_num
)
SELECT
  w.week_num,
  CAST(ROUND(FIRST_VALUE(w.daily_total) OVER (
    PARTITION BY w.week_num
    ORDER BY CASE WHEN w.dow = 1 THEN 0 ELSE 1 END
  ) * 100.0 / wt.week_total) AS REAL) AS monday_pct,
  CAST(ROUND(FIRST_VALUE(w.daily_total) OVER (
    PARTITION BY w.week_num
    ORDER BY CASE WHEN w.dow = 0 THEN 0 ELSE 1 END
  ) * 100.0 / wt.week_total) AS REAL) AS sunday_pct
FROM weekly w
JOIN week_totals wt ON w.week_num = wt.week_num
GROUP BY w.week_num
ORDER BY w.week_num;
```

> **Cost Analysis**
>
> The first CTE is one scan with partition pruning on `transaction_date`, collapsing 100M rows to about 7 per week. The window pass over that tiny aggregate is free.

> **Interviewers Watch For**
>
> Whether you remember `%w` is text (Sunday=0) and `%W` is ISO-ish week (00-53, Monday start). Confusing the two, or forgetting the cast, gives plausible-looking but wrong percentages. Also watch the 100.0 to dodge integer division.

> **Common Pitfall**
>
> Conditional SUM (`SUM(CASE WHEN dow=1 THEN daily_total END)`) would be simpler than FIRST_VALUE here and avoid the outer GROUP BY trick. The expected query's window approach works but is heavier than the problem needs.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- Rewrite this with conditional aggregation instead of FIRST_VALUE. _(SUM(CASE WHEN dow=1 THEN daily_total END) / week_total is shorter and avoids the window pass. Compare readability and plan.)_
- What happens for a week with zero Monday transactions? _(The (week, dow=1) row never exists, so FIRST_VALUE picks whatever sorts next under the CASE. Discuss how to coerce that to 0 with COALESCE on a LEFT JOIN to a calendar.)_
- How does `%W` handle the partial week at year boundaries? _(Week 00 contains days before the first Monday. Two calendar years can share a week_num, so a year column is needed for multi-year inputs.)_

## Related

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