Top 50 SQL Interview Questions – Master SQL with Practical Examples

Welcome to the ultimate SQL interview preparation guide! Whether you are a beginner looking to solidify your basics or an experienced professional aiming to crack that senior role, these 50 real‑world SQL queries cover everything you need. Each question includes sample data, expected output, a clean solution, and a step‑by‑step logic breakdown. Let’s dive in! 💪

📑 Table of Contents

• 🔵 Basic Level (Questions 1–15)
• 🟡 Intermediate Level (Questions 16–35)
• 🔴 Advanced Level (Questions 36–50)

🔵 BASIC LEVEL (1–15)

1. Second Highest Salary Basic

📊 Sample Data (employee):

employee_id	name	salary	dept_id
1	John	50000	1
2	Sarah	60000	1
3	Mike	70000	2
4	Emma	65000	2
5	David	70000	1
6	Lisa	55000	3

✅ Expected Output:

salary
65000

💻 Solution Query:

SELECT MAX(salary) AS second_highest_salary
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

🧠 Logic Explanation:

• Subquery finds the absolute maximum salary.
• Outer query finds the highest salary that is less than that maximum.
• Use DISTINCT if duplicate salaries might affect the result.
• Alternative: LIMIT 1 OFFSET 1 or DENSE_RANK().

2. Find Duplicate Records Basic

📊 Sample Data (customers):

id	name	email	phone
1	John	j@x.com	123-456
2	Sarah	s@x.com	234-567
3	John	j@x.com	123-456
4	Mike	m@x.com	345-678
5	John	j@y.com	123-456

✅ Expected Output:

id | name | email   | phone
1  | John | j@x.com | 123-456
3  | John | j@x.com | 123-456

💻 Solution Query:

SELECT id, name, email, phone
FROM customers
WHERE (name, email, phone) IN (
    SELECT name, email, phone
    FROM customers
    GROUP BY name, email, phone
    HAVING COUNT(*) > 1
);

🧠 Logic Explanation:

• Group by the columns that define a duplicate.
• HAVING COUNT(*) > 1 identifies groups with more than one record.
• The outer query retrieves all rows from those groups.

3. Delete Duplicate Records Basic

📊 Sample Data (before):

id | name  | email   | phone
1  | John  | j@x.com | 123
2  | Sarah | s@x.com | 234
3  | John  | j@x.com | 123
4  | Mike  | m@x.com | 345
5  | John  | j@x.com | 123

✅ Expected Output (keep smallest id):

id | name  | email   | phone
1  | John  | j@x.com | 123
2  | Sarah | s@x.com | 234
4  | Mike  | m@x.com | 345

💻 Solution Query:

WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY name, email, phone
               ORDER BY id
           ) AS rn
    FROM customers
)
DELETE FROM customers
WHERE (id, name, email, phone) IN (
    SELECT id, name, email, phone FROM cte WHERE rn > 1
);

🧠 Logic Explanation:

• ROW_NUMBER() assigns 1 to the first occurrence per group.
• Delete rows where rn > 1, keeping the one with the smallest id.
• Always back up your data before running a DELETE.

4. WHERE vs HAVING Basic

📊 Sample Data (sales):

sale_id	dept_id	amount	sale_date
1	1	100	2024-01-01
2	1	200	2024-01-02
3	2	300	2024-01-01
4	2	50	2024-01-03
5	3	400	2024-01-02
6	1	150	2024-01-04

✅ Expected Output:

dept_id | avg_sales | count
1       | 150.00    | 3
2       | 175.00    | 2

💻 Solution Query:

SELECT dept_id, AVG(amount) AS avg_sales, COUNT(*) AS count
FROM sales
WHERE amount > 100
GROUP BY dept_id
HAVING COUNT(*) >= 2;

🧠 Logic Explanation:

• WHERE filters individual rows before grouping (cannot use aggregates).
• HAVING filters groups after grouping (can use aggregates).
• Execution order: FROM → WHERE → GROUP BY → HAVING → SELECT.

5. Highest Salary per Department Basic

📊 Sample Data (employee):

emp_id	name	salary	dept_id
1	John	50000	1
2	Sarah	65000	1
3	Mike	75000	2
4	Emma	70000	2
5	David	60000	1
6	Lisa	80000	2
7	Tom	45000	3

✅ Expected Output:

emp_id | name  | salary | dept_id
2      | Sarah | 65000  | 1
6      | Lisa  | 80000  | 2
7      | Tom   | 45000  | 3

