SQL Programming Resources

15 Essential SQL Interview Questions (With Detailed Answers)

1. Explain Order of Execution of SQL Query

Answer: The order in which SQL queries are executed is:

1. FROM

2. WHERE

3. GROUP BY

4. HAVING

5. SELECT

6. ORDER BY

7. LIMIT/OFFSET

This order is essential to understand how SQL processes data, especially when using aggregates and filtering.

2. Use Case for RANK, DENSE_RANK & ROW_NUMBER

RANK: Assigns the same rank to ties, but skips the next rank(s).

DENSE_RANK: Same rank for ties, but does not skip any ranks.

ROW_NUMBER: Assigns unique numbers to each row, even if values are same.

Use case example: Find top 3 employees by salary:

SELECT *, RANK() OVER (ORDER BY salary DESC) as rnk FROM employees;

3. Query to Find Cumulative Sum/Running Total

SELECT name, salary,
SUM(salary) OVER (ORDER BY id) AS running_total
FROM employees;

4. Most Selling Product / Highest Salary

Most Selling Product:

SELECT product_id, SUM(quantity) AS total_sold
FROM sales
GROUP BY product_id
ORDER BY total_sold DESC
LIMIT 1;

Highest Salary:

SELECT MAX(salary) FROM employees;

5. Nth Highest Salary

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

Replace N with desired rank.

6. Difference Between UNION vs UNION ALL

UNION removes duplicates.

UNION ALL keeps all records including duplicates.

UNION is slower due to deduplication.

7. Identify Duplicates

SELECT name, COUNT(*)
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

8. Joins Scenario

Inner Join: Only matching records

Left Join: All from left + matches from right

Full Outer Join: All records from both with NULL where no match

Example:

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

9. LAG - Find records with increased transaction

SELECT *,
LAG(transaction_value) OVER (ORDER BY transaction_date) as prev_txn
FROM transactions
WHERE transaction_value > prev_txn;

10. Rank vs Dense Rank - 2nd Highest Salary

SELECT * FROM (
SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
FROM employees
) sub
WHERE rnk = 2;

11. Running Difference Using Window Function

SELECT name, transaction_value,
transaction_value - LAG(transaction_value) OVER (ORDER BY id) as difference
FROM transactions;

12. YoY or MoM Growth

SELECT year, month, total_sales,
total_sales - LAG(total_sales) OVER (ORDER BY year, month) AS growth
FROM sales_summary;

13. Rolling Average of Daily Sign-ups

SELECT signup_date, COUNT(*) AS daily_signups,
AVG(COUNT(*)) OVER (ORDER BY signup_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_avg_7_days
FROM signups
GROUP BY signup_date;

14. Running Difference Using Self Join

SELECT a.id, a.value - b.value AS running_difference
FROM transactions a
JOIN transactions b ON a.id = b.id + 1;

15. Cumulative Sum Using Self Join

SELECT a.id, a.name, SUM(b.salary) AS cumulative_salary
FROM employees a
JOIN employees b ON b.id <= a.id
GROUP BY a.id, a.name;

Like for more free resources

👍5❤4🎉1

3.53K viewsedited 06:59