Complete Interview Preparation Guide

8
32 7 New DE 3
Total Questions Categories Questions Difficulty Levels

■ SQL Fundamentals 5 questions

■ Aggregation 4 questions

■ Joins 4 questions

■ Subqueries & CTEs 4 questions

■ Window Functions 4 questions

■ Junior Data Engineer ★ NEW 9 questions

■ String & Format 2 questions

SQL & Data Engineering Interview Prep Page 1 of 30

 ■ SQL Fundamentals

Q1. Find Duplicate Rows

Easy Interview Prep (0-3 Yrs)

PROBLEM
Find all duplicate rows in the employees table based on name and department. Return the duplicated
values and their occurrence count.

INPUT SCHEMA

employees

emp_id INTEGER

name VARCHAR

department VARCHAR

salary INTEGER

EXPECTED OUTPUT

1 John Engineering 3 2 Sara Marketing 2

■ TIP: GROUP BY the columns that should be unique, then HAVING COUNT(*) > 1.

■ HINT
SELECT name, department, COUNT(*) FROM employees GROUP BY name, department HAVING COUNT(*) > 1

■ SOLUTION

1 SELECT name, department, COUNT(*) AS count 2 FROM employees 3 GROUP BY name, department 4
HAVING COUNT(*) > 1;

■ EXPLANATION
GROUP BY clusters matching rows. HAVING COUNT(*) > 1 keeps only groups appearing more than
once.

Q2. Second Highest Salary

Intermediate Interview Prep (0-3 Yrs)

PROBLEM
Find the second highest salary from the employees table. Return NULL if no second exists.

INPUT SCHEMA

employees

emp_id INTEGER

name VARCHAR

salary INTEGER

EXPECTED OUTPUT

SQL & Data Engineering Interview Prep Page 2 of 30

 1 85000

■ TIP: Two approaches — subquery excluding the max, or DISTINCT + LIMIT 1 OFFSET 1.

■ HINT
SELECT MAX(salary) WHERE salary < (SELECT MAX(salary)...) OR use DISTINCT + ORDER BY DESC LIMIT 1
OFFSET 1

■ SOLUTION

1 -- Option 1: Subquery 2 SELECT MAX(salary) 3 FROM employees 4 WHERE salary < (SELECT
MAX(salary) FROM employees); 5 6 -- Option 2: DISTINCT + OFFSET 7 SELECT DISTINCT salary 8
FROM employees 9 ORDER BY salary DESC 10 LIMIT 1 OFFSET 1;

■ EXPLANATION
Subquery finds the highest, outer MAX finds highest below it. OFFSET 1 skips the first result.

Q3. WHERE vs HAVING

Easy Interview Concept

PROBLEM
What is the difference between WHERE and HAVING? Write a query demonstrating both: find
departments with more than 5 active employees, showing only departments where avg salary > 50000.

INPUT SCHEMA

employees

emp_id INTEGER

name VARCHAR

department VARCHAR

salary INTEGER

status VARCHAR

EXPECTED OUTPUT

1 Engineering 72000 2 Sales 68500

■ WHERE filters individual rows BEFORE grouping. HAVING filters groups AFTER aggregation.

■ HINT
WHERE status = active (row filter). GROUP BY department. HAVING COUNT > 5 AND AVG(salary) > 50000 (group
filter).

■ SOLUTION

1 SELECT department, AVG(salary) AS avg_salary 2 FROM employees 3 WHERE status = 'active' 4

GROUP BY department 5 HAVING COUNT(*) > 5 6 AND AVG(salary) > 50000;

■ EXPLANATION
WHERE pre-filters rows before aggregation. HAVING filters the resulting groups. Both can coexist in a
single query.

SQL & Data Engineering Interview Prep Page 3 of 30

 Q4. DELETE vs TRUNCATE vs DROP
Easy Interview Concept

PROBLEM
Explain the difference between DELETE, TRUNCATE, and DROP. Write example SQL for each operation
on the orders table.

INPUT SCHEMA

orders

order_id INTEGER

customer_id INTEGER

amount DECIMAL

status VARCHAR

EXPECTED OUTPUT

1 (Conceptual — each command removes data differently)

■ DELETE: row-level with WHERE. TRUNCATE: all rows fast. DROP: removes the entire table structure.

■ HINT
DELETE uses WHERE, is logged, rollback-safe. TRUNCATE is fast, resets identity. DROP removes the table
entirely.

■ SOLUTION

1 -- Delete specific rows (can use WHERE, logged, rollbackable) 2 DELETE FROM orders WHERE
status = 'cancelled'; 3 4 -- Remove all rows fast (no WHERE, resets identity) 5 TRUNCATE
TABLE orders; 6 7 -- Remove the entire table structure + data 8 DROP TABLE orders;

■ EXPLANATION
DELETE is logged row-by-row and supports WHERE. TRUNCATE is faster and resets auto-increment.
DROP removes the table entirely — irreversible without backup.

Q5. INNER JOIN vs LEFT JOIN vs FULL OUTER JOIN

Easy Interview Concept

PROBLEM
Demonstrate all three JOIN types using an employees and departments table. Show which
employees/departments appear in each result.

INPUT SCHEMA

employees

emp_id INTEGER

name VARCHAR

dept_id INTEGER

departments

SQL & Data Engineering Interview Prep Page 4 of 30

 dept_id INTEGER

dept_name VARCHAR

EXPECTED OUTPUT

1 INNER: matched rows only 2 LEFT: all employees + dept info (NULL if no dept) 3 FULL: all
from both sides

■ INNER JOIN = intersection. LEFT JOIN = all left + matches. FULL OUTER JOIN = all from both tables.

■ HINT
Use ON e.dept_id = d.dept_id. Switch JOIN type to get different results.

■ SOLUTION