💻 Solution Query:

WITH ranked AS (
    SELECT *,
           RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
    FROM employee
)
SELECT employee_id, name, salary, dept_id
FROM ranked
WHERE rnk = 1;

🧠 Logic Explanation:

• PARTITION BY dept_id creates groups per department.
• ORDER BY salary DESC ranks highest first.
• RANK() handles ties (multiple employees with the same max salary).

6. Cumulative Sum Basic

📊 Sample Data (monthly_sales):

month	sales
Jan	1000
Feb	1500
Mar	1200
Apr	1800
May	2000

✅ Expected Output:

month | sales | cumul
Jan   | 1000  | 1000
Feb   | 1500  | 2500
Mar   | 1200  | 3700
Apr   | 1800  | 5500
May   | 2000  | 7500

💻 Solution Query:

SELECT month, sales,
       SUM(sales) OVER (ORDER BY month) AS cumulative
FROM monthly_sales;

🧠 Logic Explanation:

• Window function with ORDER BY creates a running total.
• Default frame is ROWS UNBOUNDED PRECEDING TO CURRENT ROW.
• Use PARTITION BY to reset the cumulative sum per group.

7. Nth Highest Salary Basic

📊 Sample Data (employee salaries):

50000, 60000, 70000, 65000, 70000, 55000

✅ Expected Output (3rd highest): 60000

💻 Solution Query:

SELECT DISTINCT salary
FROM employee e1
WHERE 2 = (SELECT COUNT(DISTINCT salary)
           FROM employee e2
           WHERE e2.salary > e1.salary);

🧠 Logic Explanation:

• The subquery counts distinct salaries higher than the current salary.
• For the 3rd highest, there must be exactly 2 distinct higher salaries.
• Alternative: use DENSE_RANK() or LIMIT with OFFSET.

8. Pattern Matching (Starts with 'A') Basic

📊 Sample Data (names):

Alice, Bob, Andrew, Anna, Alex, Aaron, Adam

✅ Expected Output (names starting with 'A' and exactly 5 chars):

Alice, Anna, Alex, Adam

💻 Solution Query:

SELECT name
FROM employees
WHERE name LIKE 'A%' AND LENGTH(name) = 5;

🧠 Logic Explanation:

• LIKE 'A%' matches names starting with "A".
• LENGTH(name) = 5 ensures exactly 5 characters.
• Alternatively, use LIKE 'A____' (A + 4 underscores).

9. Find Missing Numbers Basic

📊 Sample Data (ids):

1, 2, 4, 5, 7, 8, 10

✅ Expected Output:

3, 6, 9

💻 Solution Query:

WITH RECURSIVE numbers(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < (SELECT MAX(id) FROM seq)
)
SELECT n FROM numbers WHERE n NOT IN (SELECT id FROM seq);

🧠 Logic Explanation:

• A recursive CTE generates all numbers from 1 to the maximum id.
• NOT IN finds numbers that are not present in the table.
• Alternative: use LEAD() to detect gaps.

10. Departments with ≥3 Employees Basic

📊 Sample Data (employee):

emp_id	name	salary	dept_id
1	John	50000	1
2	Sarah	60000	1
3	Mike	70000	2
4	Emma	65000	2
5	David	55000	1
6	Lisa	45000	3
7	Tom	48000	3
8	Jerry	52000	4

✅ Expected Output:

dept_id | count | avg_salary
1       | 3     | 55000.00
2       | 2     | 67500.00
3       | 2     | 46500.00

💻 Solution Query:

SELECT dept_id, COUNT(*) AS count, AVG(salary) AS avg_salary
FROM employee
GROUP BY dept_id
HAVING COUNT(*) >= 3;

🧠 Logic Explanation:

• Group by department and count employees.
• HAVING filters for departments with at least 3 employees.

11. Top 3 Employees per Department Basic

📊 Sample Data (employee):

emp_id	name	salary	dept_id
1	John	50000	1
2	Sarah	65000	1
3	Mike	75000	2
4	Emma	70000	2
5	David	60000	1
6	Lisa	80000	2
7	Tom	45000	3
8	Jerry	48000	3
9	Anna	52000	3
10	Bob	55000	3

✅ Expected Output: Top 3 salaries per department.

💻 Solution Query:

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
    FROM employee
)
SELECT * FROM ranked WHERE rn <= 3;

🧠 Logic Explanation:

• ROW_NUMBER() assigns 1, 2, 3 … within each department.
• Use DENSE_RANK() instead if you want to include ties without skipping numbers.

