Where the Year Leans

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

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.

Some teams file early, some file late. See which way each one tips.

Tables

employee_metrics

Columns: metric_id INTEGER, department TEXT, metric_name TEXT, metric_value REAL, fiscal_quarter TEXT, fiscal_year INTEGER

Expected output

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.

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

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

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.)