1 -- INNER JOIN: only matching rows 2 SELECT [Link], d.dept_name 3 FROM employees e 4 INNER
JOIN departments d ON e.dept_id = d.dept_id; 5 6 -- LEFT JOIN: all employees, NULL if no
dept 7 SELECT [Link], d.dept_name 8 FROM employees e 9 LEFT JOIN departments d ON e.dept_id
= d.dept_id; 10 11 -- FULL OUTER JOIN: all from both tables 12 SELECT [Link], d.dept_name
13 FROM employees e 14 FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;

■ EXPLANATION
INNER: only matched rows. LEFT: all left table rows, NULLs where no match. FULL OUTER: all rows from
both, NULLs where no match on either side.

SQL & Data Engineering Interview Prep Page 5 of 30

 ■ Aggregation

Q1. Maximum Total Earnings

Intermediate HackerRank Classic

PROBLEM
Find the maximum total earnings (salary × months) for all employees, and the count of employees who
have that maximum. Print as space-separated integers.

INPUT SCHEMA

Employee

employee_id INTEGER

name VARCHAR

months INTEGER

salary INTEGER

EXPECTED OUTPUT

1 69952 1

■ TIP: GROUP BY the computed earnings expression, then ORDER BY DESC LIMIT 1.

■ HINT
GROUP BY (salary * months), ORDER BY DESC LIMIT 1, COUNT(*) gives employees with that max.

■ SOLUTION

1 SELECT (salary * months) AS earnings, COUNT(*) 2 FROM Employee 3 GROUP BY earnings 4

ORDER BY earnings DESC 5 LIMIT 1;

■ EXPLANATION
salary × months computed inline. Grouping clusters identical totals. COUNT(*) per group. LIMIT 1 picks
the maximum group.

Q2. Salary Miscalculation (REPLACE + CEIL)

Intermediate HackerRank Classic

PROBLEM
A keyboard's 0 key was broken so all zeros were stripped from salary values. Find CEIL(actual_avg -
broken_avg).

INPUT SCHEMA

EMPLOYEES

ID INTEGER

Name VARCHAR

Salary INTEGER

SQL & Data Engineering Interview Prep Page 6 of 30

 EXPECTED OUTPUT

1 2061

■ TIP: REPLACE(Salary, 0, empty string) strips zeros. CEIL rounds up.

■ HINT
CEIL(AVG(Salary) - AVG(REPLACE(Salary, 0, empty string)))

■ SOLUTION

1 SELECT CEIL(AVG(Salary) - AVG(REPLACE(Salary, '0', ''))) 2 FROM EMPLOYEES;

■ EXPLANATION
REPLACE strips zero digits. Difference between true and broken averages gives the error. CEIL rounds
up.

Q3. Top Earning Departments

Intermediate Interview Prep

PROBLEM
Find departments whose total salary exceeds the average total salary across all departments. Return
department_name, total_salary, employee_count. Order by total_salary DESC.

INPUT SCHEMA

Departments

department_id INTEGER

department_name VARCHAR

Employees

employee_id INTEGER

name VARCHAR

department_id INTEGER

salary INTEGER

EXPECTED OUTPUT

1 Sales 23000 2 2 Engineering 15000 2

■ TIP: Nested subquery in HAVING computes the global average of department totals.

■ HINT
HAVING SUM(salary) > (SELECT AVG(dt) FROM (SELECT SUM(salary) AS dt FROM Employees GROUP BY
dept_id) t)

■ SOLUTION

SQL & Data Engineering Interview Prep Page 7 of 30

 1 SELECT d.department_name, SUM([Link]) AS total_salary, 2 COUNT(e.employee_id) AS
employee_count 3 FROM Departments d 4 JOIN Employees e ON d.department_id = e.department_id
5 GROUP BY d.department_name 6 HAVING SUM([Link]) > ( 7 SELECT AVG(dt) FROM ( 8 SELECT
SUM(salary) AS dt 9 FROM Employees GROUP BY department_id 10 ) totals 11 ) 12 ORDER BY
SUM([Link]) DESC, d.department_name ASC;

■ EXPLANATION
Nested subquery sums per dept then takes AVG. HAVING filters departments above that average.

Q4. Median of Northern Latitudes

Hard HackerRank Classic

PROBLEM
Query the median of LAT_N from STATION. Round to 4 decimal places. Median = value where equal
counts exist above and below.

INPUT SCHEMA

STATION

ID INTEGER

CITY VARCHAR

LAT_N DECIMAL

LONG_W DECIMAL

EXPECTED OUTPUT

1 83.8913

■ TIP: Median is where COUNT(values below) equals COUNT(values above).

■ HINT
Correlated subqueries: WHERE (SELECT COUNT WHERE LAT_N < S.LAT_N) = (SELECT COUNT WHERE
LAT_N > S.LAT_N)

■ SOLUTION

1 SELECT ROUND(S.LAT_N, 4) 2 FROM STATION S 3 WHERE (SELECT COUNT(LAT_N) FROM STATION WHERE
LAT_N < S.LAT_N) = 4 (SELECT COUNT(LAT_N) FROM STATION WHERE LAT_N > S.LAT_N);

■ EXPLANATION
For each row correlated subqueries count values above and below. Equal counts = median position.

SQL & Data Engineering Interview Prep Page 8 of 30

 ■ Joins

Q1. Employees Earning More Than Manager

Intermediate Interview Prep (0-3 Yrs)

PROBLEM
Find employees whose salary is greater than their manager salary. Return employee_name, salary,
manager_name, manager_salary.

INPUT SCHEMA

employees

emp_id INTEGER

name VARCHAR

salary INTEGER

manager_id INTEGER

EXPECTED OUTPUT

1 Bob 90000 Alice 75000

■ TIP: Self-join the table twice — once as employee (e), once as manager (m).

■ HINT
FROM employees e JOIN employees m ON e.manager_id = m.emp_id WHERE [Link] > [Link]

■ SOLUTION