12. Employees with >5 Years Service Basic

📊 Sample Data (employee):

emp_id	name	hire_date
1	John	2018-01-15
2	Sarah	2020-03-20
3	Mike	2015-06-10
4	Emma	2019-11-01
5	David	2016-08-05
6	Lisa	2022-01-10

✅ Expected Output: Employees with >5 years of service.

💻 Solution Query:

SELECT * FROM employee
WHERE hire_date <= DATE_SUB(CURRENT_DATE, INTERVAL 5 YEAR);

🧠 Logic Explanation:

• Subtract 5 years from the current date.
• Compare with the hire date – if hire date is on or before that, the employee has at least 5 years.

13. Students Who Took All Courses Basic

📊 Sample Data:

• courses: course_id = 1,2,3,4
• enrollments: (student_id, course_id): (1,1),(1,2),(1,3),(1,4), (2,1),(2,2), (3,1),(3,2),(3,3)

✅ Expected Output: student_id = 1

💻 Solution Query:

SELECT student_id
FROM enrollments
GROUP BY student_id
HAVING COUNT(DISTINCT course_id) = (SELECT COUNT(*) FROM courses);

🧠 Logic Explanation:

• Count distinct courses taken by each student.
• Compare with the total number of courses available.

14. Last 30 Days Sales Basic

📊 Sample Data (sales):

sale_date	amount
2024-01-01	100
2024-01-03	150
2024-01-05	200
2024-01-08	120
2024-01-10	180
2024-01-15	90

✅ Expected Output: Daily sales totals for the last 30 days (including days with zero sales if needed).

💻 Solution Query:

SELECT sale_date, SUM(amount) AS total
FROM sales
WHERE sale_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY sale_date;

🧠 Logic Explanation:

• DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) gives the date 30 days ago.
• Group by date and sum amounts.

15. Pivot Table – Rows to Columns Basic

📊 Sample Data (quarterly_sales):

year	quarter	sales
2023	Q1	1000
2023	Q2	1200
2023	Q3	1100
2023	Q4	1300
2024	Q1	1500
2024	Q2	1400
2024	Q3	1600
2024	Q4	1700

✅ Expected Output:

year | Q1  | Q2  | Q3  | Q4  | total
2023 |1000 |1200 |1100 |1300 |4600
2024 |1500 |1400 |1600 |1700 |6200

💻 Solution Query:

SELECT year,
       SUM(CASE WHEN quarter='Q1' THEN sales ELSE 0 END) AS Q1,
       SUM(CASE WHEN quarter='Q2' THEN sales ELSE 0 END) AS Q2,
       SUM(CASE WHEN quarter='Q3' THEN sales ELSE 0 END) AS Q3,
       SUM(CASE WHEN quarter='Q4' THEN sales ELSE 0 END) AS Q4,
       SUM(sales) AS total
FROM quarterly_sales
GROUP BY year;

🧠 Logic Explanation:

• CASE statements create separate columns for each quarter.
• SUM aggregates values per quarter.
• This is a static pivot (requires knowing all distinct values). For dynamic pivoting, use prepared statements.

🟡 INTERMEDIATE LEVEL (16–35)

16. Running Total per Customer Intermediate

📊 Sample Data (orders):

customer_id	order_date	amount
1	2024-01-01	100
1	2024-01-05	150
1	2024-01-10	200
2	2024-01-02	50
2	2024-01-08	75
2	2024-01-12	120

✅ Expected Output: Running total per customer.

💻 Solution Query:

SELECT customer_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders;

🧠 Logic Explanation:

• PARTITION BY customer_id resets the total for each customer.
• ORDER BY order_date determines the order of accumulation.

17. Employees Earning More Than Manager Intermediate

📊 Sample Data (employee with manager_id):

emp_id	name	salary	manager_id
1	CEO	200000	NULL
2	VP	150000	1
3	Manager	80000	2
4	Dev1	90000	3
5	Dev2	75000	3

✅ Expected Output: Employees with salary > manager’s salary.

💻 Solution Query:

SELECT e.employee_id, e.name, e.salary, m.name AS manager_name, m.salary AS manager_salary
FROM employee e
JOIN employee m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

🧠 Logic Explanation:

• Self‑join connects each employee to their manager.
• Compare salaries and filter for those where employee earns more.

18. Mode (Most Frequent Value) Intermediate

📊 Sample Data (employee salaries):

50000, 60000, 50000, 70000, 50000, 60000, 60000

