# Friday Spending Analysis

> Friday spending during Q1.

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

Domain: SQL · Difficulty: hard · Seniority: L3

## Problem

For each Friday in the first 13 weeks of the year, calculate the average transaction amount. Show the week number and average amount.

## Worked solution and explanation

### What this is really asking

`strftime('%w')` returns day-of-week (Fri=5); `strftime('%W')` returns week-of-year. Same letter, different case, different value. You need both, plus a January-through-March guard to keep week 00 from leaking in prior-year December.

---

### Break down the requirements

#### Step 1: Isolate Fridays

`CAST(strftime('%w', transaction_date) AS INTEGER) = 5` keeps only Fridays. strftime returns text; a bare comparison to 5 silently drops rows in SQLite.

#### Step 2: Bound to the first 13 weeks

`%W <= 13` plus `%m IN ('01','02','03')` fence the window. The month guard stops week-00 days at the year boundary from sweeping in last December.

#### Step 3: Aggregate per week_number

GROUP BY the week expression so each Friday rolls up under its week. Output is at most 13 rows.

---

**FRIDAY AVG SPEND**

```sql
SELECT
    CAST(strftime('%W', transaction_date) AS INTEGER) AS week_number,
    AVG(total_amount) AS avg_amount
FROM transactions
WHERE CAST(strftime('%w', transaction_date) AS INTEGER) = 5
  AND CAST(strftime('%W', transaction_date) AS INTEGER) <= 13
  AND strftime('%m', transaction_date) IN ('01', '02', '03')
GROUP BY CAST(strftime('%W', transaction_date) AS INTEGER)
ORDER BY week_number;
```

> **Cost Analysis**
>
> Partition pruning on transaction_date is the only thing keeping this off a 150M-row full scan. strftime expressions are opaque to the optimizer; pair them with a date range BETWEEN '2025-01-01' AND '2025-03-31' to enable pruning.

> **Interviewers Watch For**
>
> Whether you catch that strftime returns text. `strftime('%w', ...) = 5` is string-to-integer; SQLite returns no rows. The explicit CAST is the safe move.

> **Common Pitfall**
>
> Skipping the month guard. `%W` returns '00' for early January days before the first Monday; without the month filter, a year-boundary cross can drag prior-year December rows under week 00.

> **The False Start**
>
> First instinct is `GROUP BY transaction_date` to average each Friday on its own. That returns one row per date, but the prompt asks for week_number. Pivot to `GROUP BY strftime('%W', ...)` so the week is both bucket and output.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- How would you adapt this for a configurable day-of-week and week range? _(Parameterize the 5 and 13 as inputs and consider ISO-week (`%V` in some engines) for portability.)_
- What if a Friday has no transactions? _(The query silently omits it. To surface zeros, build a week series via recursive CTE and LEFT JOIN transactions.)_
- How would you compute median spend per Friday instead of mean? _(Use PERCENTILE_CONT(0.5) where supported, or rank rows per week and pick the middle row via ROW_NUMBER and COUNT.)_

## Related

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