The Full Picture

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

L5

Asked in

DoubleVerify

Problem

The people analytics team is assembling a first-quarter department scorecard, putting each department's total payroll beside the combined Q1 metric value on record for that department. Departments with nothing logged for Q1 still belong on it, reading zero for the metric rather than dropping off.

Two tables know different things about the same people. Combine them.

Tables

employees

Columns: employee_id INTEGER, emp_name TEXT, department TEXT, salary REAL, manager_id INTEGER, hire_date TEXT, user_id INTEGER

employee_metrics

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

Expected output

Worked solution and explanation

What this problem is really about

This is a two-grain problem dressed as a scorecard. Payroll lives on employees, one row per person. The Q1 readings live on employee_metrics, many rows per department. Join the two on department and each of the five departments that logged Q1 comes back as 200 rows: 20 employees times 10 Q1 readings. SUM(e.salary) then counts every salary 10 times, and SUM(m.metric_value) counts every reading 20 times, and the numbers still look plausible. Anyone can write the join. The skill is noticing that each measure has to be totalled at the side that holds it before the two meet.

Building the query

Step 1: Total payroll before the join

SELECT department, SUM(salary) AS total_payroll FROM employees GROUP BY department sits in the FROM as payroll, giving ten rows, one per department. A CTE would read the same; the point is that salary is summed before anything can repeat it.

Step 2: Join the Q1 rows inside the ON

LEFT JOIN employee_metrics m ON m.department = payroll.department AND m.fiscal_quarter = 'Q1'. Each department meets only its own Q1 readings, and the five departments that logged nothing keep one row with an empty match.

Step 3: Sum the readings and zero-fill

Group by payroll.department, payroll.total_payroll so the payroll is carried as a key and never summed again. COALESCE(SUM(m.metric_value), 0) turns an empty match into 0 instead of NULL.

The solution

SELECT payroll.department,
       payroll.total_payroll,
       COALESCE(SUM(m.metric_value), 0) AS q1_metric_total
FROM (
    SELECT department, SUM(salary) AS total_payroll
    FROM employees
    GROUP BY department
) payroll
LEFT JOIN employee_metrics m
  ON m.department = payroll.department
 AND m.fiscal_quarter = 'Q1'
GROUP BY payroll.department, payroll.total_payroll

Join, then total

Every employee row meets every Q1 reading of the department. SUM(e.salary) sees each salary 10 times and SUM(m.metric_value) sees each reading 20 times in the five Q1 departments.

Total, then join

payroll has one row per department, so the join attaches at most 10 readings to a single total_payroll. Each measure is summed once, on its own side.

SELECT e.department,
       SUM(e.salary) AS total_payroll,
       COALESCE(SUM(m.metric_value), 0) AS q1_metric_total
FROM employees e
LEFT JOIN employee_metrics m
  ON m.department = e.department
 AND m.fiscal_quarter = 'Q1'
GROUP BY e.department

Common follow-up questions

• What changes if you need Q1 of a single `fiscal_year` only? (Tests whether they add the year predicate to the `ON` clause, not a `WHERE`.)
• How would you add each department's headcount without the Q1 rows inflating it? (Tests counting inside `payroll`, on the `employees` side, rather than after the join.)
• If metrics were logged per employee instead of per department, what would the join key and grain become? (Tests re-deriving which side is the one and which the many.)