✅ Expected Output: 50000 (appears 3 times)

💻 Solution Query:

WITH freq AS (
    SELECT salary, COUNT(*) AS cnt,
           DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
    FROM employee
    GROUP BY salary
)
SELECT salary, cnt
FROM freq
WHERE rnk = 1;

🧠 Logic Explanation:

• Group by salary and count occurrences.
• DENSE_RANK() ranks frequencies; pick those with rank 1.
• Handles ties (multiple modes).

19. Records in A Not in B Intermediate

📊 Sample Data: Table A (customers) and Table B (premium_customers).

✅ Expected Output: Customers who are not in premium_customers.

💻 Solution Query:

SELECT a.*
FROM customers a
LEFT JOIN premium_customers b ON a.id = b.id
WHERE b.id IS NULL;

🧠 Logic Explanation:

• LEFT JOIN returns all rows from A; matching rows from B.
• WHERE b.id IS NULL filters out those with matches.
• Alternative: NOT EXISTS or NOT IN.

20. Median Salary per Department Intermediate

📊 Sample Data (employee):

dept_id	salary
1	50000, 60000, 55000
2	70000, 65000, 80000, 75000
3	45000, 48000

✅ Expected Output: Median salary per department.

💻 Solution Query:

WITH numbered AS (
    SELECT dept_id, salary,
           ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary) AS rn,
           COUNT(*) OVER (PARTITION BY dept_id) AS cnt
    FROM employee
)
SELECT dept_id, AVG(salary) AS median_salary
FROM numbered
WHERE rn IN (FLOOR((cnt+1)/2), CEIL((cnt+1)/2))
GROUP BY dept_id;

🧠 Logic Explanation:

• For each department, assign row numbers and count total rows.
• The median is the average of the middle one (odd) or two (even) values.

21. Most Recent Order per Customer Intermediate

📊 Sample Data (orders):

customer_id	order_id	order_date	amount
1	101	2024-01-01	100
1	102	2024-01-05	150
2	103	2024-01-02	50
2	104	2024-01-08	75

✅ Expected Output: Latest order for each customer.

💻 Solution Query:

WITH latest AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM orders
)
SELECT * FROM latest WHERE rn = 1;

🧠 Logic Explanation:

• Rank orders per customer by date descending.
• Keep the row with rank = 1.

22. Row Count for All Tables Intermediate

Scenario: Get exact row counts for all tables in a database.

✅ Expected Output: Table name and row count.

💻 Solution Query (using dynamic SQL):

SELECT CONCAT('SELECT "', table_name, '" AS table, COUNT(*) AS rows FROM ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'your_db';

Then execute each generated statement.

🧠 Logic Explanation:

• Use information_schema.tables to get table names.
• Generate dynamic SELECT COUNT(*) statements for each table.

23. Find Records with NULL Values Intermediate

📊 Sample Data (employee):

emp_id	name	salary	dept_id
1	John	50000	1
2	Sarah	NULL	1
3	Mike	70000	NULL

✅ Expected Output: Rows with any NULL value.

💻 Solution Query:

SELECT *
FROM employee
WHERE name IS NULL OR salary IS NULL OR dept_id IS NULL;

🧠 Logic Explanation:

• Check each column for NULL.
• Use IS NULL (not = NULL).

24. Year-over-Year Growth Intermediate

📊 Sample Data (sales):

year	total_sales
2022	3100
2023	3400
2024	4100

✅ Expected Output: Year, previous year, growth %.

💻 Solution Query:

WITH yearly AS (
    SELECT YEAR(sale_date) AS year, SUM(amount) AS total
    FROM sales
    GROUP BY YEAR(sale_date)
)
SELECT year, total,
       LAG(total) OVER (ORDER BY year) AS prev,
       ROUND((total - LAG(total) OVER (ORDER BY year)) / LAG(total) OVER (ORDER BY year) * 100, 2) AS growth_pct
FROM yearly;

🧠 Logic Explanation:

• LAG() fetches the previous year’s total.
• Growth percentage = (current – previous) / previous * 100.

25. Top 5 Products per Category Intermediate

📊 Sample Data (product_sales):

category_id	product_name	sales
1	Laptop	15000
1	Monitor	12000
1	Keyboard	8000
2	Shirt	11000
2	Jacket	13000

✅ Expected Output: Top 5 products per category.

💻 Solution Query:

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rn
    FROM product_sales
)
SELECT * FROM ranked WHERE rn <= 5;