1 SELECT [Link] AS employee_name, [Link], 2 [Link] AS manager_name, [Link] AS

manager_salary 3 FROM employees e 4 JOIN employees m ON e.manager_id = m.emp_id 5 WHERE
[Link] > [Link];

■ EXPLANATION
Self-join aliases the same table as employee and manager. JOIN links manager_id to emp_id. WHERE
filters the comparison.

Q2. Products Never Sold

Easy Interview Prep (0-3 Yrs)

PROBLEM
Find all products that have never appeared in any order. Return all product columns.

INPUT SCHEMA

products

id INTEGER

product_name VARCHAR

price DECIMAL

SQL & Data Engineering Interview Prep Page 9 of 30

 order_items

item_id INTEGER

order_id INTEGER

product_id INTEGER

EXPECTED OUTPUT

1 5 Wireless Mouse 29.99 2 9 Desk Lamp 14.99

■ TIP: LEFT JOIN + WHERE right side IS NULL is the classic anti-join pattern.

■ HINT
LEFT JOIN order_items ON [Link] = o.product_id. WHERE o.product_id IS NULL means no match.

■ SOLUTION

1 SELECT p.* 2 FROM products p 3 LEFT JOIN order_items o ON [Link] = o.product_id 4 WHERE
o.product_id IS NULL;

■ EXPLANATION
LEFT JOIN keeps all products. NULL on right side means no order exists. WHERE IS NULL filters to
unmatched products only.

Q3. Students Grade Report (BETWEEN Join)

Intermediate HackerRank Classic

PROBLEM
Map students to grades using mark ranges. Show NULL for grade < 8, else show Name. Order by Grade
DESC, then by Name (grade>=8) or Marks (grade<8).

INPUT SCHEMA

Students

ID INTEGER

Name VARCHAR

Marks INTEGER

Grades

Grade INTEGER

Min_Mark INTEGER

Max_Mark INTEGER

EXPECTED OUTPUT

1 Britney 10 95 2 Hermione 10 91 3 ...

■ TIP: Join using BETWEEN — no shared key column. Grade is determined by mark range.

■ HINT
JOIN ON Marks BETWEEN Min_Mark AND Max_Mark. CASE WHEN Grade < 8 THEN NULL ELSE Name END.

SQL & Data Engineering Interview Prep Page 10 of 30

 ■ SOLUTION

1 SELECT 2 CASE WHEN [Link] < 8 THEN NULL ELSE [Link] END AS Name, 3 [Link], [Link] 4
FROM Students s 5 JOIN Grades g ON [Link] BETWEEN g.Min_Mark AND g.Max_Mark 6 ORDER BY
[Link] DESC, 7 CASE WHEN [Link] >= 8 THEN [Link] END ASC, 8 CASE WHEN [Link] < 8 THEN
[Link] END ASC;

■ EXPLANATION
BETWEEN join maps marks to grade range. Two CASE expressions in ORDER BY activate different sort
logic per tier.

Q4. INNER JOIN vs EXISTS

Intermediate Interview Prep (0-3 Yrs)

PROBLEM
Find all customers who have placed at least one order. Show the difference between using INNER JOIN
and EXISTS. Return customer_name.

INPUT SCHEMA

customers

id INTEGER

name VARCHAR

orders

order_id INTEGER

customer_id INTEGER

amount DECIMAL

EXPECTED OUTPUT

1 Alice 2 Bob 3 Carol

■ TIP: EXISTS stops at the first match — more efficient for large datasets than INNER JOIN which may produce
duplicates.

■ HINT
INNER JOIN produces duplicate customers if they have multiple orders. EXISTS returns each customer only once.

■ SOLUTION

1 -- INNER JOIN (may produce duplicates) 2 SELECT DISTINCT [Link] 3 FROM customers c 4
INNER JOIN orders o ON [Link] = o.customer_id; 5 6 -- EXISTS (cleaner, stops at first match)
7 SELECT name 8 FROM customers c 9 WHERE EXISTS ( 10 SELECT 1 FROM orders o 11 WHERE
o.customer_id = [Link] 12 );

■ EXPLANATION
INNER JOIN may duplicate rows if multiple orders exist per customer — requires DISTINCT. EXISTS
stops scanning at the first match, making it faster for large datasets.

SQL & Data Engineering Interview Prep Page 11 of 30

 ■ Subqueries & CTEs

Q1. What is a CTE and When to Use It

Intermediate Interview Concept

PROBLEM
Explain CTEs with an example. Write a CTE to find top-paid employees (salary > 100000), then select
from it. Show why a CTE is better than a subquery here.

INPUT SCHEMA

employees

emp_id INTEGER

name VARCHAR

salary INTEGER

department VARCHAR

EXPECTED OUTPUT

1 Alice Engineering 150000 2 Bob Sales 120000

■ TIP: CTEs (WITH clause) create named temporary result sets — readable and reusable within the same query.

■ HINT
WITH cte_name AS (SELECT ... WHERE salary > 100000) SELECT * FROM cte_name

■ SOLUTION

1 WITH HighEarners AS ( 2 SELECT emp_id, name, salary, department 3 FROM employees 4 WHERE
salary > 100000 5 ) 6 SELECT name, department, salary 7 FROM HighEarners 8 ORDER BY salary
DESC;

■ EXPLANATION
CTE creates a named temporary result set reusable in the same query. Benefits: improves readability,
avoids nested subqueries, allows recursion. Requires MySQL 8.0+.

SQL & Data Engineering Interview Prep Page 12 of 30

 Q2. Employees Above Dept Avg (Correlated)
Hard Advanced SQL Interview

PROBLEM
Find all employees whose salary is above their own department average. Use a correlated subquery.

INPUT SCHEMA

employees

emp_id INTEGER

name VARCHAR

department VARCHAR

salary INTEGER

EXPECTED OUTPUT

1 Bob Engineering 95000 2 Sara Marketing 72000

