# Yesterday's Crown

> Every dawn inherits the spending of the day before.

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

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

## Problem

A cloud cost dashboard shows, for each billing day, which service spent the most the day before, where a service's spend on a day is the total of its charges that day and only charges tied to a billing account count. Return each billing day with that prior-day top service, its spend, and the prior day's total spend across all services; 'the day before' is the most recent earlier day that actually has charges, and the earliest day, having no predecessor, does not appear.

## Tables

### cloud_costs

- cost_id INTEGER
- provider TEXT
- svc_name TEXT
- region TEXT
- amount REAL
- acct_id TEXT
- bill_date TEXT

Sample rows (10 of 200):

| cost_id | provider | svc_name | region | amount | acct_id | bill_date |
| --- | --- | --- | --- | --- | --- | --- |
| 543 | GCP | S3 | us-west-2 | 19.33 | acct-1001 | 2026-02-01 |
| 586 | Azure | RDS | eu-west-1 | 37.16 | acct-1002 | 2026-03-01 |
| 629 | aws | Lambda | ap-southeast-1 | 54.99 | acct-1003 | 2026-04-01 |
| 672 | gcp | BigQuery | us-central1 | 72.82 | acct-1004 | 2026-05-01 |
| 715 | AWS | Cloud Run | europe-west1 | 90.65 | acct-1005 | 2026-06-01 |
| 758 | GCP | Blob Storage | us-east-1 | 108.48 | acct-1006 | 2026-07-01 |
| 801 | Azure | Cosmos DB | us-west-2 | 126.31 | acct-1007 | 2026-08-01 |
| 844 | aws | CloudFront | eu-west-1 | 144.14 | acct-1008 | 2026-09-01 |
| 887 | gcp | Pub/Sub | ap-southeast-1 | 161.97 | NULL | 2026-01-01 |
| 930 | AWS | EC2 | us-central1 | 179.8 | acct-1010 | 2026-02-01 |

## Expected output

First 5 of 55 rows:

| bill_day | svc_name | total_amount | prior_day_total |
| --- | --- | --- | --- |
| 2022-02-01 | S3 | 1108.46 | 1108.46 |
| 2022-03-01 | Blob Storage | 1999.96 | 1999.96 |
| 2022-04-01 | S3 | 910.83 | 910.83 |
| 2022-05-01 | Blob Storage | 61.98 | 61.98 |
| 2022-06-01 | S3 | 732.53 | 732.53 |

## Worked solution and explanation

### What this problem really is

Under the dashboard framing this is a per-day leaderboard chained onto a previous-existing-day lookback. The skill being probed is stitching three windowed stages through one pipeline: total each service's charges per day, rank each day's services, and map every day to its true predecessor. The billing days are NOT contiguous, so `DATE(bill_date, '-1 day')` misses on every gap, and `LAG` over the un-deduplicated `daily_totals` points at the previous ROW instead of the previous distinct day. Get the grain wrong and every prior-day mapping silently shifts; rank before you aggregate and the numbers are nonsense; forget that unattributed charges must be dropped and the wrong service wears the crown; keep the earliest day and it surfaces with nothing behind it.

---

### Break it into stages

#### Step 1: Total each service's day, account charges only

`daily_totals`: sum `amount` per (billing day, service) with `SUM`, `ROUND` to two decimals, and `WHERE acct_id IS NOT NULL` so charges with no billing account never enter the total. `DATE(bill_date)` collapses the timestamp to a day. This filter is load-bearing: the busy days carry many unattributed rows, and including them changes which service leads.

#### Step 2: Look back exactly one distinct day

`day_grid`: over the set of DISTINCT billing days, `LAG(bill_day) OVER (ORDER BY bill_day)` names each day's previous billing day. Deduplicating first is the whole trick: `LAG` over the multi-row `daily_totals` would point at the previous ROW's day, not the previous distinct day.

#### Step 3: Rank each day's leaderboard, keep ties

`ranked`: within `daily_totals`, `DENSE_RANK() OVER (PARTITION BY bill_day ORDER BY total_amount DESC)` marks each day's top service with `rnk = 1`, and keeps ALL co-leaders at rank 1 rather than arbitrarily choosing one the way `MAX` would.

#### Step 4: Roll each day up to its total spend

`day_spend`: sum the per-service totals back up to one row per day, so the dashboard can show the prior day's total cloud spend beside its leader.

#### Step 5: Hang the prior day's leader and total on the spine

Use `day_grid` as the spine and INNER JOIN `ranked` on `r.bill_day = g.prev_day AND r.rnk = 1` to inherit the prior day's leader, then INNER JOIN `day_spend` on the same `prev_day` for that day's total. The inner join does double duty: the earliest day's `prev_day` is `NULL`, so it matches nothing and drops out on its own, with no `WHERE` needed to remove it.

---

### The solution

**Aggregate, dual window, two joins**

```sql
WITH daily_totals AS (
  SELECT DATE(bill_date) AS bill_day,
         svc_name,
         ROUND(SUM(amount), 2) AS total_amount
  FROM cloud_costs
  WHERE acct_id IS NOT NULL
  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 bill_day,
         svc_name,
         total_amount,
         DENSE_RANK() OVER (PARTITION BY bill_day ORDER BY total_amount DESC) AS rnk
  FROM daily_totals
),
day_spend AS (
  SELECT bill_day,
         ROUND(SUM(total_amount), 2) AS day_total
  FROM daily_totals
  GROUP BY bill_day
)
SELECT g.bill_day AS bill_day,
       r.svc_name AS svc_name,
       r.total_amount AS total_amount,
       d.day_total AS prior_day_total
FROM day_grid g
JOIN ranked r ON r.bill_day = g.prev_day AND r.rnk = 1
JOIN day_spend d ON d.bill_day = g.prev_day
```

> **Aggregate first, then window and join**
>
> `daily_totals` collapses the raw charges to at most (distinct days) times (services) rows before any window or join runs, so both windows, the roll-up and the two joins operate on that small aggregate rather than the raw table. Aggregating first is what keeps this cheap at scale; partitioning by `bill_date` lets the engine prune to the days it touches.

> **The tells of a clean answer**
>
> Whether `LAG` runs over the DISTINCT day set rather than the raw totals; whether the earliest day is dropped by the inner join rather than leaking in with a null leader under an outer join; and whether unattributed charges are filtered BEFORE the totals are formed, not after the ranking.

> **LAG over the wrong partition**
>
> Writing `LAG(bill_day) OVER (PARTITION BY svc_name ORDER BY bill_day)` quietly answers 'has THIS service billed before', not 'does the dataset have an earlier day'. That excludes every service on its first appearance, which the prompt never asks for.

---

## Common follow-up questions

- Why `DENSE_RANK` at rank 1 rather than `MAX` for the per-day leader? _(Tests whether the candidate knows both `RANK` and `DENSE_RANK` keep every tied row at rank 1, so co-leaders survive, whereas `MAX` collapses to one value and cannot carry the service name.)_
- How would you also return the leader's change in spend versus its previous appearance? _(Tests window combination. Add `LAG(total_amount) OVER (PARTITION BY svc_name ORDER BY bill_day)` to the leader's row and subtract to get the day-over-day change.)_
- What changes if you had to flag calendar days where no service billed at all? _(Tests gap awareness. The current `LAG` reads adjacent rows in the data, not adjacent calendar days. Detecting truly missing calendar days needs a calendar table or a recursive CTE.)_

## Related

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