SQL for data engineer

These are excellent SQL interview habits. Here's a polished version you can use as your SQL Problem-Solving Checklist during interviews.

SQL Interview Mindset Checklist

1. Don't select unnecessary columns

❌ Bad

SELECT *
FROM employees;

✅ Good

SELECT employee_id,
       employee_name,
       salary
FROM employees;

Why?

• Improves query performance.

• Reduces data scanned (especially in BigQuery, where you pay for data processed).

• Makes the query easier to read.

• Shows the interviewer you understand optimization.

Interview Tip:

"I avoid SELECT * unless I'm exploring the data. In production, I select only the required columns."

---

2. Ask clarifying questions whenever you're unsure

Don't make assumptions.

Examples:

• What does this column represent?

• Is this table already cleaned?

• Are duplicate records possible?

• Can one customer have multiple orders?

• Should NULL values be included?

• Should cancelled orders be considered?

• Do we need the latest record or all records?

• What defines an active customer?

• Which date column should I use (created_date, updated_date, order_date)?

• Should ties be included?

Example

Question:

Find the highest-paid employee.

Good follow-up:

"If multiple employees have the same highest salary, should I return all of them or just one?"

This shows analytical thinking rather than guessing.

---

3. Validate your output

Before saying you're done, ask yourself:

• Does the result make sense?

• Is the row count what I expected?

• Are there unexpected duplicates?

• Are NULL values affecting the results?

• Did my JOIN create duplicate rows?

• Did I accidentally filter out valid records?

• Is there another way to solve this?

Example

If your query returns 15,000 rows but you expected about 500, investigate:

• Is the JOIN condition correct?

• Did I use the correct filter?

• Should I use DISTINCT?

• Am I missing a GROUP BY?

---

4. Think about edge cases

Always consider:

• NULL values

• Duplicate rows

• Empty tables

• Multiple matches

• Missing data

• Division by zero

• Negative values

• Date boundaries

Interviewers often test whether you think beyond the "happy path."

---

5. Explain your thought process

Don't write SQL silently. Explain what you're doing.

Example:

"First, I'll identify the relevant tables. Then I'll join them on the customer ID, filter completed orders, group by customer, calculate total sales, and finally sort the results."

Interviewers value clear reasoning as much as the final query.

---

6. Optimize after getting a correct answer

Once your query works, think about improvements:

• Can I remove unnecessary columns?

• Can I filter earlier with WHERE?

• Can I avoid a subquery using a window function?

• Can I replace UNION with UNION ALL if duplicates are acceptable?

• Would a CTE improve readability?

• Am I scanning more data than necessary?

---

7. Consider multiple approaches

If you're stuck, ask yourself:

• Can I solve this with a JOIN?

• Can I use a subquery?

• Would a CTE make it simpler?

• Can a window function solve it more elegantly?

• Is there a BigQuery-specific feature like QUALIFY that simplifies the query?

Being able to discuss alternatives demonstrates a deeper understanding of SQL.

---

Data Engineer SQL Interview Golden Rules

1. ✅ Select only the columns you need.

2. ✅ Understand the data before writing SQL.

3. ✅ Ask clarifying questions instead of assuming.

4. ✅ Think through your solution before typing.

5. ✅ Validate your output—don't assume it's correct.

6. ✅ Consider edge cases (NULLs, duplicates, ties).

7. ✅ Explain your reasoning as you solve the problem.

8. ✅ Optimize the query after it produces the correct result.

9. ✅ Know more than one way to solve the problem.

10. ✅ Focus on correctness first, then performance.

Following these habits consistently will make you appear more like an experienced data engineer during interviews, because interviewers are evaluating your problem-solving process—not just whether the SQL query runs.

For a quick interview revision, you don't need long explanations. You need a 1-page cheat sheet for each topic. Here's a condensed guide covering Phases 1–14.

---

SQL for Data Engineer Interview Quick Revision

Phase 1: SQL Basics

SELECT

Retrieves data.

SELECT name, salary
FROM employees;

Interview: Avoid SELECT * in production.

---

DISTINCT

Removes duplicate values.

SELECT DISTINCT department
FROM employees;

---

LIMIT

Returns first N rows.

SELECT *
FROM employees
LIMIT 10;

---

WHERE

Filters rows before grouping.

SELECT *
FROM employees
WHERE salary > 5000;

---

ORDER BY

Sorts results.

SELECT *
FROM employees
ORDER BY salary DESC;

ASC = ascending (default)

DESC = descending

---

Alias (AS)

SELECT salary AS EmployeeSalary
FROM employees;

Makes column names readable.

---

Comments

-- Single line

/*
Multiple
Lines
*/

---

Phase 2: Filtering

IN

WHERE department IN ('IT','HR')

Same as multiple OR conditions.

---

NOT IN

WHERE department NOT IN ('HR')

---

BETWEEN

WHERE salary BETWEEN 5000 AND 10000

Inclusive.

---

LIKE

WHERE name LIKE 'A%'

A%   starts with A
%A   ends with A
%A%  contains A
_    single character

---

IS NULL

WHERE phone IS NULL

---

IS NOT NULL

WHERE phone IS NOT NULL

---

AND

WHERE salary>5000
AND department='IT'

---

OR

WHERE department='IT'
OR department='HR'

---

NOT

WHERE NOT salary>5000

---

Phase 3: Functions

Aggregate

COUNT

SELECT COUNT(*)
FROM employees;

SUM

SELECT SUM(salary)
FROM employees;