■ TIP: A correlated subquery references the outer query — here it recalculates avg for each employee own
department.

■ HINT
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE [Link] = [Link])

■ SOLUTION

1 SELECT [Link], [Link], [Link] 2 FROM employees e1 3 WHERE [Link] > ( 4

SELECT AVG(salary) 5 FROM employees e2 6 WHERE [Link] = [Link] 7 );

■ EXPLANATION
Correlated subquery runs once per outer row, computing avg for that employee department. Rows above
their department avg are returned.

Q3. Recursive CTE — Org Hierarchy

Hard Interview Prep (0-3 Yrs)

PROBLEM
Use a recursive CTE to traverse the employee-manager hierarchy. Return each employee id, parent_id,
and their depth level starting from 1 at the root.

INPUT SCHEMA

employees

id INTEGER

name VARCHAR

parent_id INTEGER (NULL=root)

EXPECTED OUTPUT

1 1 NULL 1 2 2 1 2 3 3 1 2 4 4 2 3

SQL & Data Engineering Interview Prep Page 13 of 30

 ■ TIP: Recursive CTEs have two parts: anchor (base case) and recursive member joined with UNION ALL.

■ HINT
Anchor: WHERE parent_id IS NULL. Recursive: JOIN org_chart ON e.parent_id = [Link], increment level.

■ SOLUTION

1 WITH RECURSIVE org_chart AS ( 2 SELECT id, parent_id, 1 AS level 3 FROM employees 4 WHERE
parent_id IS NULL 5 UNION ALL 6 SELECT [Link], e.parent_id, [Link] + 1 7 FROM employees e 8
JOIN org_chart oc ON e.parent_id = [Link] 9 ) 10 SELECT * FROM org_chart;

■ EXPLANATION
Anchor selects the root. Recursive member joins children incrementing level. UNION ALL accumulates all
levels until no more children.

Q4. Hacker Total Score (CTE + MAX)

Intermediate HackerRank Classic

PROBLEM
Each hacker total score = sum of their best score per challenge. Print hacker_id, name, total. Exclude
zero-total hackers. Order by total DESC.

INPUT SCHEMA

Hackers

hacker_id INTEGER

name VARCHAR

Submissions

submission_id INTEGER

hacker_id INTEGER

challenge_id INTEGER

score INTEGER

EXPECTED OUTPUT

1 72796 Kimberly 56 2 37068 Rose 50

■ TIP: A hacker may submit many times per challenge — only their MAX per challenge counts toward the total.

■ HINT
CTE: MAX(score) GROUP BY hacker_id, challenge_id. Then SUM those maxes per hacker.

■ SOLUTION

1 WITH best AS ( 2 SELECT hacker_id, challenge_id, 3 MAX(score) AS max_score 4 FROM

Submissions 5 GROUP BY hacker_id, challenge_id 6 ) 7 SELECT h.hacker_id, [Link], 8
SUM(b.max_score) AS total 9 FROM Hackers h 10 JOIN best b ON h.hacker_id = b.hacker_id 11
GROUP BY h.hacker_id, [Link] 12 HAVING SUM(b.max_score) > 0 13 ORDER BY SUM(b.max_score)
DESC, h.hacker_id ASC;

■ EXPLANATION

SQL & Data Engineering Interview Prep Page 14 of 30

 CTE picks best per (hacker, challenge) pair. Summing those gives true total. Without CTE every
submission would inflate the score.

SQL & Data Engineering Interview Prep Page 15 of 30

 ■ Window Functions

Q1. RANK vs DENSE_RANK vs ROW_NUMBER

Intermediate Advanced SQL Interview

PROBLEM
Show the difference between ROW_NUMBER(), RANK(), and DENSE_RANK() in a single query. Rank all
employees by salary descending.

INPUT SCHEMA

employees

emp_id INTEGER

name VARCHAR

salary INTEGER

EXPECTED OUTPUT

1 Alice 100000 1 1 1 2 Bob 100000 2 1 1 3 Carol 85000 3 3 2

■ ROW_NUMBER: always unique. RANK: skips after ties (1,1,3). DENSE_RANK: no skips (1,1,2).

■ HINT
All three use OVER (ORDER BY salary DESC). The difference shows when two rows have the same salary.

■ SOLUTION

1 SELECT name, salary, 2 ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num, 3 RANK() OVER
(ORDER BY salary DESC) AS rank_num, 4 DENSE_RANK() OVER (ORDER BY salary DESC) AS
dense_rank 5 FROM employees;

■ EXPLANATION
ROW_NUMBER: unique always. RANK: ties share a rank but next rank is skipped. DENSE_RANK: ties
share a rank, next rank is consecutive.

Q2. Running Total of Sales (Monthly Reset)

Intermediate HackerRank Classic

PROBLEM
Calculate a running total of sales that resets each month. Return sale_date, daily_amount, and
running_total.

INPUT SCHEMA

Sales

sale_id INTEGER

sale_date DATE

amount DECIMAL

SQL & Data Engineering Interview Prep Page 16 of 30

 EXPECTED OUTPUT

1 2024-01-01 100 100 2 2024-01-03 200 300 3 2024-02-01 300 300

■ TIP: PARTITION BY YEAR+MONTH resets the window. ORDER BY date inside OVER makes SUM cumulative.

■ HINT
SUM(amount) OVER (PARTITION BY YEAR(sale_date), MONTH(sale_date) ORDER BY sale_date)

■ SOLUTION

1 SELECT 2 sale_date, 3 amount AS daily_amount, 4 SUM(amount) OVER ( 5 PARTITION BY

YEAR(sale_date), MONTH(sale_date) 6 ORDER BY sale_date 7 ) AS running_total 8 FROM Sales 9
ORDER BY sale_date;

■ EXPLANATION
PARTITION BY YEAR, MONTH resets the window each month. ORDER BY inside OVER makes SUM
accumulate chronologically.

