Split Metric Sums

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

L3

Asked in

Tesla

Problem

Give us the average of each of our people metrics in employee_metrics for the first half of the fiscal year (Q1 and Q2) and for the second half (Q3 and Q4), in whole units with any fraction dropped. Leave out attrition_rate and diversity_ratio: they are rates we read to the decimal, and dropping the fraction would erase that.

One column, two totals.

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

This is a derived-key average with a truncation, dressed up as a half-year people report. employee_metrics is tall: one metric_value column holds every measurement, told apart only by metric_name. Anyone can average by metric. What separates candidates is three quiet decisions: the halves are a key you build, two metrics have to go before the math, and the fraction is dropped from the finished mean, not from each row. Miss any one and every row in the report is off.

Step 1: Remove the two rates in the WHERE

attrition_rate and diversity_ratio are rates read to the decimal, so truncating them throws away exactly the precision they are reported at. Filtering them with metric_name <> 'attrition_rate' AND metric_name <> 'diversity_ratio' drops their rows before any group is formed.

Step 2: Fold four quarters into two halves

A CASE maps 'Q1' and 'Q2' to 'H1' and everything else to 'H2'. Group by metric_name and that derived fiscal_half; grouping by fiscal_quarter gives four rows per metric, not two.

Step 3: Average, then drop the fraction

AVG(metric_value) skips the missing values, so a gap in the log does not drag the mean down. Wrapping the finished average in CAST(... AS INTEGER) truncates it to whole units.

SELECT
  metric_name,
  CASE
    WHEN fiscal_quarter = 'Q1' OR fiscal_quarter = 'Q2' THEN 'H1'
    ELSE 'H2'
  END AS fiscal_half,
  CAST(AVG(metric_value) AS INTEGER) AS avg_value
FROM employee_metrics
WHERE metric_name <> 'attrition_rate'
  AND metric_name <> 'diversity_ratio'
GROUP BY metric_name, fiscal_half

Cast each row, then average

AVG(CAST(metric_value AS INTEGER)) chops the fraction off every measurement first, then averages the stumps. The result is still a decimal, and it sits lower than the true mean by up to a full unit.

Average, then cast

CAST(AVG(metric_value) AS INTEGER) computes the honest mean from the real values and only then drops the partial unit. One truncation, at the end, exactly where the report asks for it.

Common follow-up questions

• How would you show 'H1' and 'H2' side by side as two columns, one row per metric? (Moves the `CASE` inside the aggregate instead of into the grouping key.)
• Finance wants the halves broken out per `fiscal_year`. What changes? (Tests whether the candidate sees it is one more grouping key and nothing else.)
• How would you report the two rates in the same output without truncating them? (Checks whether the candidate keeps a decimal for the rates rather than forcing one truncation onto every metric.)