AVG

SELECT AVG(salary)
FROM employees;

MIN

SELECT MIN(salary)
FROM employees;

MAX

SELECT MAX(salary)
FROM employees;

---

String Functions

CONCAT(first,last)

SUBSTRING(name,1,3)

LENGTH(name)

UPPER(name)

LOWER(name)

TRIM(name)

REPLACE(name,'A','B')

---

Date Functions (BigQuery)

CURRENT_DATE()

CURRENT_TIMESTAMP()

DATE_ADD(date,INTERVAL 5 DAY)

DATE_SUB(date,INTERVAL 5 DAY)

DATE_DIFF(date1,date2,DAY)

EXTRACT(YEAR FROM date)

FORMAT_DATE('%Y-%m-%d',date)

---

Numeric

ROUND(12.345,2)

CEIL(2.2)

FLOOR(2.8)

ABS(-5)

---

Phase 4: GROUP BY

Groups records.

SELECT department,
AVG(salary)
FROM employees
GROUP BY department;

---

HAVING

Filters groups.

SELECT department,
COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*)>5;

Interview

WHERE → rows

HAVING → groups

---

Phase 5: CASE

Conditional logic.

CASE
WHEN salary>7000 THEN 'High'
WHEN salary>5000 THEN 'Medium'
ELSE 'Low'
END

---

Phase 6: JOINS

In SQL, JOIN is used to combine rows from two or more tables based on a related column. The main differences between JOIN types are which rows are returned when there is no matching data.

Assume we have these two tables:

Employees table

emp_id	emp_name	dept_id
1	John	10
2	Sarah	20
3	Mike	30
4	David	NULL

Departments table

dept_id	dept_name
10	HR
20	IT
40	Finance

---

1. INNER JOIN

Returns only matching rows from both tables.

Query:

SELECT 
    Employees.emp_name,
    Departments.dept_name
FROM Employees
INNER JOIN Departments
ON Employees.dept_id = Departments.dept_id;

Result:

emp_name	dept_name
John	HR
Sarah	IT

Explanation:

• John matches department 10 → HR ✅

• Sarah matches department 20 → IT ✅

• Mike has department 30 (no match) ❌

• David has NULL (no match) ❌

• Finance department 40 has no employee ❌

---

2. LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table + matching rows from the right table.

Query:

SELECT 
    Employees.emp_name,
    Departments.dept_name
FROM Employees
LEFT JOIN Departments
ON Employees.dept_id = Departments.dept_id;

Result:

emp_name	dept_name
John	HR
Sarah	IT
Mike	NULL
David	NULL

Explanation:
All employees are shown. If a department is missing, SQL returns NULL.

---

3. RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table + matching rows from the left table.

Query:

SELECT 
    Employees.emp_name,
    Departments.dept_name
FROM Employees
RIGHT JOIN Departments
ON Employees.dept_id = Departments.dept_id;

Result:

emp_name	dept_name
John	HR
Sarah	IT
NULL	Finance

Explanation:
All departments are shown. Finance has no employee, so employee name is NULL.

---

4. FULL OUTER JOIN

Returns all rows from both tables. Non-matching rows get NULL values.

Query:

SELECT 
    Employees.emp_name,
    Departments.dept_name
FROM Employees
FULL OUTER JOIN Departments
ON Employees.dept_id = Departments.dept_id;

Result:

emp_name	dept_name
John	HR
Sarah	IT
Mike	NULL
David	NULL
NULL	Finance

Explanation:
Shows:

• All employees ✅

• All departments ✅

• Matches where possible ✅

---

5. CROSS JOIN

Returns every possible combination of rows.

Query:

SELECT 
    Employees.emp_name,
    Departments.dept_name
FROM Employees
CROSS JOIN Departments;

If there are:

• 4 employees

• 3 departments

Result = 4 × 3 = 12 rows

Example:

emp_name	dept_name
John	HR
John	IT
John	Finance
Sarah	HR
Sarah	IT
...	...

---

Quick Comparison

JOIN Type	Returns
INNER JOIN	Only matching rows
LEFT JOIN	All left table rows + matches
RIGHT JOIN	All right table rows + matches
FULL OUTER JOIN	Everything from both tables
CROSS JOIN	All possible combinations

Easy way to remember:

• INNER → "Only common data"

• LEFT → "Keep everything on the left"

• RIGHT → "Keep everything on the right"

• FULL → "Keep everything"

• CROSS → "Multiply everything"

SELF JOIN in SQL

• A SELF JOIN is a JOIN where a table is joined with itself. It is useful when rows in the same table have a relationship with other rows in that same table.

  A self join uses table aliases because SQL needs to treat the same table as two different copies.

  ---

Example: Employee and Manager Relationship

• Imagine an Employees table:

  emp_id	emp_name	manager_id
  1	John	NULL
  2	Sarah	1
  3	Mike	1
  4	David	2

  Here:

  • John is the top manager (no manager)

  • Sarah reports to John

  • Mike reports to John

  • David reports to Sarah

  The manager_id column refers back to the same table's emp_id.

  ---

SELF JOIN Query

• SELECT 
      e.emp_name AS Employee,
      m.emp_name AS Manager
  FROM Employees e
  LEFT JOIN Employees m
  ON e.manager_id = m.emp_id;
  

Result:

• Employee	Manager
  John	NULL
  Sarah	John
  Mike	John
  David	Sarah

  ---

How it works

• The table is treated as two separate copies:

First copy: e (Employee)