Q3. Top 3 Products Per Category

Hard Advanced SQL Interview

PROBLEM
Find the top 3 selling products by revenue within each category. Include tied products at rank 3.

INPUT SCHEMA

sales

sale_id INTEGER

category VARCHAR

product VARCHAR

revenue DECIMAL

EXPECTED OUTPUT

1 Electronics Phone 12000 2 Electronics Laptop 9800 3 ...

■ TIP: Use RANK() not ROW_NUMBER() when ties should both appear.

■ HINT
Inner: SUM(revenue) + RANK() OVER (PARTITION BY category ORDER BY SUM(revenue) DESC). Outer:
WHERE rnk <= 3.

■ SOLUTION

1 SELECT category, product, total_revenue 2 FROM ( 3 SELECT category, product, 4

SUM(revenue) AS total_revenue, 5 RANK() OVER ( 6 PARTITION BY category 7 ORDER BY
SUM(revenue) DESC 8 ) AS rnk 9 FROM sales 10 GROUP BY category, product 11 ) ranked 12
WHERE rnk <= 3;

■ EXPLANATION
RANK() allows tied products at rank 3 to both appear. PARTITION BY category resets ranking per
category. Subquery aggregates first, then ranks.

SQL & Data Engineering Interview Prep Page 17 of 30

 Q4. Pivot Occupations Table
Hard HackerRank Classic

PROBLEM
Pivot OCCUPATIONS so each occupation becomes a column: Doctor, Professor, Singer, Actor. Names
alphabetical within each occupation. Print NULL when a column runs out.

INPUT SCHEMA

OCCUPATIONS

Name VARCHAR

Occupation VARCHAR

EXPECTED OUTPUT

1 Aamina Ashley Christeen Eve 2 Julia Britney Jane Jennifer 3 NULL Meera Jenny Ketty

■ TIP: MySQL has no native PIVOT. Use ROW_NUMBER() partitioned by Occupation, then MAX(CASE WHEN).

■ HINT
CTE: ROW_NUMBER PARTITION BY Occupation. Outer: GROUP BY rn, MAX(CASE WHEN Occupation=X THEN
Name END).

■ SOLUTION

1 WITH ranked AS ( 2 SELECT Name, Occupation, 3 ROW_NUMBER() OVER ( 4 PARTITION BY

Occupation ORDER BY Name 5 ) AS rn 6 FROM OCCUPATIONS 7 ) 8 SELECT 9 MAX(CASE WHEN
Occupation='Doctor' THEN Name END) AS Doctor, 10 MAX(CASE WHEN Occupation='Professor' THEN
Name END) AS Professor, 11 MAX(CASE WHEN Occupation='Singer' THEN Name END) AS Singer, 12
MAX(CASE WHEN Occupation='Actor' THEN Name END) AS Actor 13 FROM ranked 14 GROUP BY rn;

■ EXPLANATION
ROW_NUMBER assigns 1,2,3... alphabetically within each occupation. GROUP BY rn aligns all 1st names
into one row. MAX(CASE WHEN) extracts the name per column, NULL when no more names.

SQL & Data Engineering Interview Prep Page 18 of 30

 ■ Junior Data Engineer ★ NEW SECTION

Q1. Load Data Incrementally (Watermark Pattern) ★ NEW

Intermediate Junior Data Engineer

PROBLEM
You have a source orders table and a target data warehouse table. Write a query to load ONLY new or
updated records since the last load, using a watermark (last_loaded_at timestamp).

INPUT SCHEMA

orders_source

order_id INTEGER

customer_id INTEGER

amount DECIMAL

updated_at TIMESTAMP

load_metadata

table_name VARCHAR

last_loaded_at TIMESTAMP

EXPECTED OUTPUT

1 (Rows where updated_at > last_loaded_at)

■ TIP: This is the core of incremental ETL — only process changed rows using a high-watermark timestamp.

■ HINT
WHERE updated_at > (SELECT last_loaded_at FROM load_metadata WHERE table_name = orders)

■ SOLUTION

1 -- Step 1: Get the watermark 2 SELECT last_loaded_at 3 FROM load_metadata 4 WHERE

table_name = 'orders_source'; 5 6 -- Step 2: Load only new/updated rows 7 INSERT INTO
orders_warehouse 8 SELECT order_id, customer_id, amount, updated_at 9 FROM orders_source 10
WHERE updated_at > ( 11 SELECT last_loaded_at 12 FROM load_metadata 13 WHERE table_name =
'orders_source' 14 ); 15 16 -- Step 3: Update watermark 17 UPDATE load_metadata 18 SET
last_loaded_at = NOW() 19 WHERE table_name = 'orders_source';

■ EXPLANATION
Incremental load uses a watermark (last loaded timestamp) to identify only new/changed rows. This avoids
full table scans and is fundamental to ETL pipelines.

SQL & Data Engineering Interview Prep Page 19 of 30

 Q2. Deduplicate with ROW_NUMBER (Keep Latest) ★ NEW
Intermediate Junior Data Engineer

PROBLEM
Your pipeline loaded duplicate records into the staging table. Keep only the LATEST record per order_id
(based on updated_at). Delete or exclude older duplicates.

INPUT SCHEMA

orders_staging

id INTEGER (auto)

order_id INTEGER

amount DECIMAL

status VARCHAR

updated_at TIMESTAMP

EXPECTED OUTPUT

1 (One row per order_id — the most recent one)

■ TIP: ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) assigns 1 to the latest
row.

■ HINT
CTE with ROW_NUMBER partitioned by order_id ordered by updated_at DESC. WHERE rn = 1 keeps the latest.

■ SOLUTION