🧠 Logic Explanation:

• Partition by category and order by sales descending.
• Keep the first 5 rows per category.

26. First and Last Order per Customer Intermediate

📊 Sample Data (orders):

customer_id	order_date	amount
1	2024-01-01	100
1	2024-01-05	150
2	2024-01-02	50

✅ Expected Output: First and last order date for each customer.

💻 Solution Query:

SELECT customer_id,
       MIN(order_date) AS first_order,
       MAX(order_date) AS last_order,
       COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id;

🧠 Logic Explanation:

• Use MIN and MAX aggregation on order_date per customer.
• Works well for date-only information.

27. Consecutive Days with Sales > $1000 Intermediate

📊 Sample Data (daily_sales):

sale_date	amount
2024-01-01	1200
2024-01-02	900
2024-01-03	1100
2024-01-04	1300
2024-01-05	1400

✅ Expected Output: Streaks of consecutive days with sales > 1000.

💻 Solution Query:

WITH daily AS (
    SELECT sale_date, SUM(amount) AS total,
           CASE WHEN SUM(amount) > 1000 THEN 1 ELSE 0 END AS flag
    FROM sales
    GROUP BY sale_date
),
groups AS (
    SELECT sale_date, total, flag,
           ROW_NUMBER() OVER (ORDER BY sale_date) -
           ROW_NUMBER() OVER (PARTITION BY flag ORDER BY sale_date) AS grp
    FROM daily
)
SELECT MIN(sale_date) AS start, MAX(sale_date) AS end, COUNT(*) AS streak
FROM groups
WHERE flag = 1
GROUP BY grp
HAVING COUNT(*) >= 2;

🧠 Logic Explanation:

• Create a flag for days above threshold.
• The difference of two row numbers gives a group identifier for consecutive rows with the same flag.
• Aggregate to get start, end, and length of each streak.

28. Intersection of Two Tables Intermediate

📊 Sample Data: Table A and Table B with same columns.

✅ Expected Output: Rows that exist in both tables.

💻 Solution Query:

SELECT * FROM table_a
INTERSECT
SELECT * FROM table_b;

Or (using JOIN):

SELECT a.*
FROM table_a a
INNER JOIN table_b b ON a.id = b.id AND a.name = b.name AND a.date = b.date;

🧠 Logic Explanation:

• INTERSECT is the set intersection operator (available in many databases).
• Alternatively, an INNER JOIN on all columns gives the same result.

29. Cumulative Distribution of Salaries Intermediate

📊 Sample Data (employee salaries):

50000, 60000, 55000, 70000, 65000, 80000, 45000

✅ Expected Output: Percentile rank for each salary.

💻 Solution Query:

SELECT salary,
       ROUND(CUME_DIST() OVER (ORDER BY salary) * 100, 2) AS percentile
FROM employee
ORDER BY salary;

🧠 Logic Explanation:

• CUME_DIST() returns the cumulative distribution (proportion of rows with salary ≤ current).
• Multiply by 100 to get percentile.

30. Top Performing Salesperson per Quarter Intermediate

📊 Sample Data (sales):

salesperson_id	sale_date	amount
1	2024-01-15	10000
2	2024-01-05	15000
1	2024-02-20	12000

✅ Expected Output: Top salesperson per quarter.

💻 Solution Query:

WITH quarterly AS (
    SELECT salesperson_id,
           YEAR(sale_date) AS year,
           QUARTER(sale_date) AS quarter,
           SUM(amount) AS total
    FROM sales
    GROUP BY salesperson_id, YEAR(sale_date), QUARTER(sale_date)
),
ranked AS (
    SELECT *,
           RANK() OVER (PARTITION BY year, quarter ORDER BY total DESC) AS rnk
    FROM quarterly
)
SELECT * FROM ranked WHERE rnk = 1;

🧠 Logic Explanation:

• Aggregate sales per salesperson per quarter.
• Rank them within each quarter and pick the top rank.

31. Gaps in Sequence Numbers Intermediate

📊 Sample Data (ids): 1,2,3,5,6,7,10,11,12

✅ Expected Output: Missing numbers (4,8,9) or gap ranges.

💻 Solution Query:

WITH gaps AS (
    SELECT id,
           LEAD(id) OVER (ORDER BY id) AS next_id
    FROM seq
)
SELECT id + 1 AS gap_start, next_id - 1 AS gap_end
FROM gaps
WHERE next_id > id + 1;

🧠 Logic Explanation:

