# 25 SQL Tricks to Crack Any Data Engineering Interview
> **SEO Title:** 25 SQL Tricks to Crack / Pass Any Data Engineering
> Interview (PostgreSQL + Real Interview Patterns)
**Meta Description:** Master the 25 most important SQL tricks every Data
Engineer should know. Learn PostgreSQL commands, window functions,
ranking, CTEs, joins, aggregations, SCD Type 2, optimization tips, and
interview-focused explanations.
————————————————————————
# Introduction
SQL is one of the most heavily tested skills in Data Engineering
interviews. Whether you’re interviewing for Azure Data Engineer,
Databricks Engineer, AWS Data Engineer, GCP Data Engineer, or a
traditional ETL role, interviewers rarely ask syntax alone—they
evaluate your ability to recognize patterns.
This Markdown starter includes copy-paste-ready PostgreSQL examples.
> **Note:** A true **4,000–6,000 word** article containing all 25
> tricks with deep explanations, diagrams, interview discussions,
> optimization notes, FAQs, and examples is larger than what can be
> produced in a single response. This file provides the complete
> structure and the first fully documented sections so you can extend it
> or ask me to generate the remaining sections in follow-up parts.
————————————————————————
# Table of Contents
1. Finding Duplicates
2. Removing Duplicates
3. Second Highest Salary
4. Top N Records
5. Window Functions
6. Running Totals
7. LAG / LEAD
8. Latest Record
9. Conditional Aggregation
10. Pivot
11. SCD Type 2
12. Performance Tips
13. Interview Questions
————————————————————————
# Sample Schema
“` sql
CREATE TABLE employees(
employee_id INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50),
salary NUMERIC(10,2),
manager_id INT,
joining_date DATE
);
“`
————————————————————————
# 1. Find Duplicate Records
## Problem
Find duplicate email addresses.
“` sql
SELECT email,
COUNT(*) AS duplicate_count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;
“`
### Why it works
– GROUP BY groups identical values.
– COUNT(\*) counts occurrences.
– HAVING filters aggregated results.
### Interview Tip
This question frequently appears as a warm-up before more advanced SQL.
————————————————————————
# 2. Delete Duplicate Records
“` sql
WITH ranked AS
(
SELECT *,
ROW_NUMBER() OVER(
PARTITION BY email
ORDER BY customer_id
) rn
FROM customers
)
DELETE FROM ranked
WHERE rn>1;
“`
### Pattern
– ROW_NUMBER()
– CTE
– DELETE
————————————————————————
# 3. Second Highest Salary
“` sql
SELECT MAX(salary)
FROM employees
WHERE salary <
(
SELECT MAX(salary)
FROM employees
);
“`
Alternative:
“` sql
SELECT salary
FROM
(
SELECT DISTINCT salary,
DENSE_RANK() OVER(
ORDER BY salary DESC
) rnk
FROM employees
)t
WHERE rnk=2;
“`
————————————————————————
# Window Functions Cheat Sheet
Function Purpose
————- ———————-
ROW_NUMBER Unique numbering
RANK Ranking with gaps
DENSE_RANK Ranking without gaps
LAG Previous row
LEAD Next row
FIRST_VALUE First value
LAST_VALUE Last value
————————————————————————
# Performance Tips
Use indexes on:
“` sql
CREATE INDEX idx_orders_customer
ON orders(customer_id);
“`
Check execution plan:
“` sql
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id=100;
“`
————————————————————————
# Common Interview Questions
1. Difference between RANK and DENSE_RANK?
2. ROW_NUMBER vs DISTINCT?
3. HAVING vs WHERE?
4. EXISTS vs IN?
5. UNION vs UNION ALL?
————————————————————————
# Conclusion
Mastering SQL patterns is far more valuable than memorizing syntax.
Practice every query on PostgreSQL, explain the logic aloud, and
optimize using EXPLAIN ANALYZE.