• emp_id	emp_name	manager_id
  2	Sarah	1
  3	Mike	1
  4	David	2

Second copy: m (Manager)

• emp_id	emp_name
  1	John
  2	Sarah

  The condition:

  e.manager_id = m.emp_id
  

  means:

  • Sarah's manager_id = 1 → find employee with emp_id 1 → John

  • Mike's manager_id = 1 → find employee with emp_id 1 → John

  • David's manager_id = 2 → find employee with emp_id 2 → Sarah

  ---

SELF JOIN vs Other JOINs

• JOIN Type	Joins Between	Example
  INNER JOIN	Two different tables	Employees + Departments
  LEFT JOIN	Two different tables	Customers + Orders
  RIGHT JOIN	Two different tables	Orders + Customers
  FULL JOIN	Two different tables	Complete comparison
  SELF JOIN	Same table with itself	Employees + Managers

  ---

Another Example: Finding Employees in the Same Department

• Table:

  Employees

  emp_id	emp_name	dept_id
  1	John	10
  2	Sarah	10
  3	Mike	20
  4	David	10

  Query:

  SELECT 
      e1.emp_name AS Employee1,
      e2.emp_name AS Employee2,
      e1.dept_id
  FROM Employees e1
  JOIN Employees e2
  ON e1.dept_id = e2.dept_id
  AND e1.emp_id <> e2.emp_id;
  

  Result:

  Employee1	Employee2	dept_id
  John	Sarah	10
  John	David	10
  Sarah	John	10
  Sarah	David	10
  David	John	10
  David	Sarah	10

  Here the table compares employees with other employees in the same department.

  ---

When to use SELF JOIN

• Common uses:

  • Employee → Manager hierarchy

  • Finding duplicate records

  • Comparing rows within the same table

  • Finding related records in the same table

  • Category/subcategory relationships

  Simple definition:

  A SELF JOIN is a JOIN where a table is compared with itself by using aliases to create two logical copies of the same table.

---

Phase 7: Set Operators

UNION

Removes duplicates.

SELECT city FROM A

UNION

SELECT city FROM B;

---

UNION ALL

Keeps duplicates.

---

INTERSECT

Common rows.

---

EXCEPT

Rows in first query but not second.

---

Phase 8: SQL Subqueries

A subquery is a query written inside another SQL query. It is enclosed in parentheses and can be used in SELECT, FROM, WHERE, or HAVING clauses.

1. Scalar Subquery

A scalar subquery returns exactly one value (one row and one column).

Example: Find employees whose salary is greater than the average salary.

SELECT *
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Explanation:

• The inner query calculates the average salary.

• The outer query returns employees whose salary is greater than that average.

---

2. Correlated Subquery

A correlated subquery depends on the outer query and is executed once for every row processed by the outer query.

Example: Find employees who earn more than the average salary in their own department.

SELECT e1.*
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e1.department_id = e2.department_id
);

Explanation:

• The inner query calculates the average salary for the current employee's department.

• It runs once for each employee in the outer query.

---

3. EXISTS

The EXISTS operator returns TRUE if the subquery returns one or more rows.

Example: Find customers who have placed at least one order.

SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE c.customer_id = o.customer_id
);

Explanation:

• If at least one matching order exists for a customer, that customer is returned.

• SELECT 1 is commonly used because only the existence of rows matters.

---

4. NOT EXISTS

The NOT EXISTS operator returns TRUE if the subquery returns no rows.

Example: Find customers who have never placed an order.

SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE c.customer_id = o.customer_id
);

Explanation:

• Returns customers with no matching orders.

---

5. ANY

The ANY operator returns TRUE if the comparison is true for at least one value returned by the subquery.

Example: Find products that cost more than at least one product in category 1.

SELECT *
FROM products
WHERE price > ANY (
    SELECT price
    FROM products
    WHERE category_id = 1
);

Explanation:

• The condition is true if the product's price is greater than one or more prices returned by the subquery.

---

6. ALL

The ALL operator returns TRUE only if the comparison is true for every value returned by the subquery.

Example: Find products that cost more than every product in category 1.

SELECT *
FROM products
WHERE price > ALL (
    SELECT price
    FROM products
    WHERE category_id = 1
);

Explanation:

• The product's price must be greater than the highest price returned by the subquery.

---

Summary

Subquery Type	Description
Scalar	Returns a single value.
Correlated	Depends on the outer query and executes once per outer row.
EXISTS	Returns TRUE if the subquery returns at least one row.
NOT EXISTS	Returns TRUE if the subquery returns no rows.
ANY	Returns TRUE if the condition matches at least one value.
ALL	Returns TRUE only if the condition matches every value.

Phase 9: Common Table Expressions (CTE)

A Common Table Expression (CTE) is a temporary named result set that exists only for the duration of a single SQL statement. CTEs make complex queries easier to read, write, and maintain.

1. Basic CTE

A CTE is defined using the WITH keyword.

Example:

WITH Sales AS (
    SELECT *
    FROM Orders
)
SELECT *
FROM Sales;

Explanation:

• Sales is a temporary result set created from the Orders table.

• The main query then selects data from the CTE.

• The CTE exists only while this query is executed.

Why use a CTE?

• Improves query readability.

• Breaks complex queries into smaller, logical parts.

• Avoids repeating the same subquery multiple times.

• Makes debugging easier.

---

2. Recursive CTE

A Recursive CTE is a CTE that references itself. It is commonly used to work with hierarchical or tree-structured data, such as:

• Employee → Manager relationships

• Organization charts

• Categories and subcategories

• Folder/file structures

• Bill of Materials (BOM)

Example: Employee Hierarchy

Suppose the Employees table contains:

emp_id	emp_name	manager_id
1	John1	NULL
2	Sarah	1
3	Mike	1
4	David	2

The following recursive CTE displays the employee hierarchy:

WITH EmployeeHierarchy AS (
    -- Anchor member
    SELECT
        emp_id,
        emp_name,
        manager_id,
        1 AS level
    FROM Employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive member
    SELECT
        e.emp_id,
        e.emp_name,
        e.manager_id,
        eh.level + 1
    FROM Employees e
    JOIN EmployeeHierarchy eh
        ON e.manager_id = eh.emp_id
)
SELECT *
FROM EmployeeHierarchy;

Result

emp_id	emp_name	manager_id	level
1	John	NULL	1
2	Sarah	1	2
3	Mike	1	2
4	David	2	3

How it works

1. Anchor member

   • Selects the starting rows (employees with no manager).

2. Recursive member

   • Finds employees who report to the rows returned in the previous step.

   • Repeats until no more matching rows are found.

---

Summary

CTE Type	Description
Basic CTE	Creates a temporary named result set to simplify queries.
Recursive CTE	References itself to retrieve hierarchical or tree-structured data.

Key Points

• Defined using the WITH keyword.

• Exists only during the execution of a single SQL statement.

• Improves readability and maintainability.

• Recursive CTEs are ideal for hierarchical data and recursive relationships.

• omparison Table

  Feature	Subquery	CTE	Temporary Table
  Definition	Query inside another query	Temporary named result set	Temporary physical table
  Lifetime	Only within the query	One SQL statement	Until dropped or session ends
  Can be reused	No	Only within the same statement	Yes
  Readability	Moderate	Excellent	Good
  Supports recursion	No	Yes	No
  Can create indexes	No	No	Yes
  Stores data physically	No	No	Yes (temporary storage)
  Best for	Simple calculations	Complex readable queries	Large intermediate datasets

  ---

  When to Use Each

  Use a Subquery when:

  • You need a simple calculation.
  • The result is used only once.
  • The query is short.

  Example

  • Find employees earning more than the average salary.

  ---

  Use a CTE when:

  • The query is becoming difficult to read.
  • You want to break the logic into steps.
  • You need recursion (hierarchies or tree structures).

  Example

  • Employee hierarchy
  • Monthly sales calculations
  • Multiple logical transformations

  ---

  Use a Temporary Table when:

  • You need the intermediate data multiple times.
  • The dataset is large.
  • You want to add indexes.
  • Multiple queries need to access the same temporary data.

  Example

  • Large reporting queries
  • ETL (Extract, Transform, Load) processes
  • Complex stored procedures

  ---

  Interview Question

  Q: When would you choose a CTE instead of a Temporary Table?

  Answer:

  • Choose a CTE when you need a readable, one-time result set within a single query.
  • Choose a Temporary Table when the intermediate data must be reused multiple times, indexed, or processed across several SQL statements.

  ---

  Quick Summary

  Situation	Best Choice
  Simple one-time calculation	Subquery
  Improve readability	CTE
  Employee hierarchy (recursive data)	Recursive CTE
  Reuse intermediate results	Temporary Table
  Large datasets with indexing	Temporary Table
  Small nested logic	Subquery

Phase 10: SQL Window Functions

A Window Function performs calculations across a set of rows related to the current row without grouping the rows into a single result. Unlike GROUP BY, window functions return a value for every row.

All window functions require the OVER() clause.

This is one of the most confusing SQL concepts at first because OVER() looks similar to GROUP BY, but they behave differently.

Let's compare them using the same data.

Employees Table

emp_name	salary
John	50000
Sarah	70000
Mike	60000

Average salary = (50000 + 70000 + 60000) / 3 = 60000

---

Without OVER() (Using GROUP BY)

SELECT AVG(salary)
FROM Employees;

Result

AVG(salary)
60000

You get only one row because you're asking SQL to calculate a single average for the entire table.

---

With OVER()

SELECT
    emp_name,
    salary,
    AVG(salary) OVER() AS AvgSalary
FROM Employees;

Result

emp_name	salary	AvgSalary
John	50000	60000
Sarah	70000	60000
Mike	60000	60000

What SQL is doing

Think of SQL processing each row like this:

Row 1 (John)

Current row:
John | 50000

Window:
John
Sarah
Mike

Average = 60000

Output:
John | 50000 | 60000

---

Row 2 (Sarah)

Current row:
Sarah | 70000

Window:
John
Sarah
Mike

Average = 60000

Output:
Sarah | 70000 | 60000

---

Row 3 (Mike)

Current row:
Mike | 60000

Window:
John
Sarah
Mike

Average = 60000

Output:
Mike | 60000 | 60000

Notice that the window contains all rows because OVER() has no PARTITION BY or ORDER BY.

---

Think of OVER() as a Window

Imagine you're standing on each row while looking through a window.

Current Row
    ↓

John   ← looks through the window → John Sarah Mike
Sarah  ← looks through the window → John Sarah Mike
Mike   ← looks through the window → John Sarah Mike

Since the window always contains all employees, the average is always 60000.

---

Why not use GROUP BY?

If you write:

SELECT emp_name, AVG(salary)
FROM Employees;

You'll get an error because emp_name is not grouped or aggregated.

If you write:

SELECT AVG(salary)
FROM Employees;

You lose the employee names.

Window functions solve this problem by combining row-level data with aggregate values.

---

A Better Example: PARTITION BY

Suppose you have:

emp_name	department	salary
John	HR	50000
Sarah	HR	60000
Mike	IT	70000
David	IT	80000

Query:

SELECT
    emp_name,
    department,
    salary,
    AVG(salary) OVER(PARTITION BY department) AS DeptAvg
FROM Employees;

Result:

emp_name	department	salary	DeptAvg
John	HR	50000	55000
Sarah	HR	60000	55000
Mike	IT	70000	75000
David	IT	80000	75000

Now each employee looks only at their department's window:

HR Window
---------
John
Sarah
Average = 55000

IT Window
---------
Mike
David
Average = 75000

So:

• John sees 55000

• Sarah sees 55000

• Mike sees 75000

• David sees 75000

Easy way to remember

• GROUP BY → Groups rows together and returns one row per group.

• OVER() → Keeps every row but performs a calculation over a "window" of rows.

A simple rule is:

• GROUP BY = Collapse rows

• OVER() = Keep rows + Add calculated information

---

3. ROW_NUMBER()

Assigns a unique sequential number to every row.

Example

SELECT
    emp_name,
    salary,
    ROW_NUMBER() OVER(ORDER BY salary DESC) AS RowNum
FROM Employees;

Result

Employee	Salary	RowNum
David	80000	1
Mike	70000	2
Sarah	60000	3
John	50000	4

Key Point

• Every row gets a unique number.

• No duplicate row numbers.

---

4. RANK()

Assigns the same rank to ties, but skips the next rank.

Example

Employee	Salary	Rank
John	80000	1
Sarah	80000	1
Mike	70000	3
David	60000	4

Explanation

• Two employees tie for rank 1.

• Rank 2 is skipped.

---

5. DENSE_RANK()

Assigns the same rank to ties without skipping ranks.

Example

Employee	Salary	DenseRank
John	80000	1
Sarah	80000	1
Mike	70000	2
David	60000	3

Explanation

• Two employees tie for rank 1.

• The next rank is 2 (not 3).

---

Difference: ROW_NUMBER vs RANK vs DENSE_RANK

Suppose the salaries are:

Employee	Salary
John	90000
Sarah	90000
Mike	80000
David	70000

Employee	ROW_NUMBER	RANK	DENSE_RANK
John	1	1	1
Sarah	2	1	1
Mike	3	3	2
David	4	4	3

Summary

Function	Duplicate Values	Skips Rank
ROW_NUMBER	No	No
RANK	Yes	Yes
DENSE_RANK	Yes	No

---

6. NTILE()

Divides rows into a specified number of groups (buckets).

Example

SELECT
    emp_name,
    salary,
    NTILE(4) OVER(ORDER BY salary DESC) AS Quartile
FROM Employees;

Result

Employee	Salary	Quartile
John	90000	1
Sarah	80000	2
Mike	70000	3
David	60000	4

Use Cases

• Quartiles

• Percentiles

• Customer segmentation

---

7. LAG()

Returns the value from the previous row.

Example

SELECT
    emp_name,
    salary,
    LAG(salary) OVER(ORDER BY salary) AS PreviousSalary
FROM Employees;

Result

Employee	Salary	PreviousSalary
John	50000	NULL
Sarah	60000	50000
Mike	70000	60000

---

8. LEAD()

Returns the value from the next row.

Example

SELECT
    emp_name,
    salary,
    LEAD(salary) OVER(ORDER BY salary) AS NextSalary
FROM Employees;

Result

Employee	Salary	NextSalary
John	50000	60000
Sarah	60000	70000
Mike	70000	NULL

---

9. FIRST_VALUE()

Returns the first value in the window (partition).

Example

SELECT
    emp_name,
    salary,
    FIRST_VALUE(salary)
    OVER(ORDER BY salary DESC) AS HighestSalary
FROM Employees;

Result

Employee	Salary	HighestSalary
John	90000	90000
Sarah	80000	90000
Mike	70000	90000

---

10. LAST_VALUE()

Returns the last value in the window.

Example

SELECT
    emp_name,
    salary,
    LAST_VALUE(salary)
    OVER(
        ORDER BY salary
        ROWS BETWEEN UNBOUNDED PRECEDING
        AND UNBOUNDED FOLLOWING
    ) AS LowestSalary
FROM Employees;

Note: LAST_VALUE() often requires a window frame (ROWS BETWEEN ...) to return the true last value of the partition.

---

11. Running Total

Calculates a cumulative total.

Example

SELECT
    emp_name,
    salary,
    SUM(salary)
    OVER(ORDER BY emp_id) AS RunningTotal
FROM Employees;

Result

Employee	Salary	RunningTotal
John	50000	50000
Sarah	60000	110000
Mike	70000	180000

---

12. Moving Average

Calculates the average over a sliding window of rows.

Example

Average of the current row and the previous two rows.