• LEAD() gets the next id in sequence.
• If the gap between current and next is > 1, then there is a missing range.

32. Month with Highest Sales Each Year Intermediate

📊 Sample Data (sales): similar to previous.

✅ Expected Output: For each year, the month with the highest sales.

💻 Solution Query:

WITH monthly AS (
    SELECT YEAR(sale_date) AS year, MONTH(sale_date) AS month,
           SUM(amount) AS total
    FROM sales
    GROUP BY YEAR(sale_date), MONTH(sale_date)
),
ranked AS (
    SELECT *,
           RANK() OVER (PARTITION BY year ORDER BY total DESC) AS rnk
    FROM monthly
)
SELECT year, month, total
FROM ranked
WHERE rnk = 1;

🧠 Logic Explanation:

• Aggregate sales by year and month.
• Rank within each year by total sales; pick the top.

33. Employees Above Department Average Intermediate

📊 Sample Data (employee): same as earlier.

✅ Expected Output: Employees with salary > department average.

💻 Solution Query:

WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employee
    GROUP BY dept_id
)
SELECT e.*
FROM employee e
JOIN dept_avg da ON e.dept_id = da.dept_id
WHERE e.salary > da.avg_salary;

🧠 Logic Explanation:

• Compute average salary per department.
• Join back and filter where salary exceeds the average.

34. Update Table with Values from Another Table Intermediate

📊 Sample Data: employees and salary_updates tables.

✅ Expected Output: employees.salary updated from salary_updates.new_salary.

💻 Solution Query:

UPDATE employees e
JOIN salary_updates su ON e.employee_id = su.employee_id
SET e.salary = su.new_salary;

🧠 Logic Explanation:

• Join the two tables on the employee id.
• Set the salary column to the new value.

35. Moving Average (Last 7 Days) Intermediate

📊 Sample Data (daily_sales):

sale_date	amount
2024-01-01	100
2024-01-02	150

✅ Expected Output: 7‑day moving average.

💻 Solution Query:

SELECT sale_date, amount,
       AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d
FROM daily_sales;

🧠 Logic Explanation:

• Use window frame to include current row and the 6 preceding rows.
• Calculates average over that window.

🔴 ADVANCED LEVEL (36–50)

36. Longest Streak of Consecutive Days Advanced

📊 Sample Data: similar to Q27, but with more data.

✅ Expected Output: The longest run of consecutive days meeting the condition.

💻 Solution Query: (similar to Q27 but with ranking the streaks)

WITH streaks AS (
    -- same logic as Q27 to get each streak and its length
)
SELECT start, end, streak_length,
       DENSE_RANK() OVER (ORDER BY streak_length DESC) AS rnk
FROM streaks
WHERE rnk = 1;

🧠 Logic Explanation:

• First find all streaks.
• Then rank them by length descending and pick the top.

37. Hierarchical/Recursive Queries (Employee Org Chart) Advanced

📊 Sample Data (employee with manager_id):

emp_id	name	manager_id
1	CEO	NULL
2	VP	1
3	Manager	2
4	Dev	3

✅ Expected Output: Full org chart with indentation.

💻 Solution Query:

WITH RECURSIVE org AS (
    SELECT emp_id, name, manager_id, 0 AS level, name AS path
    FROM employee
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.emp_id, e.name, e.manager_id, o.level + 1,
           CONCAT(o.path, ' -> ', e.name)
    FROM employee e
    JOIN org o ON e.manager_id = o.emp_id
)
SELECT CONCAT(REPEAT('  ', level), name) AS hierarchy, level, path
FROM org
ORDER BY path;

🧠 Logic Explanation:

• Recursive CTE starts with the root (CEO).
• Each recursion adds direct reports.
• Level and path help visualize the tree.

38. 5-Day Moving Average of Stock Prices Advanced

📊 Sample Data (stock_prices):

trade_date	close_price
2024-01-01	100.00
2024-01-02	102.00

✅ Expected Output: 5-day moving average.

💻 Solution Query:

SELECT trade_date, close_price,
       ROUND(AVG(close_price) OVER (ORDER BY trade_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW), 2) AS ma_5d
FROM stock_prices;

🧠 Logic Explanation: Same as Q35 but for stock prices.

39. Customers Inactive for 6+ Months Advanced

📊 Sample Data: customers and orders tables.

✅ Expected Output: Customers whose last order was > 6 months ago.

💻 Solution Query:

