Cloud Cost Trend Analysis

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

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Tesla

Problem

FinOps wants to spot bumpy cloud bills service by service. For every individual bill, list the service, the billing date, the amount, and how much that bill moved versus the service's previous bill. The first bill on record for a service has nothing to compare against.

Cost trends across billing periods.

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

Why this problem exists in real interviews

This is the LAG window function in its purest form: same row count in, one extra column out, the comparison anchored per partition. The interviewer is checking that you partition by svc_name, order by bill_date, and remember that the first row per partition naturally returns NULL when there's no prior bill to subtract.

Break down the requirements

Step 1: Project the row-level columns

Project the four output columns directly from cloud_costs: svc_name, bill_date, amount, and the LAG expression as price_change.

Step 2: Compute the bill-over-bill delta

amount - LAG(amount) OVER (PARTITION BY svc_name ORDER BY bill_date, cost_id) gives the delta versus the previous bill of the same service. Partitioning by svc_name ensures the LAG never crosses service boundaries; adding cost_id after bill_date makes the ordering total, so two bills a service posts on the same date still have a well-defined predecessor.

Step 3: Sort the output

ORDER BY svc_name, bill_date, cost_id puts each service's bills together in date order, with cost_id settling any same-date pair so the row order is fully determined. The first row per service has no prior bill so price_change is NULL.

The solution

SELECT
    svc_name,
    bill_date,
    amount,
    amount - LAG(amount) OVER (PARTITION BY svc_name ORDER BY bill_date, cost_id) AS price_change
FROM cloud_costs

Common follow-up questions

• How would you also show the percentage change? (Divide the delta by LAG(amount) value, guarding against division by zero with NULLIF.)
• What if billing dates have gaps (missing months)? Should the bill after a gap show the change versus the last seen bill or NULL? (LAG compares adjacent rows regardless of calendar gaps; a generated date series joined back may be needed.)
• How would you flag services with 3+ consecutive bills where `price_change` > 0? (Tests streak detection with CASE + SUM window patterns.)
• If finance wanted only services where the latest bill jumped more than 50% from the prior one, where would you add that filter? (Tests aggregation on top of the row-level result.)