SELECT
    emp_name,
    salary,
    AVG(salary)
    OVER(
        ORDER BY emp_id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS MovingAverage
FROM Employees;

---

Summary Table

Function	Purpose
OVER()	Defines the window for calculation
PARTITION BY	Divides rows into groups without collapsing them
ROW_NUMBER()	Assigns a unique sequential number
RANK()	Same rank for ties; skips the next rank
DENSE_RANK()	Same rank for ties; no skipped ranks
NTILE(n)	Splits rows into n equal buckets
LAG()	Returns the previous row's value
LEAD()	Returns the next row's value
FIRST_VALUE()	Returns the first value in the window
LAST_VALUE()	Returns the last value in the window
SUM() OVER()	Calculates a running (cumulative) total
AVG() OVER()	Calculates moving or partitioned averages

Interview Tip

• Use ROW_NUMBER() to remove duplicates or select the top N rows.

• Use RANK() or DENSE_RANK() when handling ties.

• Use LAG() and LEAD() to compare values with previous or next rows.

• Use window functions instead of GROUP BY when you need aggregated values while preserving individual rows.

Phase 11: NULL Handling Across Data Engineering Technologies

• Handling NULL (missing) values is one of the most common tasks in data engineering. Every platform provides functions to replace, compare, or create NULL values.

  ---

1. COALESCE

• Purpose: Returns the first non-NULL value from a list of expressions.

SQL (ANSI SQL, SQL Server, PostgreSQL, Snowflake, Databricks SQL)

• SELECT COALESCE(phone, 'NA') AS phone
  FROM customers;
  

Example

• phone
  NULL
  9876543210
  NULL

  Result

  phone
  NA
  9876543210
  NA

Multiple Columns

• SELECT COALESCE(home_phone, mobile_phone, office_phone, 'NA')
  FROM customers;
  

  SQL checks values from left to right and returns the first non-NULL value.

  ---

2. IFNULL

• Purpose: Replaces NULL with another value.

  Supported in:

  • BigQuery

  • MySQL

  • SQLite

BigQuery

• SELECT IFNULL(phone, 'NA') AS phone
  FROM customers;
  

  Equivalent using COALESCE:

  SELECT COALESCE(phone, 'NA')
  FROM customers;
  

  ---

3. NULLIF

• Purpose: Returns NULL if the two expressions are equal; otherwise returns the first expression.

SQL Example

• SELECT NULLIF(100, 100);
  

  Result

  NULL
  

  SELECT NULLIF(100, 50);
  

  Result

  100
  

Practical Example: Prevent Division by Zero

• SELECT
      salary / NULLIF(hours_worked, 0) AS hourly_rate
  FROM employees;
  

  If hours_worked = 0, NULLIF returns NULL instead of 0, preventing a divide-by-zero error.

  ---

NULL Handling in Different Data Engineering Technologies

1. Databricks SQL

• Databricks SQL supports standard ANSI SQL functions.

COALESCE

• SELECT COALESCE(phone, 'NA')
  FROM customers;
  

NULLIF

• SELECT NULLIF(a, b);
  

IFNULL

• SELECT IFNULL(phone, 'NA');
  

  ---

2. Snowflake

• Snowflake supports all common NULL-handling functions.

COALESCE

• SELECT COALESCE(phone, 'NA');
  

NULLIF

• SELECT NULLIF(a, b);
  

IFNULL

• SELECT IFNULL(phone, 'NA');
  

  Snowflake also supports:

  NVL(phone, 'NA')
  

  NVL() is an Oracle-compatible alternative to COALESCE().

  ---

3. BigQuery

COALESCE

• SELECT COALESCE(phone, 'NA');
  

IFNULL

• SELECT IFNULL(phone, 'NA');
  

NULLIF

• SELECT NULLIF(a, b);
  

  BigQuery supports all three functions.

  ---

4. PySpark (DataFrame API)

Replace NULL values

• from pyspark.sql.functions import coalesce, col, lit
  
  df.select(
      coalesce(col("phone"), lit("NA")).alias("phone")
  )
  

Using when

• from pyspark.sql.functions import when
  
  df.withColumn(
      "phone",
      when(col("phone").isNull(), "NA")
      .otherwise(col("phone"))
  )
  

NULLIF Equivalent

• PySpark does not provide a built-in NULLIF() function in the DataFrame API.

  Equivalent:

  from pyspark.sql.functions import when
  
  df.withColumn(
      "value",
      when(col("a") == col("b"), None)
      .otherwise(col("a"))
  )
  

In Spark SQL

• SELECT NULLIF(a, b)
  FROM table_name;
  

  ---

5. Pandas

• Pandas represents missing values using NaN or None.

Fill NULL values

• df["phone"] = df["phone"].fillna("NA")
  

Using combine_first() (similar to COALESCE)

• df["phone"] = df["home_phone"].combine_first(df["mobile_phone"])
  

NULLIF Equivalent

• import numpy as np
  
  df["value"] = np.where(
      df["a"] == df["b"],
      np.nan,
      df["a"]
  )
  

  ---

6. Python

• Python uses None to represent null values.

COALESCE Equivalent

• phone = phone or "NA"
  

  or

  phone = phone if phone is not None else "NA"
  

NULLIF Equivalent

• result = None if a == b else a
  

  ---

Comparison Table

• Technology	COALESCE	IFNULL	NULLIF
  SQL Server	✅	❌	✅
  PostgreSQL	✅	❌	✅
  MySQL	✅	✅	✅
  Databricks SQL	✅	✅	✅
  Snowflake	✅	✅	✅
  BigQuery	✅	✅	✅
  PySpark DataFrame	coalesce()	❌	when() equivalent
  Spark SQL	✅	❌	✅
  Pandas	combine_first()	fillna() equivalent	np.where() equivalent
  Python	or / conditional expression	Conditional expression	Conditional expression

  ---

When to Use

• Situation	Function
  Replace NULL with a default value	COALESCE() or IFNULL()
  Return the first available non-NULL value	COALESCE()
  Convert equal values to NULL	NULLIF()
  Prevent divide-by-zero errors	NULLIF()
  Fill missing values in Pandas	fillna()
  Fill missing values in PySpark	coalesce() or when()

  ---

Interview Tip

• • COALESCE() can take multiple arguments and returns the first non-NULL value.

  • IFNULL() accepts only two arguments and is available in systems such as BigQuery, MySQL, Snowflake, and Databricks SQL.

  • NULLIF(a, b) returns NULL when a = b; otherwise, it returns a.

  • In PySpark and Pandas, NULLIF is typically implemented using conditional expressions (when in PySpark or np.where in Pandas).

Phase 12: BigQuery SQL

ARRAY

['A','B','C']

---

UNNEST

Convert array into rows.

SELECT *
FROM UNNEST([1,2,3]);

---

STRUCT

Nested record.

---

ARRAY_AGG

Create arrays.

ARRAY_AGG(name)

---

SAFE_CAST

Returns NULL instead of error.

SAFE_CAST(age AS INT64)

---

SAFE_DIVIDE

Avoid divide-by-zero errors.

SAFE_DIVIDE(a,b)

---

QUALIFY

Filter window function results.

QUALIFY ROW_NUMBER()
OVER(PARTITION BY dept ORDER BY salary DESC)=1

---

Partitioned Tables

• Improve performance by scanning only relevant partitions.

• Commonly partition by DATE.

---

Clustered Tables

• Physically organize data by columns (for example, customer_id).

• Improve filtering and join performance.

---

Phase 13: Data Modification

INSERT

INSERT INTO employees
VALUES(1,'John',5000);

---

UPDATE

UPDATE employees
SET salary=6000
WHERE id=1;

---

DELETE

DELETE
FROM employees
WHERE id=1;

---

MERGE (UPSERT)

MERGE target t
USING source s
ON t.id=s.id
WHEN MATCHED THEN
UPDATE SET salary=s.salary
WHEN NOT MATCHED THEN
INSERT(id,salary)
VALUES(s.id,s.salary);

Used to synchronize two tables.

---

Phase 14: Views

View

Virtual table.

CREATE VIEW high_salary AS
SELECT *
FROM employees
WHERE salary>7000;

---

Temporary View

Exists only for the current session.

---

Materialized View

Stores query results physically.

• Faster reads.

• Automatically refreshed in BigQuery (subject to supported query patterns).

---

⭐ Top 15 SQL Interview Questions

1. Difference between WHERE and HAVING?

2. UNION vs UNION ALL?

3. ROW_NUMBER() vs RANK() vs DENSE_RANK()?

4. Explain all JOIN types.

5. What is a CTE?

6. What is a window function?

7. COUNT(*) vs COUNT(column)?

8. EXISTS vs IN?

9. DELETE vs TRUNCATE vs DROP? (Note: TRUNCATE support varies by database.)

10. What is MERGE?

11. What is QUALIFY in BigQuery?

12. Why use SAFE_CAST()?

13. What is a partitioned table?

14. What is a clustered table?

15. What is the logical execution order of a SQL query?

SQL Logical Execution Order

FROM
JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT

This cheat sheet covers the core SQL concepts that appear most frequently in data engineering interviews, especially for BigQuery, Snowflake, Redshift, PostgreSQL, and SQL Server.

⭐ Top 15 SQL Interview Questions with Answers (Data Engineer Quick Revision)

---

1. Difference between WHERE and HAVING?

Answer

WHERE	HAVING
Filters rows	Filters groups
Executed before GROUP BY	Executed after GROUP BY
Cannot use aggregate functions	Can use aggregate functions

Example

SELECT department, AVG(salary)
FROM employees
WHERE salary > 3000
GROUP BY department
HAVING AVG(salary) > 5000;

Interview Tip

• WHERE → Filters individual records.

• HAVING → Filters aggregated results.

---

2. UNION vs UNION ALL?

Answer

UNION	UNION ALL
Removes duplicates	Keeps duplicates
Slower	Faster
Performs duplicate check	No duplicate check

Example

Table A

A
B
C

Table B

B
C
D

UNION

A
B
C
D

UNION ALL

A
B
C
B
C
D

Interview Tip

Use UNION ALL unless duplicate removal is required.

---

3. ROW_NUMBER() vs RANK() vs DENSE_RANK()

Answer

Suppose salaries are:

9000
9000
8000
7000

Salary	ROW_NUMBER	RANK	DENSE_RANK
9000	1	1	1
9000	2	1	1
8000	3	3	2
7000	4	4	3

Difference

ROW_NUMBER()

• Always unique.

• No duplicate rankings.

RANK()

• Same rank for ties.

• Skips rank numbers.

DENSE_RANK()

• Same rank for ties.

• No skipped numbers.

Interview Tip

Most common interview question:

Find the second highest salary.

Use DENSE_RANK().

---

4. Explain all JOIN types

INNER JOIN

Returns matching rows only.

A: 1 2 3
B: 2 3 4

Result:
2
3

---

LEFT JOIN

Returns all rows from left table.

1
2
3

Matching values from right table are added.

---

RIGHT JOIN

Returns all rows from right table.

---

FULL OUTER JOIN

Returns everything.

1
2
3
4

---

CROSS JOIN

Every row joins every row.

3 × 4 = 12 rows

---

SELF JOIN

Table joins itself.

Used for:

• Employee → Manager

• Parent → Child

• Product hierarchy

---

5. What is a CTE?

Answer

CTE = Common Table Expression

Temporary named result set.

WITH HighSalary AS
(
SELECT *
FROM Employees
WHERE Salary>5000
)

SELECT *
FROM HighSalary;

Advantages

• Easier to read

• Easier to debug

• Reusable in the same query

• Great for complex SQL

---

6. What is a Window Function?

Answer

A window function performs calculations across a set of rows without reducing the number of rows returned.

Unlike GROUP BY, it preserves each row.

Example:

SELECT
EmployeeName,
Salary,
AVG(Salary)
OVER(PARTITION BY Department)
FROM Employees;

Every employee remains visible while showing the department average.

Common window functions:

• ROW_NUMBER()

• RANK()

• DENSE_RANK()

• LAG()

• LEAD()

• SUM() OVER()

• AVG() OVER()

---

7. COUNT(*) vs COUNT(column)

COUNT(*)

Counts every row.

NULL included

Example

Name
John
NULL
Mary

COUNT(*)

Returns

3

---

COUNT(column)

Counts only non-null values.

COUNT(Name)

Returns

2

Interview Tip

COUNT(*) is generally preferred when counting rows because it is clear and works regardless of NULL values.

---

8. EXISTS vs IN

EXISTS

Checks whether matching rows exist.

Stops searching after finding the first match.

SELECT *
FROM Customers c
WHERE EXISTS
(
SELECT 1
FROM Orders o
WHERE c.CustomerID=o.CustomerID
);

---

IN

Compares against a list of values.

WHERE CustomerID IN
(
SELECT CustomerID
FROM Orders
)

Difference

EXISTS	IN
Checks existence	Compares values
Good for correlated subqueries	Good for small result sets
Often more efficient for large datasets	Can be slower on large datasets depending on the optimizer

---

9. DELETE vs TRUNCATE vs DROP

DELETE	TRUNCATE	DROP
Removes selected rows	Removes all rows	Removes table completely
WHERE allowed	WHERE not allowed	Table disappears
Table remains	Table remains	Table removed

Example

DELETE

DELETE
FROM Employees
WHERE ID=10;

TRUNCATE

TRUNCATE TABLE Employees;

DROP

DROP TABLE Employees;

---

10. What is MERGE?

Answer

MERGE combines:

• INSERT

• UPDATE

• DELETE (optional)

in one statement.

Example

MERGE target t
USING source s
ON t.id=s.id

WHEN MATCHED THEN
UPDATE SET salary=s.salary

WHEN NOT MATCHED THEN
INSERT(id,salary)
VALUES(s.id,s.salary);

Very common in ETL pipelines.

---

11. What is QUALIFY in BigQuery?

Answer

QUALIFY filters the result of window functions.

Instead of writing

SELECT *
FROM
(
SELECT *,
ROW_NUMBER()
OVER(PARTITION BY department ORDER BY salary DESC) rn
FROM employees
)
WHERE rn=1;

BigQuery allows

SELECT *
FROM employees
QUALIFY ROW_NUMBER()
OVER(PARTITION BY department ORDER BY salary DESC)=1;

Much simpler.

---

12. Why use SAFE_CAST()?

Answer

Normal CAST throws an error if conversion fails.

CAST('ABC' AS INT64)

Error

Invalid integer

SAFE_CAST

SAFE_CAST('ABC' AS INT64)

Returns

NULL

Useful when cleaning messy data.

---

13. What is a Partitioned Table?

Answer

A partitioned table divides data into smaller pieces based on a column (often a date).

Example

Sales

2023-01
2023-02
2023-03

Instead of scanning the whole table,

WHERE order_date='2024-01-01'

BigQuery scans only the matching partition.

Benefits

• Faster queries

• Lower query cost

• Less data scanned

---

14. What is a Clustered Table?

Answer

A clustered table stores rows ordered by one or more columns (for example, customer_id or department).

Example

Cluster by

CustomerID

When querying

WHERE CustomerID=101

BigQuery can read much less data.

Benefits

• Faster filtering

• Faster joins

• Lower query cost

Difference

Partitioning splits data into partitions (commonly by date), while clustering organizes data within those partitions (or within the table if not partitioned) by the clustered columns.

---

15. What is the Logical Execution Order of a SQL Query?

Although we write SQL like this:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

The database logically executes it in this order:

1. FROM
2. JOIN
3. WHERE
4. GROUP BY
5. HAVING
6. SELECT
7. DISTINCT
8. ORDER BY
9. LIMIT

Why is this important?

Suppose you write:

SELECT salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 50000;

This fails because WHERE runs before SELECT, so the alias annual_salary doesn't exist yet.

Correct approach:

SELECT salary * 12 AS annual_salary
FROM employees
WHERE salary * 12 > 50000;

or use a CTE/subquery.

---

⭐ Final Data Engineer Interview Tips

• Know JOINs thoroughly—they're asked in almost every SQL interview.

• Master Window Functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD)—these are among the most common advanced SQL topics.

• Be comfortable explaining WHERE vs HAVING, UNION vs UNION ALL, and COUNT(*) vs COUNT(column).

• Understand partitioning and clustering in BigQuery because they directly impact performance and cost.

• Be ready to explain MERGE, QUALIFY, and SAFE_CAST, as they're frequently used in BigQuery-based ETL pipelines.

• Always think about performance: filter early, avoid unnecessary SELECT *, and use appropriate joins and partition pruning where possible.