Mastering SQL: 15 Common Interview Questions and Expert Solutions

Structured Query Language (SQL) is a fundamental skill for data professionals. Whether you’re preparing for a technical interview or improving your database expertise, mastering SQL is crucial. In this article, we tackle 15 commonly asked SQL interview questions, providing efficient solutions and insights.

1. How to Find the N-th Highest Salary Without Using LIMIT, OFFSET, or TOP?

SELECT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
    FROM employees
) t
WHERE rnk = N;

• Uses DENSE_RANK() to assign ranking without skipping numbers when duplicates exist.

2. Difference Between CROSS JOIN, FULL OUTER JOIN, and SELF JOIN

CROSS JOIN: Produces a Cartesian product of both tables.

FULL OUTER JOIN: Returns all matching and non-matching rows from both tables.

SELF JOIN: Joins a table to itself, useful for hierarchical relationships.

3. Detecting Gaps in Missing Sequential Data (e.g., Invoice Numbers)

SELECT invoice_number + 1 AS missing_start
FROM invoices i
WHERE NOT EXISTS (
    SELECT 1 FROM invoices WHERE invoice_number = i.invoice_number + 1
)
ORDER BY invoice_number;

Identifies missing sequences by checking for gaps in the numbering.

4. PIVOT and UNPIVOT with Real-World Use Cases

PIVOT: Converts row-based data into columns.

UNPIVOT: Converts columns into rows.

SELECT * FROM (
    SELECT department, year, revenue FROM sales
) s
PIVOT (
    SUM(revenue) FOR year IN ([2022], [2023], [2024])
) p;

Example: Sales data for multiple years transformed into columns.

5. Efficiently Finding the Median Salary

SELECT salary FROM (
    SELECT salary, NTILE(2) OVER (ORDER BY salary) as grp
    FROM employees
) t
WHERE grp = 1
ORDER BY salary DESC
LIMIT 1;

Uses NTILE(2) to split data into two halves and selects the middle value.

6. Optimizing Slow Queries on Large Datasets

Indexing: B-Trees for lookups, Bitmap indexes for low-cardinality data.

Partitioning: Divide large tables into smaller chunks.

Query Optimization: Avoid SELECT *, use EXPLAIN ANALYZE.

Denormalization: Precompute aggregates for performance.

7. Understanding WINDOW Functions (ROW_NUMBER vs. RANK vs. DENSE_RANK)

**ROW_NUMBER()**: Assigns a unique sequence (no ties).

**RANK()**: Allows ranking gaps when duplicates exist.

**DENSE_RANK()**: Ranks sequentially without gaps.

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK() OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

8. Recursive CTEs for Hierarchical Data

WITH RECURSIVE org_hierarchy AS (
    SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN org_hierarchy h ON e.manager_id = h.id
)
SELECT * FROM org_hierarchy;

Useful for organization trees and category structures.

9. Generating Running Totals and Moving Averages

SELECT employee_id, salary,
       SUM(salary) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       AVG(salary) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees;

Uses SUM() for cumulative totals and AVG() for moving averages.

10. Difference Between HAVING and WHERE

**WHERE** filters rows before aggregation.

**HAVING** filters aggregated results.

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

11. Finding and Removing Duplicates

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

12. UNION vs. UNION ALL

**UNION**: Removes duplicates.

**UNION ALL**: Keeps all rows (faster).

SELECT name FROM employees
UNION
SELECT name FROM customers;

13. Handling Data Skew in Distributed SQL Engines

Partition wisely: Use high-cardinality columns.

Bucketing: Spread data evenly across nodes.

Avoid Data Shuffling: Use CLUSTER BY in BigQuery.

SELECT * FROM sales
WHERE region = 'APAC'
CLUSTER BY region;

14. The Impact of Indexing on Query Performance

Speeds up lookups: Uses B-Trees or Hash indexes.

Slows down writes: Index maintenance required.

Types:

• Clustered Index: Physically sorts data.
• Non-Clustered Index: Stores pointers.
• Composite Index: Indexes multiple columns.

15. Finding the Longest Consecutive Streak of Events

WITH streaks AS (
    SELECT user_id, login_date,
           login_date - INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY AS grp
    FROM logins
)
SELECT user_id, COUNT(*) AS longest_streak
FROM streaks
GROUP BY user_id, grp
ORDER BY longest_streak DESC
LIMIT 1;

• Groups consecutive logins and finds the longest streak.

Conclusion

SQL proficiency is essential for data professionals, and mastering these concepts will help you excel in interviews and real-world scenarios. Understanding ranking functions, indexing, joins, and optimization techniques can significantly improve your efficiency. Keep practicing and refining your skills!

Have more SQL questions? Drop them in the comments! 🚀