Loading…
Loading…
case studies · real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
Browse Case Studies Interview questions// SELECT A CASE STUDY
SQL case study · Meta
Recursively roll up total downstream headcount, org depth, and salary cost for every manager in a self-referencing employee table.
People analytics needs the full picture of a 20-person org stored as a self-referencing table (each employee points at their manager; the CEO points at NULL): for every manager, roll up direct reports, total downstream headcount, org depth below them, and the salary cost of everyone beneath them.
The engine is a recursive CTE — a query that repeatedly joins employees to its own output, walking one level deeper per iteration — producing every (manager, report, depth) triple, then aggregating per manager. Each leader also gets a span-of-control profile: Individual Contributor (no reports), Wide & Flat (many directs, shallow), Narrow & Deep (few directs, deep chains), or Balanced.
The verdict writes itself from the shape: a 4-deep chain hanging off 2-wide spans concentrates 19 heads and ₹32.47L of salary under one executive — decision latency compounds down long chains. Recommend widening the Director layer or flattening a level, and note the skew (10 of 20 people are ICs) before proposing any ratio-based cut.
A single self-referencing employees table with employee_id, employee_name, manager_id (nullable, NULL for the CEO), department, title, and salary.
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
employee_name TEXT,
manager_id INTEGER,
department TEXT,
title TEXT,
salary NUMERIC(12,2)
);Your target
Recursively roll up total downstream headcount, org depth, and salary cost for every manager in a self-referencing employee table.
Loading the expected output…
WITH RECURSIVE org_chain AS (
SELECT
employee_id AS manager_id,
employee_id AS report_id,
0 AS depth
FROM employees
UNION ALL
SELECT
oc.manager_id,
e.employee_id AS report_id,
oc.depth + 1
FROM org_chain oc
JOIN employees e ON e.manager_id = oc.report_id
),
rollup AS (
SELECT
oc.manager_id,
COUNT(*) FILTER (WHERE depth = 1) AS direct_reports,
COUNT(*) FILTER (WHERE depth >= 1) AS total_reports,
MAX(depth) AS max_depth_below,
SUM(e.salary) FILTER (WHERE depth >= 1) AS total_reports_salary_cost
FROM org_chain oc
JOIN employees e ON e.employee_id = oc.report_id
GROUP BY oc.manager_id
)
SELECT
m.employee_id,
m.employee_name,
m.title,
m.department,
COALESCE(r.direct_reports, 0) AS direct_reports,
COALESCE(r.total_reports, 0) AS total_org_size,
COALESCE(r.max_depth_below, 0) AS org_depth_below,
COALESCE(r.total_reports_salary_cost, 0) AS total_reports_salary_cost,
CASE
WHEN COALESCE(r.total_reports, 0) = 0 THEN 'Individual Contributor'
WHEN r.direct_reports >= 4 AND r.max_depth_below <= 1 THEN 'Wide & Flat'
WHEN r.direct_reports <= 2 AND r.max_depth_below >= 2 THEN 'Narrow & Deep'
ELSE 'Balanced'
END AS span_of_control_profile
FROM employees m
LEFT JOIN rollup r ON r.manager_id = m.employee_id
ORDER BY total_org_size DESC NULLS LAST, m.employee_id;Step-by-step Solution:
The recursive org_chain CTE starts with every employee as their own manager_id/report_id pair at depth 0, then recursively joins employees to their manager's chain, incrementing depth by 1 each level — this produces every (manager, downstream report, depth) triple in the org, no matter how deep.
The rollup CTE aggregates that chain per manager_id, using FILTER clauses to separately count direct reports (depth = 1) versus the full downstream org (depth >= 1), plus the max depth and total salary cost of everyone below them.
The final SELECT left-joins every employee against their rollup (employees with no reports get zeros via COALESCE) and classifies each manager's span_of_control_profile based on direct report count versus org depth, flagging 'Narrow & Deep' chains that might be worth flattening.
Computing the reference output…
The company runs Narrow & Deep at the top: a 4-level chain hangs off spans of just 1-2 directs (Reid Chen carries all 19 heads at Rs.32.47L salary cost, Priya Nair 13). Widen the Director layer or flatten a level — and note half the company (10 of 20) are ICs with zero reports, so manager load is concentrated, not shared.
What the interviewer asks next
“Reid carries 19 heads across 4 levels at Rs.32.47L cost — what breaks first in a Narrow & Deep chain?”
Strong answer covers: Span-of-control mechanics: decision latency compounds down long chains and each layer taxes information fidelity — widen the Director layer or flatten a level, with the tradeoff named.
“Half the company (10 of 20) are ICs with zero reports — is management overhead concentrated or shared?”
Strong answer covers: Reading the distribution: management load sits with 10 managers over 10 ICs, so cuts or growth must target specific layers (deep Engineering chain vs lean Sales) rather than across-the-board ratios.
“Priya's Engineering org (13 heads, Rs.20.74L) dwarfs Marcus's Sales org (4 heads) — what question does that raise?”
Strong answer covers: Cost-center curiosity: engineering-heavy ratios are normal pre-scale, but the follow-up is output per crore — headcount rollups only matter against what each org ships.
“Tom Walker and Raj Patel each carry 3 directs (the widest spans) at depth 1, both Balanced — make the Director promotion case for one of them. Which, and why?”
Strong answer covers: Span as promotion evidence: sustained 3-wide management with delivery is the classic Director signal — compare their subtree costs (Rs.4.25L vs Rs.3.82L) and depth to pick, and name what additional quarter of evidence you'd want.
“Marcus Lee's entire Sales org is 4 heads at Rs.5.53L against Engineering's 13 — is Sales understaffed, or is Engineering bloated?”
Strong answer covers: Ratio without revenue is just gossip: headcount mix means nothing until divided by what each org ships — ask for revenue-per-head before endorsing either hiring or cuts.
“Lena Ford (2 directs, Rs.2.67L subtree) vs Tom Walker (3 directs, Rs.4.25L) — same level, different load. How do you calibrate manager scope fairly?”
Strong answer covers: Load calibration: headcount, depth, and subtree cost triangulate true managerial burden — performance systems that ignore scope punish managers handed bigger teams.
Questions test whether your SQL runs. Cases test whether you can own a decision. Learn this once, use it everywhere below.
Restate the grain
One output row = one what? Write it down first — every GROUP BY and join must serve that grain.
Explore before you aggregate
Eyeball every table first: NULL join keys, orphan keys, date coverage. Ten minutes here saves an hour of debugging.
Hand-trace one entity end to end
Compute one row's answer by hand, then demand your query agree. Trust the trace, not the query.
Build incrementally, validate each CTE
Run each CTE standalone and reconcile counts before stacking the next one.
Pressure-test boundaries and empties
Probe cutoffs, zero-activity entities, ties, and empty results — interviewers live on boundaries.
Recommend, don't just report
End with a decision, an owner, and what would change your mind. No recommendation, no case study.