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

INNER JOIN

Common records only.

SELECT *
FROM A
INNER JOIN B
ON A.id=B.id;

---

LEFT JOIN

All rows from left table.

---

RIGHT JOIN

All rows from right table.

---

FULL JOIN

Everything from both tables.

---

CROSS JOIN

Cartesian product.

3 rows × 4 rows =12 rows

---

SELF JOIN

Join table with itself.

Used for manager-employee relationships.

---

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: Subqueries

Scalar

Returns one value.

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

---

Correlated

Runs once for every outer row.

---

EXISTS

Checks if rows exist.

WHERE EXISTS
(
SELECT 1
FROM orders
WHERE customer.id=orders.customer_id
)

---

NOT EXISTS

Opposite of EXISTS.

---

ANY

True if condition matches at least one value.

---

ALL

True if condition matches every value.

---

Phase 9: CTE

Makes queries readable.

WITH Sales AS
(
SELECT *
FROM Orders
)

SELECT *
FROM Sales;

---

Recursive CTE

Used for hierarchy/tree structures.

---

Phase 10: Window Functions

OVER()

Required for every window function.

---

PARTITION BY

Creates groups without collapsing rows.

---

ROW_NUMBER()

Unique numbering.

1
2
3

---

RANK()

1
1
3

---

DENSE_RANK()

1
1
2

---

NTILE(4)

Splits rows into 4 buckets.

---

LAG()

Previous row.

---

LEAD()

Next row.

---

FIRST_VALUE()

First value in partition.

---

LAST_VALUE()

Last value in partition.

---

Running Total

SUM(salary)
OVER(ORDER BY id)

Moving Average

AVG(salary)
OVER(...)

---

Phase 11: NULL Handling

COALESCE

First non-null value.

COALESCE(phone,'NA')

---

IFNULL (BigQuery)

IFNULL(phone,'NA')

---

NULLIF

Returns NULL if equal.

NULLIF(a,b)

---

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.