The Org Chart in Numbers

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

L4

Asked in

Block

Problem

An analyst reviewing the quarterly scorecards suspects some departments only file metrics in alternating quarters, but employee_metrics stores one row per entry, so the gaps never show. Give her one row per department with the number of entries it logged in each of Q1 through Q4, counting every fiscal year together.

Headcount by department, sliced by quarter. Every seat accounted for.

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 reshape dressed up as a scorecard. The skill being probed: can you turn a value that lives in a column (fiscal_quarter) into four fixed output columns in one pass, without losing the quarters that never happened? Anyone can count entries per department and quarter. The trap is stopping there. The long result simply has no row for 'Data' in 'Q1', so the zero the analyst is hunting for is not in it. On this seed every department files in only two of the four quarters, so half the cells the report needs are exactly the ones a long result never emits.

Getting there

Step 1: Settle the grain first

The output has one row per department, so that is the only grouping key. Put fiscal_quarter in the GROUP BY too and you are back to the long shape: up to four rows per department, and none for the empty quarters.

Step 2: Give each quarter its own counter

Inside a department's group, each output column asks every row one question: is this a 'Q1' entry? CASE WHEN fiscal_quarter = 'Q1' THEN 1 ELSE 0 END answers with a 1 or a 0, and SUM adds up the answers. Four such expressions, one per quarter, produce the whole grid from a single scan.

Step 3: Count entries, not values

A cell is how many entries landed in that quarter, not how large they were. metric_value never enters the query, so its 12 NULLs cannot leak into a cell.

SELECT
    department,
    SUM(CASE WHEN fiscal_quarter = 'Q1' THEN 1 ELSE 0 END) AS q1,
    SUM(CASE WHEN fiscal_quarter = 'Q2' THEN 1 ELSE 0 END) AS q2,
    SUM(CASE WHEN fiscal_quarter = 'Q3' THEN 1 ELSE 0 END) AS q3,
    SUM(CASE WHEN fiscal_quarter = 'Q4' THEN 1 ELSE 0 END) AS q4
FROM employee_metrics
GROUP BY department

Grouped by department and quarter

GROUP BY department, fiscal_quarter returns 20 rows on this seed, two per department, and 'Data' has no 'Q1' or 'Q3' row at all. The reader has to infer the zeros from what is absent.

One row per department

Four conditional counts under GROUP BY department return 10 rows, each with q1 through q4 filled in. The empty quarters for 'Data' show up as an explicit 0.

Common follow-up questions

• How would you add a column per `fiscal_year` and quarter pair without hand-writing a dozen expressions? (Dynamic reshaping: generating the column list from the data, or moving the reshape into the reporting layer.)
• What if the scorecard needs the average `metric_value` per quarter instead of the entry count? (A conditional `AVG`: the non-matching branch must now be `NULL`, not 0, or empty quarters drag the average down.)
• A new department has no entries at all. How do you still show it with four zeros? (This needs a department list to start from, because this table alone cannot produce a row for a department it never mentions.)