The Full Picture

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L5

Asked in

DoubleVerify

Problem

The people analytics team is assembling a first-quarter scorecard: every employee's name beside the combined Q1 metric value on record for their department. Departments with nothing logged for Q1 should still list their people, each reading zero 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 an outer join dressed as a quarterly report. The real question: can you keep every employee on the sheet when only half the departments logged a Q1 metric? Anyone can join employees to employee_metrics on department and SUM the readings. The tell is where the m.fiscal_quarter = 'Q1' filter goes. Put it in a WHERE and the outer join collapses to an inner one: the 100 people in departments that never reported for Q1 silently fall off the scorecard.

Building the query

Step 1: Keep everyone with a filtered LEFT JOIN

LEFT JOIN employee_metrics m ON e.department = m.department AND m.fiscal_quarter = 'Q1'. The Q1 test rides inside the ON, so departments with no Q1 row still emit their employees, matched to nothing.

Step 2: Zero-fill with COALESCE

COALESCE(SUM(m.metric_value), 0). For an unmatched employee, SUM over zero rows is NULL; COALESCE turns that into a real 0 so the person reads 'none recorded' rather than blank.

Step 3: Roll up to one row per person

GROUP BY e.employee_id, e.emp_name. A department can log several Q1 metrics, so each employee fans out to multiple rows; the GROUP BY plus SUM collapses them into one total. Grouping on employee_id also keeps two people who share a name distinct.

The solution

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

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 also show how many Q1 metrics each department reported? (Tests COUNT vs SUM and NULL handling on the unmatched outer side.)
• If a department reports the same Q1 metric twice, is that double-counting or expected? (Tests awareness of grain and whether repeated readings are intended.)