Quick Overview

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.

Understand SQL Aggregations and Joins: Key Differences Explained

Company: Fannie Mae

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Onsite

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.

Overview: 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.

Aggregation basics overview

Compute the overall COUNT, SUM, AVG, MIN, and MAX of salaries across all employees.

Tables

Employees(id INTEGER, name VARCHAR, salary INTEGER, dept_id INTEGER)

Hints

  1. 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.

Tables

Employees(id INTEGER, name VARCHAR, salary INTEGER, dept_id INTEGER)

Departments(id INTEGER, dept_name VARCHAR)

Hints

  1. 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.

Tables

Employees(id INTEGER, name VARCHAR, salary INTEGER, dept_id INTEGER)

Departments(id INTEGER, dept_name VARCHAR)

Hints

  1. 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).

Tables

Employees(id INTEGER, name VARCHAR, salary INTEGER, dept_id INTEGER)

Departments(id INTEGER, dept_name VARCHAR)

Hints

  1. 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.

Tables

Employees(id INTEGER, name VARCHAR, salary INTEGER, dept_id INTEGER)

Hints

  1. 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).

Tables

Employees(id INTEGER, name VARCHAR, salary INTEGER, dept_id INTEGER)

Departments(id INTEGER, dept_name VARCHAR)

Hints

  1. Use AVG() OVER (PARTITION BY ...) for the department average.
  2. 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).

Tables

Employees(id INTEGER, name VARCHAR, salary INTEGER, dept_id INTEGER)

Departments(id INTEGER, dept_name VARCHAR)

Hints

  1. 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.

Tables

Employees(id INTEGER, name VARCHAR, salary INTEGER, dept_id INTEGER)

Departments(id INTEGER, dept_name VARCHAR)

Hints

  1. 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.

Tables

Employees(id INTEGER, name VARCHAR, salary INTEGER, dept_id INTEGER)

Hints

  1. 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 `getNthHighestSalary`** holding that salary. - If fewer than 2 distinct salaries exist, the query should return one row whose value is `NULL`.

Tables

Employees(id INTEGER, name VARCHAR, salary INTEGER, dept_id INTEGER)

Hints

  1. DENSE_RANK() (not RANK or ROW_NUMBER) collapses duplicate salaries into one rank with no gaps.
  2. Filter the ranked rows to rk = 2, then aggregate so you always return exactly one row.

Loading coding console...