SELECT customer_id, MAX(order_date) AS last_order
FROM orders
GROUP BY customer_id
HAVING MAX(order_date) < DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH);

🧠 Logic Explanation:

• Group by customer, get latest order date.
• Filter for those whose latest order is before the date 6 months ago.

40. Sessionization of User Activity Advanced

📊 Sample Data (user_events):

user_id	event_timestamp	event_type
1	2024-01-01 10:00:00	login
1	2024-01-01 10:05:00	view

✅ Expected Output: Session IDs and session boundaries (gap > 30 min).

💻 Solution Query:

WITH events AS (
    SELECT user_id, event_timestamp,
           LAG(event_timestamp) OVER (PARTITION BY user_id ORDER BY event_timestamp) AS prev_ts
    FROM user_events
),
session_marks AS (
    SELECT *,
           CASE WHEN prev_ts IS NULL OR TIMESTAMPDIFF(MINUTE, prev_ts, event_timestamp) > 30
                THEN 1 ELSE 0 END AS new_session
    FROM events
),
session_ids AS (
    SELECT *,
           SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_timestamp) AS session_id
    FROM session_marks
)
SELECT user_id, session_id,
       MIN(event_timestamp) AS session_start,
       MAX(event_timestamp) AS session_end,
       COUNT(*) AS events
FROM session_ids
GROUP BY user_id, session_id;

🧠 Logic Explanation:

• Use LAG to get previous timestamp.
• Mark a new session if gap > 30 min.
• Use a running sum of new session marks to assign session IDs.
• Aggregate to get session boundaries.

41. First Purchase Date in Last 3 Years Advanced

📊 Sample Data: customers and orders.

✅ Expected Output: For each customer, the first order date within the last 3 years.

💻 Solution Query:

SELECT customer_id, MIN(order_date) AS first_purchase
FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 3 YEAR)
GROUP BY customer_id;

🧠 Logic Explanation:

• Filter orders to last 3 years.
• Group by customer and take the minimum order date.

42. Full Outer Join Advanced

📊 Sample Data: Table A and Table B.

✅ Expected Output: All rows from both tables, matched where possible.

💻 Solution Query:

SELECT COALESCE(a.id, b.id) AS id,
       a.name AS name_a, b.name AS name_b
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.id;

🧠 Logic Explanation:

• FULL OUTER JOIN returns all rows from both tables.
• Use COALESCE to handle missing keys.
• For databases without FULL JOIN, use UNION of LEFT and RIGHT joins.

43. All Permutations of a Column Value Advanced

📊 Sample Data (items): A, B, C

✅ Expected Output: All unordered pairs: (A,B), (A,C), (B,C).

💻 Solution Query:

SELECT a.item AS item1, b.item AS item2
FROM items a
CROSS JOIN items b
WHERE a.item < b.item;

🧠 Logic Explanation:

• CROSS JOIN creates all combinations.
• Condition a.item < b.item ensures each pair only once and avoids self-pairs.

44. Percentage Contribution to Total Sales Advanced

📊 Sample Data (product_sales):

product	sales
Laptop	15000
Monitor	12000
Printer	9000

✅ Expected Output: Product, sales, percentage of total, and cumulative percentage.

💻 Solution Query:

SELECT product, sales,
       ROUND(sales * 100.0 / SUM(sales) OVER (), 2) AS pct,
       ROUND(SUM(sales) OVER (ORDER BY sales DESC) * 100.0 / SUM(sales) OVER (), 2) AS cum_pct
FROM product_sales
ORDER BY sales DESC;

🧠 Logic Explanation:

• SUM(sales) OVER () gives total sales.
• Percentage = sales / total * 100.
• Cumulative percentage uses ORDER BY in the window.

45. Most Frequent Item per Order Advanced

📊 Sample Data (order_details): order_id, product_id, quantity.

✅ Expected Output: For each order, the product with the highest quantity.

💻 Solution Query:

WITH ranked AS (
    SELECT order_id, product_id, quantity,
           RANK() OVER (PARTITION BY order_id ORDER BY quantity DESC) AS rnk
    FROM order_details
)
SELECT order_id, product_id, quantity
FROM ranked
WHERE rnk = 1;

🧠 Logic Explanation:

• Rank items within each order by quantity descending.
• Pick the top rank (handles ties).

46. Wide to Long Format (Unpivot) Advanced

📊 Sample Data (quarterly_sales wide):

employee_id	q1_sales	q2_sales	q3_sales	q4_sales
1	1000	1200	1100	1300

