Peak Spending Month

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

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Lyft

Problem

A spike on the cloud invoice has the CFO asking which billing month was responsible. Working from the line items in cloud_costs, find the billing month with the highest total spend and report that total next to it.

One month, the bill was unforgettable.

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

This is an argmax over a rollup dressed up as an invoice investigation. Nobody fumbles SUM(amount). What separates candidates is noticing that the answer is a label, not a number. MAX(amount) hands the CFO the single biggest line item, and a bare SUM(amount) hands back one grand total. Neither names a month. You have to roll the line items up to months first, then pick the bucket that won and return its name with its total. Miss that and the spike gets pinned on one invoice line, or on nothing at all.

Walk it

Step 1: Make the month the key on purpose

bill_date already sits on the first of each month, so grouping by the raw date would happen to give the right buckets here. The label would read '2026-08-01', though, and the preview wants '2026-08'. strftime('%Y-%m', bill_date) makes the month the key by design rather than by luck. It also keeps the year, which matters: the data runs from 2022 to 2026, and August of two different years must stay apart.

Step 2: Total each month, credits included

SUM(amount) per billing_month. Leave the ten negative amount rows in: they are credits the invoice nets out, and dropping them inflates every month they touch. The twelve NULL amounts cost nothing, because SUM skips them.

Step 3: Keep the winning bucket

ORDER BY total_spend DESC LIMIT 1 sorts the monthly totals heaviest first and keeps the top one, label attached. Flip the direction and you confidently report the quietest month.

SELECT strftime('%Y-%m', bill_date) AS billing_month, SUM(amount) AS total_spend
FROM cloud_costs
GROUP BY billing_month
ORDER BY total_spend DESC
LIMIT 1

Scalar extreme

SELECT MAX(amount) FROM cloud_costs returns one number: the largest single line item. It is not any month's total, and it carries no month.

Argmax over months

Group by the month label, sum, sort heaviest first, keep one row. The answer is a pair: '2026-08' with 12268.71.

Common follow-up questions

• The CFO now wants the peak month per `provider`, with the casing cleaned up. What changes? (Normalizing a dirty key before using it, then picking a top month within each vendor.)
• How would you return every month tied for the top total instead of exactly one? (Replacing a row cap with a comparison against the maximum monthly total.)
• What if `bill_date` started carrying the actual usage day instead of the first of the month? (Whether the month key was chosen on purpose or worked only by luck.)