1 -- View deduplicated data 2 WITH deduped AS ( 3 SELECT *, 4 ROW_NUMBER() OVER ( 5

PARTITION BY order_id 6 ORDER BY updated_at DESC 7 ) AS rn 8 FROM orders_staging 9 ) 10
SELECT * FROM deduped WHERE rn = 1; 11 12 -- Delete duplicates (keep latest) 13 DELETE FROM
orders_staging 14 WHERE id NOT IN ( 15 SELECT MIN(id) 16 FROM orders_staging 17 GROUP BY
order_id 18 );

■ EXPLANATION
ROW_NUMBER partitioned by order_id assigns rank 1 to the most recent record. Filtering WHERE rn = 1
keeps only the latest. The DELETE version removes all but the first occurrence.

SQL & Data Engineering Interview Prep Page 20 of 30

 Q3. Data Quality — NULL Checks ★ NEW
Easy Junior Data Engineer

PROBLEM
As a data engineer you need to validate incoming data. Write queries to: (1) count NULLs per column, (2)
find rows where critical fields are NULL, (3) report the NULL percentage per column.

INPUT SCHEMA

customer_data

customer_id INTEGER

name VARCHAR

email VARCHAR

phone VARCHAR

created_at TIMESTAMP

EXPECTED OUTPUT

1 name_nulls: 5 email_nulls: 23 phone_nulls: 112

■ TIP: NULL checks are essential data quality validation steps in any ETL pipeline.

■ HINT
COUNT(*) - COUNT(column) counts NULLs. ROUND(100.0 * SUM(CASE WHEN col IS NULL...)/COUNT(*)) gives
percentage.

■ SOLUTION

1 -- 1. Count NULLs per column 2 SELECT 3 COUNT(*) - COUNT(name) AS name_nulls, 4 COUNT(*)

- COUNT(email) AS email_nulls, 5 COUNT(*) - COUNT(phone) AS phone_nulls, 6 COUNT(*) -
COUNT(created_at) AS created_nulls 7 FROM customer_data; 8 9 -- 2. Find rows with NULL in
critical fields 10 SELECT * 11 FROM customer_data 12 WHERE email IS NULL OR name IS NULL;
13 14 -- 3. NULL percentage per column 15 SELECT 16 ROUND(100.0 * SUM(CASE WHEN email IS
NULL THEN 1 ELSE 0 END) / COUNT(*), 2) 17 AS email_null_pct 18 FROM customer_data;

■ EXPLANATION
COUNT(column) ignores NULLs, so COUNT(*) - COUNT(col) = NULL count. CASE WHEN with SUM
gives percentage. These checks catch data quality issues before they reach downstream tables.

SQL & Data Engineering Interview Prep Page 21 of 30

 Q4. Slowly Changing Dimension (SCD Type 2) ★ NEW
Hard Junior Data Engineer

PROBLEM
Implement SCD Type 2 logic: when a customer updates their address, keep the old record with an
end_date and insert a new record with is_current = TRUE. Write the SQL to handle an address change.

INPUT SCHEMA

customer_dim

surrogate_key INTEGER (auto)

customer_id INTEGER

name VARCHAR

address VARCHAR

start_date DATE

end_date DATE (NULL=current)

is_current BOOLEAN

EXPECTED OUTPUT

1 (Old row updated with end_date. New row inserted with is_current=TRUE)

■ TIP: SCD Type 2 preserves history by closing old records and inserting new ones. Widely used in data
warehouses.

■ HINT
Step 1: UPDATE old row SET end_date = today, is_current = FALSE. Step 2: INSERT new row with start_date =
today.

■ SOLUTION

1 -- Step 1: Close the old record 2 UPDATE customer_dim 3 SET 4 end_date = CURRENT_DATE -
INTERVAL '1 day', 5 is_current = FALSE 6 WHERE customer_id = 101 7 AND is_current = TRUE; 8
9 -- Step 2: Insert the new current record 10 INSERT INTO customer_dim 11 (customer_id,
name, address, start_date, end_date, is_current) 12 VALUES 13 (101, 'Alice', '456 New St',
CURRENT_DATE, NULL, TRUE);

■ EXPLANATION
SCD Type 2 preserves full history. Old record is closed with end_date. New record inserted with
is_current=TRUE. Enables point-in-time reporting — critical for data warehouses.

SQL & Data Engineering Interview Prep Page 22 of 30

 Q5. Upsert / MERGE Pattern ★ NEW
Hard Junior Data Engineer

PROBLEM
Write an UPSERT: if a record with the same order_id exists in the target table, UPDATE it; otherwise
INSERT it. This is a common ETL pattern called MERGE or INSERT ON DUPLICATE KEY.

INPUT SCHEMA

orders_target

order_id INTEGER PRIMARY KEY

amount DECIMAL

status VARCHAR

updated_at TIMESTAMP

orders_staging

order_id INTEGER

amount DECIMAL

status VARCHAR

updated_at TIMESTAMP

EXPECTED OUTPUT

1 (Existing rows updated, new rows inserted)

■ TIP: MySQL uses INSERT ... ON DUPLICATE KEY UPDATE. PostgreSQL uses INSERT ... ON CONFLICT DO
UPDATE.

■ HINT
MySQL: INSERT INTO target SELECT * FROM staging ON DUPLICATE KEY UPDATE col=VALUES(col). Or use
MERGE in SQL Server.

■ SOLUTION

1 -- MySQL: INSERT ... ON DUPLICATE KEY 2 INSERT INTO orders_target (order_id, amount,
status, updated_at) 3 SELECT order_id, amount, status, updated_at 4 FROM orders_staging 5
ON DUPLICATE KEY UPDATE 6 amount = VALUES(amount), 7 status = VALUES(status), 8 updated_at
= VALUES(updated_at); 9 10 -- PostgreSQL: INSERT ... ON CONFLICT 11 INSERT INTO
orders_target (order_id, amount, status, updated_at) 12 SELECT order_id, amount, status,
updated_at 13 FROM orders_staging 14 ON CONFLICT (order_id) DO UPDATE SET 15 amount =
[Link], 16 status = [Link], 17 updated_at = EXCLUDED.updated_at;

