Yesterday's Crown

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

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.

Every dawn inherits the spending of the day before.

Tables

cloud_costs

Columns: cost_id INTEGER, provider TEXT, svc_name TEXT, region TEXT, amount REAL, acct_id TEXT, bill_date TEXT

Expected output

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

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

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.)