✅ Expected Output: employee_id, quarter, sales (long format).

💻 Solution Query:

SELECT employee_id, 'Q1' AS quarter, q1_sales AS sales FROM quarterly_sales
UNION ALL
SELECT employee_id, 'Q2', q2_sales FROM quarterly_sales
UNION ALL
SELECT employee_id, 'Q3', q3_sales FROM quarterly_sales
UNION ALL
SELECT employee_id, 'Q4', q4_sales FROM quarterly_sales;

🧠 Logic Explanation:

• Use UNION ALL to stack columns as rows.
• Each SELECT corresponds to one column.
• Alternative: UNPIVOT operator (SQL Server, Oracle).

47. Nth Customer's First and Last Order Advanced

📊 Sample Data: customers and orders.

✅ Expected Output: For the nth customer (by id), their first and last order date.

💻 Solution Query:

WITH customer_orders AS (
    SELECT customer_id, MIN(order_date) AS first_order, MAX(order_date) AS last_order
    FROM orders
    GROUP BY customer_id
),
ranked_customers AS (
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY customer_id) AS rn
    FROM customers
)
SELECT rc.customer_id, rc.name, co.first_order, co.last_order
FROM ranked_customers rc
JOIN customer_orders co ON rc.customer_id = co.customer_id
WHERE rc.rn = 10;  -- 10th customer

🧠 Logic Explanation:

• Assign row numbers to customers sorted by id.
• Join with customer order aggregates to get first/last.

48. Related Transactions (Same User, Within 1 Hour) Advanced

📊 Sample Data (transactions):

transaction_id	user_id	transaction_time	amount
1	1	2024-01-01 10:00:00	100
2	1	2024-01-01 10:30:00	150

✅ Expected Output: Pairs of transactions by same user within 60 minutes.

💻 Solution Query:

SELECT t1.user_id, t1.transaction_id AS t1_id, t1.transaction_time AS t1_time,
       t2.transaction_id AS t2_id, t2.transaction_time AS t2_time
FROM transactions t1
JOIN transactions t2 ON t1.user_id = t2.user_id
    AND t1.transaction_id < t2.transaction_id
    AND t2.transaction_time <= DATE_ADD(t1.transaction_time, INTERVAL 1 HOUR);

🧠 Logic Explanation:

• Self‑join on user_id.
• Condition ensures t2 is later than t1 and within 1 hour.

49. Retry Logic for Failed Transactions Advanced

📊 Sample Data (transaction_attempts):

attempt_id	order_id	attempt_number	attempt_time	status
1	1001	1	2024-01-01 10:00:00	FAILED
2	1001	2	2024-01-01 10:05:00	SUCCESS

✅ Expected Output: For each order, the status of each attempt and retry information.

💻 Solution Query:

SELECT order_id, attempt_number, attempt_time, status,
       LAG(status) OVER (PARTITION BY order_id ORDER BY attempt_time) AS prev_status
FROM transaction_attempts;

🧠 Logic Explanation:

• Use LAG to get previous attempt status.
• This allows you to see if a failure was followed by a success.

50. Shortest Path Between Two Nodes in a Graph Advanced

📊 Sample Data (graph_edges):

node1	node2	distance
A	B	10
B	C	20
A	C	50

✅ Expected Output: Shortest path and total distance from A to C.

💻 Solution Query:

WITH RECURSIVE path AS (
    SELECT node1 AS start, node2 AS end, distance AS total, CAST(node1 AS CHAR(1000)) AS nodes
    FROM graph_edges
    WHERE node1 = 'A'
    UNION ALL
    SELECT p.start, ge.node2, p.total + ge.distance, CONCAT(p.nodes, '->', ge.node2)
    FROM path p
    JOIN graph_edges ge ON p.end = ge.node1
    WHERE p.nodes NOT LIKE CONCAT('%', ge.node2, '%')
)
SELECT nodes, total
FROM path
WHERE end = 'C'
ORDER BY total
LIMIT 1;

🧠 Logic Explanation:

• Recursive CTE traverses edges from start node.
• Keeps track of visited nodes to avoid cycles.
• At the end, filter for target node and order by total distance.

🎉 Congratulations! You’ve now mastered 50 essential SQL interview questions.

Practice these queries, understand the logic, and you’ll be ready to ace any SQL interview. 💪

📌 Pro tip: Try writing your own variations and test them on real datasets.

👍 If this guide helped you, clap and share it with fellow developers.

✨ Follow for more data‑related content. Happy querying! 🚀