DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.
DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.
The Question
SQL

Identifying Senior Management Tiers

Given a table employees with columns emp_id, manager_id, and manager_name, define a 'Senior Manager' as an employee who manages at least one individual who is himself/herself a manager, but does not manage anyone who qualifies as a Senior Manager. Write a SQL query to return the name of every Senior Manager and the total count of their direct reports. If an employee reports to multiple managers, they should be counted as a report for each. Sort the results by the report count in descending order. Note: A manager is any employee who has at least one direct report.
PostgreSQL
CTE
Subquery
Set Theory
Aggregations
Questions & Insights

Clarifying Questions

What is the definition of the root node? We assume top-level executives have a NULLmanager_id.
What is the granularity of the table? The table employees is at the (emp_id, manager_id) grain. Given the assumption that an employee can report to two senior managers, the table may contain multiple rows per emp_id.
How is `manager_name` handled? Based on the schema (emp_id, manager_id, manager_name), manager_name refers to the name of the individual identified by manager_id. This allows us to retrieve the senior manager's name directly from the report's row.
Can an employee be their own manager? We assume the hierarchy is a Directed Acyclic Graph (DAG) and no self-referencing loops exist.
Assumptions:
emp_id: Integer, representing the employee.
manager_id: Integer, foreign key to emp_id.
manager_name: String/Text, the name of the person represented by manager_id.
Relationship: N:M (Employee to Manager) is possible based on the prompt's assumption, though 1:N is standard. The query will handle both.
Table Type: Event/Fact-like table representing the reporting structure.

Thinking Process

Identify Managers: A "Manager" is any ID that appears in the manager_id column.
Identify "Managers of Managers" (MoM): These are employees who are manager_ids for another employee who is also a manager_id.
Define "Senior Manager" (SM):
Condition A: Must be a "Manager of Managers" (manages at least one manager).
Condition B: None of their direct reports is a "Senior Manager".
Logical Simplification: In a hierarchical context, the requirement "manages a manager but does not manage a senior manager" typically defines the lowest tier of the manager-of-managers hierarchy. If we define SMs as the bottom-most MoMs (those whose reports are only Level-1 managers or ICs), the logic becomes deterministic.
Step-by-Step CTE Strategy:
all_managers: Get the set of all unique manager_ids.
mom_candidates: Get manager_ids where the report (emp_id) is found in all_managers.
senior_managers: Select from mom_candidates where the manager does not manage anyone who is also in mom_candidates.
Aggregation: Join the identified SM IDs back to the base table to count direct reports and extract the manager_name.
Implementation Breakdown

Problem Set

Requirement: Find senior managers (manage \ge 1 manager, manage 0 senior managers).
Output:manager_name, direct_reports_count.
Ordering: Highest report count first.
Edge Cases: Employees reporting to multiple managers, managers with no senior manager above them, leaf-node ICs.

Approach

CTEs: To modularize the identification of managers and managers-of-managers.
Set Theory / Subqueries: Use IN and NOT IN (or EXISTS/NOT EXISTS) to filter the hierarchy levels.
Aggregations:COUNT and GROUP BY to roll up the direct reports per senior manager.
Dialect: PostgreSQL.

Implementation

Wrap Up

Advanced Topics

Indexing: To optimize this query, a B-Tree index on manager_id and emp_id is essential. In PostgreSQL, a composite index (manager_id, emp_id) would allow for index-only scans during the CTE evaluations.
Recursion: While this puzzle focuses on a specific distance from the leaf (Level 2), a RECURSIVE CTE would be required if the definition of "Senior Manager" depended on the total depth of the tree or if we needed to calculate the entire management chain for every employee.
Data Skew: In very large organizations (e.g., Google), certain "managers" (like HR placeholders or automated system IDs) might have a massive number of reports. This can cause "skew" in joins. Using DISTINCT in CTEs helps reduce the volume of data passed to the final join.
Alternative Syntax: Using EXISTS and NOT EXISTS is often preferred over IN and NOT IN in PostgreSQL when dealing with potential NULL values, as NOT IN will return zero results if the subquery contains a single NULL. Here, WHERE manager_id IS NOT NULL handles this risk.