# Where the Year Leans > Some teams file early, some file late. See which way each one tips. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 · Asked in: ESPN ## Problem A people-analytics team logs one reading per department metric each quarter, and leadership wants to see whether a department's reporting leans toward the first half of the fiscal year (quarters Q1 and Q2) or the second half (quarters Q3 and Q4). Walking through the departments in alphabetical order, show the first-half reading count, the second-half reading count, and the ratio of the two. ## Tables ### employee_metrics - metric_id INTEGER - department TEXT - metric_name TEXT - metric_value REAL - fiscal_quarter TEXT - fiscal_year INTEGER Sample rows (10 of 200): | metric_id | department | metric_name | metric_value | fiscal_quarter | fiscal_year | | --- | --- | --- | --- | --- | --- | | 141 | Product | attrition_rate | 7.3 | Q2 | 2025 | | 182 | Design | avg_tenure_months | 14.6 | Q3 | 2026 | | 223 | Marketing | satisfaction_score | 21.9 | Q4 | 2024 | | 264 | Sales | training_hours | 29.2 | Q1 | 2025 | | 305 | HR | promotions | 36.5 | Q2 | 2026 | | 346 | Finance | open_positions | 43.8 | Q3 | 2024 | | 387 | Legal | time_to_hire_days | 51.1 | Q4 | 2025 | | 428 | Operations | diversity_ratio | 58.4 | Q1 | 2026 | | 469 | Data | remote_percentage | 65.7 | Q2 | 2024 | | 510 | Engineering | headcount | 73 | Q3 | 2025 | ## Expected output First 5 of 10 rows: | department | first_half_count | second_half_count | half_year_ratio | | --- | --- | --- | --- | | Data | 10 | 10 | 1 | | Design | 10 | 10 | 1 | | Engineering | 10 | 10 | 1 | | Finance | 10 | 10 | 1 | | HR | 10 | 10 | 1 | ## Worked solution and explanation ### What this really is Strip the reporting-cadence costume and this is a single-pass conditional pivot: for each department you pull two counts out of the same scan, split by which half of the year a reading lands in, then divide them. Anyone can produce the two counts. Two moves separate the people who finish clean. They put the CASE INSIDE the SUM so one pass over the table yields both buckets, and they cast before dividing so SQLite does floating-point division instead of handing back a truncated 0 or 1. Miss the cast and every ratio collapses to a whole number that looks plausible and is quietly wrong. > **Two counts from one table means the branch goes inside the aggregate** > > The shape 'two counts from one table, split by a condition' is the tell for conditional aggregation. Write SUM(CASE WHEN THEN 1 ELSE 0 END) once per bucket, GROUP BY the dimension, and you get every bucket in a single pass instead of one query per bucket. --- ### Building it #### Step 1: Split each row with CASE inside the aggregate SUM(CASE WHEN `fiscal_quarter` IN ('Q1','Q2') THEN 1 ELSE 0 END) counts only first-half readings; a second SUM keyed on Q3 and Q4 counts the rest. Both are computed in the same pass because the CASE lives inside the SUM, not wrapped around it. #### Step 2: Collapse to one row per department GROUP BY department collapses the scan to one row per team, and the two conditional sums are evaluated within each group. #### Step 3: Divide, but cast first SQLite divides two integers as integers, so 5 over 8 becomes 0. Cast the numerator to REAL to force floating-point division. Wrap the denominator in MAX(`second_half_count`, 1) so a department with no second-half readings returns the numerator instead of a divide-by-zero NULL. --- ### The solution **Single-pass conditional pivot** ```sql SELECT department, SUM(CASE WHEN fiscal_quarter IN ('Q1','Q2') THEN 1 ELSE 0 END) AS first_half_count, SUM(CASE WHEN fiscal_quarter IN ('Q3','Q4') THEN 1 ELSE 0 END) AS second_half_count, CAST(SUM(CASE WHEN fiscal_quarter IN ('Q1','Q2') THEN 1 ELSE 0 END) AS REAL) / MAX(SUM(CASE WHEN fiscal_quarter IN ('Q3','Q4') THEN 1 ELSE 0 END), 1) AS half_year_ratio FROM employee_metrics GROUP BY department ORDER BY department ``` > **The CASE belongs inside the SUM, not around it** > > The classic miss is CASE WHEN ... THEN SUM(x), which puts the aggregate inside the branch instead of the branch inside the aggregate. The CASE must sit inside the SUM. The subtler miss is dropping the CAST and shipping integer-truncated ratios that all read as 0 or 1. > **One pass beats two stitched queries** > > They are checking whether you reach for one conditional-aggregation pass or fall back to two grouped queries stitched together in the application. The single-pass answer signals you think in set operations. > **One scan, ten groups** > > On roughly 12,000 rows this is one sequential scan and a hash grouping over the 10 distinct departments. Two separate grouped queries would scan the table twice for no benefit; the conditional pivot reads it once. **Two-pass stitch** Run one grouped count filtered to Q1 and Q2, run another filtered to Q3 and Q4, then join the two result sets on department to compute the ratio. Two scans, one join, more code, and a silent gap if a department shows up in only one half. **Single-pass pivot** One scan, two SUM(CASE...) columns, then divide. Every department appears exactly once, a missing half counts as zero for free, and there is nothing to join. --- ## Common follow-up questions - A department reported nothing in the second half of the year. What does your ratio return, and is that the behavior you want? _(Tests the divide-by-zero guard and whether the candidate reasoned about MAX(..., 1) versus NULLIF.)_ - How would you extend this from two half-year buckets to a full per-quarter breakdown in the same single pass? _(Tests whether conditional aggregation generalizes cleanly to more buckets.)_ - New quarterly readings arrive continuously. How would you maintain these per-department counts incrementally instead of re-scanning the whole table each time? _(Tests incremental aggregation thinking versus full re-scan.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/where_the_year_leans) - [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.