■ EXPLANATION
UPSERT combines insert and update atomically. MySQL uses ON DUPLICATE KEY. PostgreSQL uses
ON CONFLICT. SQL Server uses MERGE. This pattern is fundamental to idempotent ETL pipelines.

SQL & Data Engineering Interview Prep Page 23 of 30

 Q6. Daily Active Users & Retention ★ NEW
Hard Junior Data Engineer

PROBLEM
Calculate Day-1 retention: for users who first appeared on a given date, what percentage came back the
NEXT day? Return cohort_date, total_users, retained_users, retention_pct.

INPUT SCHEMA

user_activity

user_id INTEGER

activity_date DATE

EXPECTED OUTPUT

1 2024-01-01 1000 423 42.30 2 2024-01-02 850 371 43.65

■ TIP: Find each user first_seen date (MIN). Then LEFT JOIN on user_id AND first_seen + 1 = activity_date.

■ HINT
CTE1: MIN(activity_date) per user = first_seen. CTE2: join back to check if user appeared on first_seen+1.

■ SOLUTION

1 WITH first_seen AS ( 2 SELECT user_id, 3 MIN(activity_date) AS cohort_date 4 FROM

user_activity 5 GROUP BY user_id 6 ), 7 retained AS ( 8 SELECT f.cohort_date, 9
COUNT(DISTINCT f.user_id) AS total_users, 10 COUNT(DISTINCT a.user_id) AS retained_users 11
FROM first_seen f 12 LEFT JOIN user_activity a 13 ON f.user_id = a.user_id 14 AND
a.activity_date = f.cohort_date + INTERVAL '1 day' 15 GROUP BY f.cohort_date 16 ) 17 SELECT
cohort_date, total_users, retained_users, 18 ROUND(100.0 * retained_users / total_users, 2)
AS retention_pct 19 FROM retained 20 ORDER BY cohort_date;

■ EXPLANATION
First CTE finds each user cohort date (first appearance). Second CTE left joins to check day-1 return.
NULL from left join = did not return. Retention % = retained / total.

SQL & Data Engineering Interview Prep Page 24 of 30

 Q7. Partition Large Table Query ★ NEW
Intermediate Junior Data Engineer

PROBLEM
You have a massive orders table partitioned by year. Write a query that efficiently uses partition pruning to
get 2024 orders over $500, and explain why you filter on the partition column.

INPUT SCHEMA

orders

order_id INTEGER

customer_id INTEGER

amount DECIMAL

order_date DATE

INTEGER (partition
year key)

EXPECTED OUTPUT

1 (Orders from 2024 partition only with amount > 500)

■ TIP: Always filter on the partition column directly to enable partition pruning — skips scanning other partitions
entirely.

■ HINT
WHERE year = 2024 AND amount > 500. If using DATE, use WHERE order_date BETWEEN 2024-01-01 AND
2024-12-31.

■ SOLUTION

1 -- GOOD: Filters on partition column — enables partition pruning 2 SELECT order_id,

customer_id, amount, order_date 3 FROM orders 4 WHERE year = 2024 -- prunes to 2024
partition only 5 AND amount > 500 6 ORDER BY order_date; 7 8 -- ALSO GOOD: Range on date
column 9 SELECT order_id, customer_id, amount, order_date 10 FROM orders 11 WHERE
order_date BETWEEN '2024-01-01' AND '2024-12-31' 12 AND amount > 500; 13 14 -- BAD: YEAR()
function prevents partition pruning 15 -- WHERE YEAR(order_date) = 2024 -- scans ALL
partitions!

■ EXPLANATION
Partition pruning skips entire partitions not matching the filter — huge performance gain. Applying
functions to the partition column (like YEAR(order_date)) disables pruning. Always filter directly on the
partition key.

SQL & Data Engineering Interview Prep Page 25 of 30

 Q8. Data Lineage — Track Record Source ★ NEW
Intermediate Junior Data Engineer

PROBLEM
You load data from multiple source systems. Add metadata columns to track where each row came from
and when it was loaded. Write the INSERT with audit columns.

INPUT SCHEMA

orders_raw

order_id INTEGER

amount DECIMAL

source_system VARCHAR

load_timestamp TIMESTAMP

batch_id VARCHAR

is_active BOOLEAN

EXPECTED OUTPUT

1 (Rows inserted with full lineage metadata)

■ TIP: Audit columns (source_system, load_timestamp, batch_id) are essential for debugging pipelines and tracing
data issues.

■ HINT
INSERT with hardcoded metadata: source_system = system_name, load_timestamp = NOW(), batch_id = unique
run ID.

■ SOLUTION

1 -- Insert with full lineage metadata 2 INSERT INTO orders_raw 3 (order_id, amount,
source_system, load_timestamp, batch_id, is_active) 4 SELECT 5 order_id, 6 amount, 7
'CRM_SYSTEM' AS source_system, 8 NOW() AS load_timestamp, 9 'BATCH_2024_01_15_001' AS
batch_id, 10 TRUE AS is_active 11 FROM orders_staging_crm; 12 13 -- Query lineage: which
batches loaded today? 14 SELECT batch_id, source_system, 15 MIN(load_timestamp) AS
batch_start, 16 MAX(load_timestamp) AS batch_end, 17 COUNT(*) AS rows_loaded 18 FROM
orders_raw 19 WHERE DATE(load_timestamp) = CURRENT_DATE 20 GROUP BY batch_id,
source_system;

■ EXPLANATION
Audit columns answer: where did this data come from? When? Which pipeline run? Essential for
debugging, reprocessing, and compliance. batch_id lets you roll back a specific load.

SQL & Data Engineering Interview Prep Page 26 of 30

 Q9. Find Gaps in Sequential IDs
Hard Advanced SQL Interview

PROBLEM
Find all missing IDs in the employees table. Assuming IDs should be consecutive integers, identify any
gaps in the sequence.

