Coding questions · Fannie Mae
Understand SQL Aggregations and Joins: Key Differences Explained
This question evaluates a candidate's competency in SQL aggregations (COUNT, SUM, AVG, MIN, MAX), join semantics (INNER, LEFT, RIGHT, FULL OUTER), set operations (UNION vs UNION ALL), window functions, view versus table trade-offs, duplicate detection and removal, query optimization techniques, and pandas DataFrame operations (merge, join, concat) within the Data Manipulation (SQL/Python) domain. It is commonly asked because it assesses both conceptual understanding (function purposes and join semantics) and practical application (writing efficient queries, deduplicating data, and translating patterns between SQL and pandas), thereby probing correctness and performance reasoning.
Employees
+----+--------+--------+---------+
| id | name | salary | dept_id |
+----+--------+--------+---------+
| 1 | Alice | 70000 | 1 |
| 2 | Bob | 90000 | 2 |
| 3 | Carol | 80000 | 1 |
+----+--------+--------+---------+
Departments
+----+------------+
| id | dept_name |
+----+------------+
| 1 | Finance |
| 2 | Operations |
| 3 | Marketing |
+----+------------+
Scenario
Querying and manipulating enterprise data stored in relational databases and pandas data frames.
Question
Explain the purpose and differences of COUNT, SUM, AVG, MIN, and MAX aggregation functions in SQL. Describe INNER, LEFT, RIGHT, and FULL OUTER joins and give a use-case for each. Contrast UNION and UNION ALL. When is each preferable? What is a window function in SQL and how does it differ from a standard aggregation? Compare SQL views to physical tables. List advantages and disadvantages. How can you hide duplicate rows in a result set without deleting them? How do you permanently remove duplicate rows from a table? Write an SQL query to solve LeetCode #177 (Nth Highest Salary). List practical techniques to improve SQL query efficiency. In pandas, compare merge, join, and concat. When should each be used?
Hints
Expect to write efficient SQL, reason about duplicates, and pick the right pandas operation.
Aggregation basics overview
Compute the overall COUNT, SUM, AVG, MIN, and MAX of salaries across all employees.
Table Employees
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| name | VARCHAR | NOT NULL |
| salary | INTEGER | NOT NULL |
| dept_id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES Departments(id) |
Sample data:
| dept_id | id | name | salary |
|---|---|---|---|
| 1 | 1 | Alice | 70000 |
| 2 | 2 | Bob | 90000 |
| 1 | 3 | Carol | 80000 |
Expected output
Single-row aggregates over all employees.
| avg_salary | count_employees | max_salary | min_salary | total_salary |
|---|---|---|---|---|
| 80000 | 3 | 90000 | 70000 | 240000 |
Hints
- Use aggregate functions across the entire Employees table.
LEFT JOIN with counts
Return each department with the number of employees, including departments with zero employees.
Table Employees
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| name | VARCHAR | NOT NULL |
| salary | INTEGER | NOT NULL |
| dept_id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES Departments(id) |
Sample data:
| dept_id | id | name | salary |
|---|---|---|---|
| 1 | 1 | Alice | 70000 |
| 2 | 2 | Bob | 90000 |
| 1 | 3 | Carol | 80000 |
Table Departments
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| dept_name | VARCHAR | NOT NULL |
Sample data:
| dept_name | id |
|---|---|
| Finance | 1 |
| Operations | 2 |
| Marketing | 3 |
Expected output
Departments with employee counts, including zero.
| dept_name | employee_count |
|---|---|
| Finance | 2 |
| Operations | 1 |
| Marketing | 0 |
Hints
- LEFT JOIN from Departments to Employees, then GROUP BY department.
RIGHT JOIN via swap
Show all departments and any employee names in them, equivalent to Employees RIGHT JOIN Departments.
Table Employees
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| name | VARCHAR | NOT NULL |
| salary | INTEGER | NOT NULL |
| dept_id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES Departments(id) |
Sample data:
| dept_id | id | name | salary |
|---|---|---|---|
| 1 | 1 | Alice | 70000 |
| 2 | 2 | Bob | 90000 |
| 1 | 3 | Carol | 80000 |
Table Departments
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| dept_name | VARCHAR | NOT NULL |
Sample data:
| dept_name | id |
|---|---|
| Finance | 1 |
| Operations | 2 |
| Marketing | 3 |
Expected output
All departments listed; employees shown where present (RIGHT JOIN semantics).
| dept_name | employee_name |
|---|---|
| Finance | Alice |
| Finance | Carol |
| Marketing | NULL |
| Operations | Bob |
Hints
- Simulate RIGHT JOIN by LEFT JOIN from Departments to Employees.
FULL OUTER JOIN emulate
Return all employee–department pairs where matched, plus departments with no employees (FULL OUTER JOIN behavior for this data).
Table Employees
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| name | VARCHAR | NOT NULL |
| salary | INTEGER | NOT NULL |
| dept_id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES Departments(id) |
Sample data:
| dept_id | id | name | salary |
|---|---|---|---|
| 1 | 1 | Alice | 70000 |
| 2 | 2 | Bob | 90000 |
| 1 | 3 | Carol | 80000 |
Table Departments
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| dept_name | VARCHAR | NOT NULL |
Sample data:
| dept_name | id |
|---|---|
| Finance | 1 |
| Operations | 2 |
| Marketing | 3 |
Expected output
Inner-joined rows plus unmatched departments.
| dept_name | employee_name |
|---|---|
| Finance | Alice |
| Finance | Carol |
| Marketing | NULL |
| Operations | Bob |
Hints
- Combine INNER JOIN with unmatched rows from a LEFT JOIN using UNION ALL.
UNION vs UNION ALL
Show the number of rows produced when combining salaries with UNION versus UNION ALL.
Table Employees
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| name | VARCHAR | NOT NULL |
| salary | INTEGER | NOT NULL |
| dept_id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES Departments(id) |
Sample data:
| dept_id | id | name | salary |
|---|---|---|---|
| 1 | 1 | Alice | 70000 |
| 2 | 2 | Bob | 90000 |
| 1 | 3 | Carol | 80000 |
Expected output
UNION removes duplicates; UNION ALL keeps them.
| row_count | set_type |
|---|---|
| 3 | UNION |
| 6 | UNION ALL |
Hints
- Use UNION for distinct rows, UNION ALL to keep duplicates.
Window functions demo
For each employee, show their department's average salary and their rank by salary within the department (1 = highest).
Table Employees
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| name | VARCHAR | NOT NULL |
| salary | INTEGER | NOT NULL |
| dept_id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES Departments(id) |
Sample data:
| dept_id | id | name | salary |
|---|---|---|---|
| 1 | 1 | Alice | 70000 |
| 2 | 2 | Bob | 90000 |
| 1 | 3 | Carol | 80000 |
Table Departments
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| dept_name | VARCHAR | NOT NULL |
Sample data:
| dept_name | id |
|---|---|
| Finance | 1 |
| Operations | 2 |
| Marketing | 3 |
Expected output
Per-row window metrics alongside each employee.
| dept_avg_salary | dept_name | dept_salary_rank | name | salary |
|---|---|---|---|---|
| 75000 | Finance | 1 | Carol | 80000 |
| 75000 | Finance | 2 | Alice | 70000 |
| 90000 | Operations | 1 | Bob | 90000 |
Hints
- Use AVG() OVER (PARTITION BY ...) for the department average.
- Use DENSE_RANK() OVER (PARTITION BY ... ORDER BY ... DESC) for ranking.
View-like CTE usage
Using a CTE named EmployeeDepartments, list employee id, name, and department name (demonstrating view-like behavior).
Table Employees
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| name | VARCHAR | NOT NULL |
| salary | INTEGER | NOT NULL |
| dept_id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES Departments(id) |
Sample data:
| dept_id | id | name | salary |
|---|---|---|---|
| 1 | 1 | Alice | 70000 |
| 2 | 2 | Bob | 90000 |
| 1 | 3 | Carol | 80000 |
Table Departments
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| dept_name | VARCHAR | NOT NULL |
Sample data:
| dept_name | id |
|---|---|
| Finance | 1 |
| Operations | 2 |
| Marketing | 3 |
Expected output
CTE produces a reusable result set like a temporary view.
| dept_name | id | name |
|---|---|---|
| Finance | 1 | Alice |
| Operations | 2 | Bob |
| Finance | 3 | Carol |
Hints
- Use WITH to define a named subquery and then select from it.
Hide duplicates with DISTINCT
List distinct department names that have at least one employee.
Table Employees
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| name | VARCHAR | NOT NULL |
| salary | INTEGER | NOT NULL |
| dept_id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES Departments(id) |
Sample data:
| dept_id | id | name | salary |
|---|---|---|---|
| 1 | 1 | Alice | 70000 |
| 2 | 2 | Bob | 90000 |
| 1 | 3 | Carol | 80000 |
Table Departments
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| dept_name | VARCHAR | NOT NULL |
Sample data:
| dept_name | id |
|---|---|
| Finance | 1 |
| Operations | 2 |
| Marketing | 3 |
Expected output
Distinct department names appearing in Employees.
| dept_name |
|---|
| Finance |
| Operations |
Hints
- DISTINCT removes duplicate department names produced by the join.
Identify duplicates to delete
Identify duplicate employee rows by (name, dept_id, salary) and return only the extra duplicates (beyond the first) that you would delete.
Table Employees
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| name | VARCHAR | NOT NULL |
| salary | INTEGER | NOT NULL |
| dept_id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES Departments(id) |
Sample data:
| dept_id | id | name | salary |
|---|---|---|---|
| 1 | 1 | Alice | 70000 |
| 2 | 2 | Bob | 90000 |
| 1 | 3 | Carol | 80000 |
Expected output
Rows that would be considered duplicates (none in the sample).
Hints
- Use ROW_NUMBER over a partition to mark duplicates and filter rn > 1.
Nth Highest Salary
2nd-Highest Distinct Salary
You are given an Employees table:
| column | type | notes |
|---|---|---|
| id | INTEGER | primary key |
| name | VARCHAR | employee name |
| salary | INTEGER | employee salary |
| dept_id | INTEGER | department the employee is in |
Write a query that returns the 2nd-highest distinct salary across all employees.
Rules:
- Rank salaries by distinct value, so duplicate salaries count as a single rank (e.g. if two people earn the top salary, the next lower salary is still the 2nd-highest distinct value).
- Return exactly one row with a single column named
getNthHighestSalaryholding that salary. - If fewer than 2 distinct salaries exist, the query should return one row whose value is
NULL.
Table Employees
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| name | VARCHAR | NOT NULL |
| salary | INTEGER | NOT NULL |
| dept_id | INTEGER | NOT NULL |
Sample data:
| dept_id | id | name | salary |
|---|---|---|---|
| 1 | 1 | Alice | 70000 |
| 2 | 2 | Bob | 90000 |
| 1 | 3 | Carol | 80000 |
| 2 | 4 | Dave | 80000 |
| 1 | 5 | Eve | 60000 |
Expected output
One row, one column getNthHighestSalary = 80000 (the 2nd-highest distinct salary; 90000 is highest, the tied 80000 pair is 2nd).
| getNthHighestSalary |
|---|
| 80000 |
Hints
- DENSE_RANK() (not RANK or ROW_NUMBER) collapses duplicate salaries into one rank with no gaps.
- Filter the ranked rows to rk = 2, then aggregate so you always return exactly one row.
- Wrapping the result in MAX(...) yields NULL automatically when there is no 2nd distinct salary.
Top N salaries efficiently
Top 2 Distinct Salaries
You are given an Employees table holding one row per employee, with their salary.
Write a query that returns the 2 highest distinct salary values in the company, in descending order. (The original prompt asked for the "top N" distinct salaries; here N is pinned to 2 so the question is concretely answerable.)
Requirements:
- Treat duplicate salaries as a single value (e.g. if two employees both earn 90000, that salary counts once).
- The result must have a single column named
salary. - Return at most 2 rows: the highest distinct salary first, then the second-highest.
- Order the output by
salaryin descending order.
Table Employees
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| name | VARCHAR | NOT NULL |
| salary | INTEGER | NOT NULL |
| dept_id | INTEGER | NOT NULL |
Sample data:
| dept_id | id | name | salary |
|---|---|---|---|
| 1 | 1 | Alice | 70000 |
| 2 | 2 | Bob | 90000 |
| 1 | 3 | Carol | 80000 |
| 2 | 4 | Dave | 90000 |
| 1 | 5 | Eve | 70000 |
Expected output
The 2 highest distinct salary values in descending order: 90000 then 80000.
| salary |
|---|
| 90000 |
| 80000 |
Hints
- Use DISTINCT so duplicate salaries collapse into one value before you rank them.
- ORDER BY salary DESC then LIMIT 2 gives you the top two without scanning beyond what you need.
- In Postgres the row cap is a literal: write LIMIT 2, never LIMIT :N.
Pandas merge equivalent (INNER JOIN)
Produce the same result as pandas merge(Employees, Departments, left_on='dept_id', right_on='id', how='inner'). Return id, name, salary, dept_id, dept_name.
Table Employees
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| name | VARCHAR | NOT NULL |
| salary | INTEGER | NOT NULL |
| dept_id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES Departments(id) |
Sample data:
| dept_id | id | name | salary |
|---|---|---|---|
| 1 | 1 | Alice | 70000 |
| 2 | 2 | Bob | 90000 |
| 1 | 3 | Carol | 80000 |
Table Departments
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| dept_name | VARCHAR | NOT NULL |
Sample data:
| dept_name | id |
|---|---|
| Finance | 1 |
| Operations | 2 |
| Marketing | 3 |
Expected output
Inner join result equivalent to pandas merge with how='inner'.
| dept_id | dept_name | id | name | salary |
|---|---|---|---|---|
| 1 | Finance | 1 | Alice | 70000 |
| 2 | Operations | 2 | Bob | 90000 |
| 1 | Finance | 3 | Carol | 80000 |
Hints
- Pandas merge with how='inner' maps to SQL INNER JOIN.
Pandas join equivalent (LEFT JOIN)
Produce the same result as df_employees.join(df_departments, on='dept_id', how='left'): return all employees and their dept_name if available.
Table Employees
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| name | VARCHAR | NOT NULL |
| salary | INTEGER | NOT NULL |
| dept_id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES Departments(id) |
Sample data:
| dept_id | id | name | salary |
|---|---|---|---|
| 1 | 1 | Alice | 70000 |
| 2 | 2 | Bob | 90000 |
| 1 | 3 | Carol | 80000 |
Table Departments
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| dept_name | VARCHAR | NOT NULL |
Sample data:
| dept_name | id |
|---|---|
| Finance | 1 |
| Operations | 2 |
| Marketing | 3 |
Expected output
Left join of employees to departments.
| dept_name | id | name |
|---|---|---|
| Finance | 1 | Alice |
| Operations | 2 | Bob |
| Finance | 3 | Carol |
Hints
- Pandas DataFrame.join with how='left' maps to SQL LEFT JOIN.
Pandas concat equivalent with UNION ALL
Vertically concatenate two employee subsets without removing duplicates: (a) employees in the Finance department, and (b) employees with salary >= 80000. Return a source label, id, name, and dept_name. Use UNION ALL so an employee who appears in both subsets appears twice with different source labels.
Table Departments
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| dept_name | VARCHAR | NOT NULL |
Sample data:
| dept_name | id |
|---|---|
| Finance | 1 |
| Operations | 2 |
| Marketing | 3 |
Table Employees
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| name | VARCHAR | NOT NULL |
| salary | INTEGER | NOT NULL |
| dept_id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES Departments(id) |
Sample data:
| dept_id | id | name | salary |
|---|---|---|---|
| 1 | 1 | Alice | 70000 |
| 2 | 2 | Bob | 90000 |
| 1 | 3 | Carol | 80000 |
Expected output
Union-all style vertical stacking with a source label.
| dept_name | id | name | source |
|---|---|---|---|
| Finance | 1 | Alice | finance |
| Finance | 3 | Carol | finance |
| Operations | 2 | Bob | high_salary |
| Finance | 3 | Carol | high_salary |
Hints
- Use
UNION ALLto stack two SELECT results without deduplication. - Make sure both SELECT branches return the same columns in the same order.