# Yesterday's Crown

> Every dawn inherits the spending of the day before.

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

Domain: SQL · Difficulty: hard · Seniority: L4

## Problem

A cost dashboard shows, for each billing day, the top-spending service from the day before, where a service's spend on a day is the total of all its charges that day. Return the billing day alongside that prior-day service and its spending total.

## Worked solution and explanation

### Why this problem exists in real interviews

Underneath the dashboard framing, this is three windowed constructs that have to flow through one pipeline without colliding: a per-day per-service total, a same-day leaderboard that keeps ties, and a one-step lookback that names each day's predecessor. Most candidates can write each piece, then bolt them together with nested subqueries that re-scan the aggregate three times. The skill being probed is making them share a single pass: deduplicate days once, LAG across that distinct set, and join the leaderboard back onto each day's prior date. Miss the deduplication and LAG points at the previous ROW instead of the previous day, and every result silently shifts.

---

### Break down the requirements

#### Step 1: Daily totals per service, rounded

daily_totals: aggregate amount per (billing day, service), ROUND to 2 decimals BEFORE any comparison, exactly as the prose requires.

#### Step 2: Rank per day AND look back one day

day_grid: over the set of DISTINCT billing days, use LAG(bill_day) OVER (ORDER BY bill_day) to find each day's previous billing day. Deduplicating first is essential: LAG over the un-deduplicated multi-row daily_totals would point at the previous ROW's day, not the previous distinct day.

#### Step 3: Filter rank 1 AND prev_day IS NOT NULL

ranked: keep the original construct family. Within the day-grid-joined daily_totals, compute DENSE_RANK() OVER (PARTITION BY bill_day ORDER BY total_amount DESC) so rnk=1 marks each day's top service, and DENSE_RANK keeps ALL tied services at the top. The prev_day from day_grid travels alongside it in the same CTE.

---

### The solution

**Aggregate, dual window, filter**

```sql
WITH daily_totals AS (
  SELECT DATE(bill_date) AS bill_day,
         svc_name,
         ROUND(SUM(amount), 2) AS total_amount
  FROM cloud_costs
  GROUP BY DATE(bill_date), svc_name
),
day_grid AS (
  SELECT bill_day,
         LAG(bill_day) OVER (ORDER BY bill_day) AS prev_day
  FROM (SELECT DISTINCT bill_day FROM daily_totals)
),
ranked AS (
  SELECT dt.bill_day,
         dt.svc_name,
         dt.total_amount,
         g.prev_day,
         DENSE_RANK() OVER (PARTITION BY dt.bill_day ORDER BY dt.total_amount DESC) AS rnk
  FROM daily_totals dt
  JOIN day_grid g ON g.bill_day = dt.bill_day
)
SELECT g.bill_day AS bill_day,
       r.svc_name AS svc_name,
       r.total_amount AS total_amount
FROM day_grid g
JOIN ranked r ON r.bill_day = g.prev_day AND r.rnk = 1
WHERE g.prev_day IS NOT NULL
ORDER BY g.bill_day, r.svc_name
```

> **Cost Analysis**
>
> daily_totals collapses 20M rows to (48 months times 30 days) times 400 services, around 575K rows. The two windows in ranked share an ORDER BY on bill_day so the planner can sort once. Final filter is a cheap predicate on the windowed output.

> **Interviewers Watch For**
>
> Whether DENSE_RANK was used instead of RANK (so ties at rank 1 all surface), whether LAG ordering ignores partition (so the very first row globally is the one with NULL prev_day), and whether you remembered to ROUND inside the CTE rather than after windowing.

> **Common Pitfall**
>
> Using LAG(bill_day) OVER (PARTITION BY svc_name ORDER BY bill_day) accidentally checks 'has this service had a previous day', not 'does the dataset have a previous day'. That excludes brand new services on every day they appear, which the prompt does not want.

---

## Common follow-up questions

- Why DENSE_RANK rather than RANK for the per day leaderboard? _(Tests whether the candidate knows that 'include ties at rank 1' is what DENSE_RANK and RANK both do at rank 1; either works for this exact filter, but DENSE_RANK is defensive if the threshold changes to <= 3.)_
- How would you also return the day over day delta for the top service? _(Tests window combination. The candidate should add LAG(total_amount) over the daily totals partitioned by svc_name, then compute total_amount minus prev_total.)_
- What changes if some bill_dates have no rows because the day is missing entirely? _(Tests gap awareness. The current LAG looks at adjacent rows in the data, not adjacent calendar days. To detect gaps you need a calendar table or a recursive CTE.)_

## Related

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