Above the Line

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

General Assembly

Problem

A charge in cloud_costs with no acct_id is unattributed and stays out of every total, and a service crosses the $100 line in a month once its charges that month add up to $100 or more. For each month, report what percentage of the services billed that month crossed, as a whole number with any fraction dropped rather than rounded.

The budget line is here. How many crossed it?

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

The trap

This is a grain problem wearing a cost-report costume. The ask is the share of services that crossed $100 in a month, but a service's monthly spend is itself a sum: one service can post several charges in the same month. That forces two aggregations stacked in order. First collapse each (svc_name, month) pair into one total, then count how many of those totals cleared $100. Group straight by month and you stop counting services at all. Two quieter traps ride along: a charge with no acct_id belongs to nobody and must never reach a total, and the report wants a whole number with the fraction dropped, so 66.667 is 66, not 67.

Walking the two passes

Step 1: Drop the unattributed charges first

A charge with a null acct_id was billed to no account, so WHERE acct_id IS NOT NULL runs before anything is summed. Filter late and the damage is already done: those charges pushed some services over the line and created service-months that should not exist in the denominator.

Step 2: Collapse to one total per service per month

The monthly_svc CTE groups by svc_name and strftime('%Y-%m', bill_date) and sums amount. This is the load-bearing line: it turns raw charges into the unit the metric talks about. Credits (negative amounts) net out inside the same sum, which is exactly what a monthly bill means.

Step 3: Count the crossers, then drop the fraction

Over the per-service totals, the CASE marks each total at or above $100 as 1. Multiply by 100.0, divide by COUNT(*), and CAST(... AS INTEGER) cuts the result to a whole number by truncation. ROUND would turn two crossers out of three into 67; the report wants 66.

The solution

WITH monthly_svc AS (
    SELECT svc_name,
           strftime('%Y-%m', bill_date) AS month,
           SUM(amount) AS total_spend
    FROM cloud_costs
    WHERE acct_id IS NOT NULL
    GROUP BY svc_name, strftime('%Y-%m', bill_date)
)
SELECT month,
       CAST(100.0 * SUM(CASE WHEN total_spend >= 100 THEN 1 ELSE 0 END) / COUNT(*) AS INTEGER) AS pct_hitting_threshold
FROM monthly_svc
GROUP BY month

Group by month directly

Grouping cloud_costs by month tests individual charges. A service with five $30 charges never crosses, a single $120 charge counts as one crosser out of many rows, and the denominator is rows, not services.

Stage (service, month) first

One total per service per month makes the denominator the services billed and the numerator the services whose combined spend cleared $100. Now the percentage means what was asked.

Common follow-up questions

• A service bills in three regions in the same month. Should that count as one service crossing the line or three? (Tests whether the candidate treats grain as a choice: (`svc_name`, month) versus (`svc_name`, `region`, month).)
• How would you also report the dollars by which crossing services exceeded $100 each month? (Extends conditional aggregation to a conditional sum over the staged totals.)
• Late charges arrive days after a month closes. How would you keep this percentage current without re-aggregating all of `cloud_costs`? (Probes incremental aggregation and late-arriving data.)