INPUT SCHEMA

employees

id INTEGER

name VARCHAR

EXPECTED OUTPUT

1 3 2 7 3 8

■ TIP: Self-join or NOT EXISTS — find IDs where (id + 1) does not exist in the table.

■ HINT
SELECT [Link] + 1 FROM employees curr WHERE NOT EXISTS (SELECT 1 FROM employees WHERE id =
[Link] + 1) AND [Link] < (SELECT MAX(id))

■ SOLUTION

1 SELECT [Link] + 1 AS missing_id 2 FROM employees curr 3 WHERE NOT EXISTS ( 4 SELECT 1
FROM employees 5 WHERE id = [Link] + 1 6 ) 7 AND [Link] < (SELECT MAX(id) FROM employees)
8 ORDER BY missing_id;

■ EXPLANATION
For each row, NOT EXISTS checks if the next sequential ID exists. The max ID check prevents false gaps
beyond the last real ID.

SQL & Data Engineering Interview Prep Page 27 of 30

 ■ String & Formatting

Q1. Shortest & Longest City Names

Easy HackerRank Classic

PROBLEM
Query the two cities in STATION with the shortest and longest CITY name lengths, including the lengths. If
tied, choose alphabetically first.

INPUT SCHEMA

STATION

ID INTEGER

CITY VARCHAR

STATE VARCHAR

LAT_N DECIMAL

LONG_W DECIMAL

EXPECTED OUTPUT

1 Abo 3 2 Marine City 11

■ TIP: UNION two queries — one sorted by LENGTH ASC, one by LENGTH DESC, each with LIMIT 1.

■ HINT
ORDER BY LENGTH(CITY) ASC, CITY ASC LIMIT 1. Change ASC to DESC for longest.

■ SOLUTION

1 (SELECT CITY, LENGTH(CITY) AS len 2 FROM STATION 3 ORDER BY LENGTH(CITY) ASC, CITY ASC 4
LIMIT 1) 5 UNION 6 (SELECT CITY, LENGTH(CITY) AS len 7 FROM STATION 8 ORDER BY LENGTH(CITY)
DESC, CITY ASC 9 LIMIT 1);

■ EXPLANATION
Two queries combined with UNION. Secondary sort by CITY handles ties alphabetically. Parentheses
allow ORDER BY inside UNION subqueries in MySQL.

SQL & Data Engineering Interview Prep Page 28 of 30

 Q2. Company Hierarchy Multi-Level Count
Intermediate HackerRank Classic

PROBLEM
Print company_code, founder, and total distinct counts of lead managers, senior managers, managers,
and employees. Order by company_code (string sort).

INPUT SCHEMA

Company

company_code VARCHAR

founder VARCHAR

Lead_Manager

lead_manager_code VARCHAR

company_code VARCHAR

Employee

employee_code VARCHAR

manager_code VARCHAR

company_code VARCHAR

EXPECTED OUTPUT

1 C_1 Monika 1 2 1 5 2 C_10 Jane 1 1 2 4

■ TIP: Tables may contain duplicates. Use COUNT(DISTINCT ...) to avoid overcounting. String ORDER BY sorts
C_1, C_10, C_2.

■ HINT
LEFT JOIN all tables to Company via company_code. COUNT(DISTINCT) each level code.

■ SOLUTION

1 SELECT c.company_code, [Link], 2 COUNT(DISTINCT lm.lead_manager_code), 3

COUNT(DISTINCT sm.senior_manager_code), 4 COUNT(DISTINCT m.manager_code), 5 COUNT(DISTINCT
e.employee_code) 6 FROM Company c 7 LEFT JOIN Lead_Manager lm ON c.company_code =
lm.company_code 8 LEFT JOIN Senior_Manager sm ON c.company_code = sm.company_code 9 LEFT
JOIN Manager m ON c.company_code = m.company_code 10 LEFT JOIN Employee e ON c.company_code
= e.company_code 11 GROUP BY c.company_code, [Link] 12 ORDER BY c.company_code ASC;

■ EXPLANATION
COUNT(DISTINCT) deduplicates potentially duplicate records. LEFT JOIN keeps companies with no staff.
String ORDER BY sorts lexicographically not numerically.

SQL & Data Engineering Interview Prep Page 29 of 30

 ■ Quick Reference Cheat Sheet
WHERE vs HAVING WHERE filters rows before grouping. HAVING filters groups after aggregation.

COUNT(*) vs COUNT(col) COUNT(*) counts all rows including NULLs. COUNT(col) ignores NULLs.

RANK vs DENSE_RANK RANK skips numbers after ties (1,1,3). DENSE_RANK does not (1,1,2).

ROW_NUMBER Always unique sequential number. Use for deduplication (WHERE rn = 1).

Self JOIN JOIN a table to itself using two aliases. Used for manager/employee
comparisons.

Anti-JOIN LEFT JOIN + WHERE right IS NULL finds rows with no match in the right table.

EXISTS vs IN EXISTS stops at first match — more efficient. IN checks all values.

Correlated Subquery References outer query. Runs once per row. Slower but flexible.

CTE (WITH) Named temporary result. More readable than nested subqueries. MySQL 8.0+.

Recursive CTE Traverses hierarchies. Anchor query + UNION ALL recursive member.

PARTITION BY Divides window function into groups. Resets running totals per group.

Incremental Load Load only rows WHERE updated_at > last_watermark. Core ETL pattern.

SCD Type 2 Track history: close old row (end_date), insert new row (is_current=TRUE).

UPSERT INSERT + UPDATE combined. MySQL: ON DUPLICATE KEY. PostgreSQL: ON

CONFLICT.

Partition Pruning Filter directly on partition column. Avoid functions on partition keys.

NULL Handling COUNT(*)-COUNT(col) = null count. COALESCE(col, default) replaces NULLs.

SQL & Data Engineering Interview Prep Page 30 of 30