# Previous Day Top Service

> Yesterday's top spender.

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

Domain: SQL · Difficulty: hard · Seniority: L4

## Problem

Build a daily revenue dashboard widget that shows each date's top-spending service from the previous day. Round totals to 2 decimal places before comparing. If multiple services tie for the top spot, show all. Only include dates where prior-day data exists. Return the billing day, service name, and total amount.

## Worked solution and explanation

### Why this problem exists in real interviews

Daily operational reports trim the first day of every dataset because there is no comparison baseline. Interviewers use this prompt to check that the candidate combines a per day aggregate, a per day top N rank with ties, AND a LAG based 'has previous day' filter, all in one query without resorting to nested subqueries.

---

### Break down the requirements

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

GROUP BY DATE(bill_date), svc_name with ROUND(SUM(amount), 2). DATE() strips the time component if any, so timestamps with intra day granularity collapse to one bucket per day.

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

In the same window pass: DENSE_RANK() OVER (PARTITION BY bill_day ORDER BY total_amount DESC) gives the per day leaderboard with ties, and LAG(bill_day) OVER (ORDER BY bill_day) records whether a previous day exists. Computing both in one CTE avoids a second pass.

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

The first chronological day has prev_day = NULL and must be excluded. DENSE_RANK is correct because the prompt explicitly says 'include ties' for rank 1; RANK or ROW_NUMBER would change the semantics.

---

### 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
),
ranked AS (
  SELECT bill_day, svc_name, total_amount,
         LAG(bill_day) OVER (ORDER BY bill_day) AS prev_day,
         DENSE_RANK() OVER (PARTITION BY bill_day ORDER BY total_amount DESC) AS rnk
  FROM daily_totals
)
SELECT bill_day, svc_name, total_amount FROM ranked
WHERE rnk = 1 AND prev_day IS NOT NULL
ORDER BY bill_day, 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/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). 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.