# Bookends of the Week

> Every week has two edges. Weigh what lands on each.

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

Domain: SQL · Difficulty: hard · Seniority: L4 · Asked in: Meta

## Problem

Find what percentage of each week's spending in `transactions` landed on Monday and what percentage landed on Sunday, with each week identified by its week-of-year number. Report both shares as integer percentage points with any fraction dropped rather than rounded, so a 37.9% share reads 37.

## Tables

### transactions

- transaction_id INTEGER
- user_id INTEGER
- product_id INTEGER
- quantity INTEGER
- total_amount REAL
- transaction_date TEXT

Sample rows (10 of 200):

| transaction_id | user_id | product_id | quantity | total_amount | transaction_date |
| --- | --- | --- | --- | --- | --- |
| 1067 | 197 | 1001 | 2 | 23.46 | 2026-02-02 |
| 1134 | 294 | 1050 | 3 | 36.93 | 2026-03-03 |
| 1201 | 391 | 1099 | 4 | 50.4 | 2026-04-04 |
| 1268 | 488 | 1148 | 5 | 63.87 | 2026-05-05 |
| 1335 | 585 | 1197 | 0 | 0 | 2026-06-06 |
| 1402 | 682 | 1246 | 2 | 90.81 | 2026-07-07 |
| 1469 | 779 | 1295 | 3 | 104.28 | 2026-08-08 |
| 1536 | 876 | 1344 | 4 | 117.75 | 2026-09-09 |
| 1603 | 973 | 1393 | 5 | -45 | 2026-01-10 |
| 1670 | 1070 | 1442 | 1 | 144.69 | 2026-02-11 |

## Expected output

First 5 of 50 rows:

| week_num | monday_pct | sunday_pct |
| --- | --- | --- |
| 00 | 0 | 100 |
| 01 | 0 | 4 |
| 02 | 0 | 12 |
| 03 | 13 | 0 |
| 04 | 32 | 11 |

## Worked solution and explanation

### What this is really asking

Beneath the calendar language this is a share-of-total done twice against one denominator. For each week you need Monday's slice and Sunday's slice of the same weekly pie. The trap is filtering to Monday and Sunday first: the week total then holds only those two days, both shares inflate until they sum to about 100, and the numbers look tidy and mean nothing. The fix is folding both numerators and the full-week denominator into one grouped pass. The quieter tell is the last step: whole points with the fraction dropped is a type change, and reaching for `ROUND` turns 37.9 into 38.

---

### Break down the requirements

#### Step 1: Read the date twice

`strftime('%W', transaction_date)` gives the week bucket ('00' to '53', Monday-start), and `strftime('%w', transaction_date)` gives the day of week as text where Sunday is '0' and Monday is '1'. Comparing against those two codes is enough.

#### Step 2: One grouped pass, three sums

Group by `week_num`. Inside that group `SUM(total_amount)` is the denominator, and two conditional sums, `SUM(CASE WHEN ... = '1' ...)` and `SUM(CASE WHEN ... = '0' ...)`, are the Monday and Sunday numerators. The `ELSE 0` keeps a week with no Monday at 0 instead of dropping it.

#### Step 3: Scale, then cast to `INTEGER`

Divide `monday_total * 100.0` by `week_total`, then `CAST(... AS INTEGER)`. The cast is the requirement: it drops the fraction toward zero and hands back an integer, exactly what 'whole points, not rounded' asks for.

---

### The solution

**Monday and Sunday share per week**

```sql
WITH weekly AS (
  SELECT
    strftime('%W', transaction_date) AS week_num,
    SUM(total_amount) AS week_total,
    SUM(CASE WHEN strftime('%w', transaction_date) = '1' THEN total_amount ELSE 0 END) AS monday_total,
    SUM(CASE WHEN strftime('%w', transaction_date) = '0' THEN total_amount ELSE 0 END) AS sunday_total
  FROM transactions
  GROUP BY week_num
)
SELECT
  week_num,
  CAST(monday_total * 100.0 / week_total AS INTEGER) AS monday_pct,
  CAST(sunday_total * 100.0 / week_total AS INTEGER) AS sunday_pct
FROM weekly;
```

**Round it**

`ROUND(monday_total * 100.0 / week_total)` returns 38.0 for a 37.9 share: the wrong number, and still a decimal.

**Cast it**

`CAST(monday_total * 100.0 / week_total AS INTEGER)` returns 37: the fraction is dropped and the type is an integer, both of which the grade checks.

> **One pass carries all three sums**
>
> One scan over `transactions` collapses 100M rows into about 53 week buckets. The divides and casts run once per bucket, so the whole cost is the single aggregate pass, not three correlated subqueries.

> **`%w` and `%W` answer different questions**
>
> Whether you know `%w` is day-of-week text with Sunday '0' and Monday '1', versus `%W`, the week number. Mixing them up produces numbers that pass a glance and fail the grader.

> **A filter on weekday steals the denominator**
>
> Adding `WHERE strftime('%w', transaction_date) IN ('0', '1')` shrinks every denominator to two days, so the shares stop being shares of the week.

---

### Common follow-up questions

## Common follow-up questions

- How would you report every weekday's share in one row? _(Generalizes the two conditional sums to seven while the denominator stays fixed.)_
- What happens for a week with zero Monday transactions? _(The conditional sum stays 0, so the share is 0 without any calendar join.)_
- How does `%W` behave at year boundaries, and what breaks over multiple years? _(Week '00' holds the days before the first Monday, and every year reuses the same week numbers, so a year column is needed to keep years apart.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/bookends_of_the_week)
- [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.