# Above the Line > The budget line is here. How many crossed it? Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L4 ## Problem A cloud finance team wants to know how many of their services rack up a real monthly bill, counting a service the moment its total spend for a month reaches $100. Month by month, report the percentage of services that cross that line, ignoring billing entries that have no date. ## Worked solution and explanation ### The trap This is a grain problem wearing a cost-report costume. The ask is the percentage of services that crossed $100 in a month, but a service's monthly spend is itself a sum: one service can post many billing rows in the same month. That forces two aggregations stacked in the right order. First collapse each (svc_name, month) pair into a single total, then count how many of those totals cleared $100. Group straight by month and you stop counting services altogether: you count billing rows above $100, and the percentage silently answers a different question. > **The two-pass tell** > > Whenever the thing you are counting is itself an aggregate (a service's monthly spend), you need a pass to build that aggregate before the pass that counts it. Pass one: SUM(amount) grouped by (svc_name, month). Pass two: over those per-service totals, SUM(CASE WHEN total >= 100 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), grouped by month. --- ### Walking the two passes #### Step 1: Collapse to one total per service per month The CTE groups by (svc_name, month) and sums amount. This is the load-bearing line: it turns raw billing rows into the unit the metric actually talks about, a service's spend for the month. Miss it and every later count is off. #### Step 2: Drop the undated rows A null bill_date cannot be assigned to any month, so those rows are filtered before the first aggregation. Doing it here keeps them out of both the numerator and the denominator, so they never distort the percentage. #### Step 3: Count the crossers as a percentage Over the per-service totals, a CASE marks each total that reached $100 as 1 and the rest as 0. Summing that and dividing by COUNT(*) per month gives the share of services above the line. The CAST keeps the division floating point instead of truncating the percentage toward 0. --- ### The solution **Stage the grain, then count the crossers** ```sql WITH monthly_svc AS ( SELECT svc_name, strftime('%Y-%m', bill_date) AS month, SUM(amount) AS total_spend FROM cloud_costs WHERE bill_date IS NOT NULL GROUP BY svc_name, strftime('%Y-%m', bill_date) ) SELECT month, CAST(SUM(CASE WHEN total_spend >= 100 THEN 1 ELSE 0 END) AS DOUBLE) * 100.0 / COUNT(*) AS pct_hitting_threshold FROM monthly_svc GROUP BY month ORDER BY month ``` **Group by month directly** SELECT month, ... FROM cloud_costs GROUP BY month counts individual billing rows over $100. A service with five small $30 rows never crosses on its own, yet a single $120 row counts as one crosser out of many rows. The denominator is rows, not services, so the percentage is meaningless. **Stage (service, month) first** Summing to one total per service per month makes the denominator the number of services and the numerator the services whose combined spend cleared $100. Now the percentage means what the prompt asked for. > **Counting rows instead of services** > > The most common wrong answer skips the CTE and aggregates straight off cloud_costs. It runs, it returns numbers, and it is wrong: the grain is billing rows, not services. And do not forget the undated rows. Leave the null bill_date filter off and each one lands in a phantom month and skews the denominator. > **What the interviewer is watching** > > Whether you recognize the hidden sum inside 'a service's monthly spend' and stage it in a CTE, whether you handle the null bill_date on both sides of the fraction, and whether you force floating-point division rather than integer-truncating the percentage to 0. > **At 18M rows** > > The (svc_name, month) aggregation collapses ~18M billing rows into at most a few thousand per-service-month totals before the second pass ever runs, so the outer percentage is cheap. bill_date is the partition key, so the strftime bucketing lines up with the physical layout, and a covering index on (svc_name, bill_date, amount) turns the first pass into a range scan instead of a full table scan. --- ## 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 sees grain as a choice: (svc_name, month) versus (svc_name, region, month).)_ - How would you also report the total dollars by which qualifying services exceeded $100 each month, not just the count? _(Extends conditional aggregation to a conditional sum over the staged per-service totals.)_ - New billing rows arrive days after a month closes. How would you make this percentage incrementally updatable instead of re-aggregating the full history on every run? _(Probes incremental aggregation and late-arriving data handling on cloud_costs.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/above_the_line) - [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.