SQL Interview Questions & Solutions Page 1 of 21

300 REAL SQL INTERVIEW QUESTIONS

Asked at PwC, Deloitte, EY, KPMG, Tredence, Persistent Systems, Accenture & More
Compiled by Krishna Singh

Medium to Advanced SQL Questions

1. Find the second highest salary from the Employee table.

SELECT MAX(salary) AS SecondHighestSalary

FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
);

2. Find duplicate records in a table based on name.

SELECT name, COUNT(*)

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

3. Retrieve employees who earn more than their manager.

SELECT [Link] AS Employee, [Link], [Link] AS Manager, [Link] AS ManagerSalary

FROM employees e
JOIN employees m ON e.manager_id = [Link]
WHERE [Link] > [Link];

4. Count employees in each department having more than 5 employees.

SELECT department_id, COUNT(*) AS num_employees

FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

5. Find employees who joined in the last 6 months.

SELECT *
FROM employees
WHERE join_date >= CURRENT_DATE - INTERVAL '6 months';
SQL Interview Questions & Solutions Page 2 of 21

6. Get departments with no employees.

SELECT d.department_name
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
WHERE [Link] IS NULL;

7. Write a query to find the median salary.

SELECT AVG(salary) AS median_salary

FROM (
SELECT salary
FROM employees
ORDER BY salary
LIMIT 2 - (SELECT COUNT(*) FROM employees) % 2
OFFSET (SELECT (COUNT(*) - 1) / 2 FROM employees)
) AS median_subquery;

8. Running total of salaries by department.

SELECT name, department_id, salary,

SUM(salary) OVER (PARTITION BY department_id ORDER BY id) AS running_total
FROM employees;

9. Find the longest consecutive streak of daily logins for each user.

WITH login_dates AS (
SELECT user_id, login_date,
login_date - INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY
login_date) DAY AS grp
FROM user_logins
)
SELECT user_id, COUNT(*) AS streak_length,
MIN(login_date) AS start_date, MAX(login_date) AS end_date
FROM login_dates
GROUP BY user_id, grp
ORDER BY streak_length DESC;
SQL Interview Questions & Solutions Page 3 of 21

10. Recursive query to find the full reporting chain for each employee.

WITH RECURSIVE reporting_chain AS (

SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT [Link], [Link], e.manager_id, [Link] + 1
FROM employees e
JOIN reporting_chain rc ON e.manager_id = [Link]
)
SELECT * FROM reporting_chain ORDER BY level, id;

11. Write a query to find gaps in a sequence of numbers (missing IDs).

SELECT (id + 1) AS missing_id

FROM employees e1
WHERE NOT EXISTS (
SELECT 1 FROM employees e2 WHERE [Link] = [Link] + 1
)
ORDER BY missing_id;

12. Calculate cumulative distribution (CDF) of salaries.

SELECT name, salary,

CUME_DIST() OVER (ORDER BY salary) AS salary_cdf
FROM employees;

13. Compare two tables and find rows with differences in any column.

SELECT *
FROM table1 t1
FULL OUTER JOIN table2 t2 ON [Link] = [Link]
WHERE t1.col1 IS DISTINCT FROM t2.col1
OR t1.col2 IS DISTINCT FROM t2.col2
OR t1.col3 IS DISTINCT FROM t2.col3;

14. Write a query to rank employees based on salary with ties handled properly.

SELECT name, salary,

RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;
SQL Interview Questions & Solutions Page 4 of 21

15. Find customers who have not made any purchase.

SELECT c.customer_id, [Link]

FROM customers c
LEFT JOIN sales s ON c.customer_id = s.customer_id
WHERE s.sale_id IS NULL;

16. Write a query to perform a conditional aggregation (count males and females in each department).

SELECT department_id,
COUNT(CASE WHEN gender = 'M' THEN 1 END) AS male_count,
COUNT(CASE WHEN gender = 'F' THEN 1 END) AS female_count
FROM employees
GROUP BY department_id;

17. Write a query to calculate the difference between current row and previous row's salary (lag
function).

SELECT name, salary,

salary - LAG(salary) OVER (ORDER BY id) AS salary_diff
FROM employees;

18. Identify overlapping date ranges for bookings.

SELECT b1.booking_id AS booking1, b2.booking_id AS booking2

FROM bookings b1
JOIN bookings b2 ON b1.booking_id <> b2.booking_id
WHERE b1.start_date <= b2.end_date
AND b1.end_date >= b2.start_date;

19. Find employees with salary greater than average salary in the entire company, ordered by salary
descending.

SELECT name, salary

FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

20. Aggregate JSON data to list all employee names in a department as a JSON array.

SELECT department_id, JSON_AGG(name) AS employee_names

FROM employees
GROUP BY department_id;
SQL Interview Questions & Solutions Page 5 of 21

21. Find employees who have the same salary as their manager.

SELECT [Link] AS Employee, [Link], [Link] AS Manager

FROM employees e
JOIN employees m ON e.manager_id = [Link]
WHERE [Link] = [Link];

22. Write a query to get the first and last purchase date for each customer.

SELECT customer_id,
MIN(purchase_date) AS first_purchase,
MAX(purchase_date) AS last_purchase
FROM sales
GROUP BY customer_id;

23. Find departments with the highest average salary.

WITH avg_salaries AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT *
FROM avg_salaries
WHERE avg_salary = (SELECT MAX(avg_salary) FROM avg_salaries);

24. Write a query to find the number of employees in each job title.

SELECT job_title, COUNT(*) AS num_employees

FROM employees
GROUP BY job_title;

25. Find employees who don't have a department assigned.

SELECT *
FROM employees
WHERE department_id IS NULL;

26. Write a query to find the difference in days between two dates in the same table.

SELECT id, DATEDIFF(day, start_date, end_date) AS days_difference

FROM projects;

Note: DATEDIFF syntax varies - replace accordingly (e.g., DATEDIFF('day', start_date, end_date) or simple subtraction in some
systems).
SQL Interview Questions & Solutions Page 6 of 21

27. Calculate the moving average of salaries over the last 3 employees ordered by hire date.

SELECT name, hire_date, salary,

AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS
moving_avg_salary
FROM employees;

28. Find the most recent purchase per customer using window functions.

SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY purchase_date DESC) AS rn
FROM sales
) sub
WHERE rn = 1;

29. Detect hierarchical depth of each employee in the org chart.

WITH RECURSIVE employee_depth AS (

SELECT id, name, manager_id, 1 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT [Link], [Link], e.manager_id, [Link] + 1
FROM employees e
JOIN employee_depth ed ON e.manager_id = [Link]
)
SELECT * FROM employee_depth;

30. Write a query to perform a self-join to find pairs of employees in the same department.

SELECT [Link] AS Employee1, [Link] AS Employee2, e1.department_id

FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id AND [Link] < [Link];

31. Write a query to pivot rows into columns dynamically/statically.

SELECT department_id,
SUM(CASE WHEN job_title = 'Manager' THEN 1 ELSE 0 END) AS Managers,
SUM(CASE WHEN job_title = 'Developer' THEN 1 ELSE 0 END) AS Developers,
SUM(CASE WHEN job_title = 'Tester' THEN 1 ELSE 0 END) AS Testers
FROM employees
GROUP BY department_id;
SQL Interview Questions & Solutions Page 7 of 21

32. Find customers who made purchases in every category available.

SELECT customer_id
FROM sales s
GROUP BY customer_id
HAVING COUNT(DISTINCT category_id) = (SELECT COUNT(DISTINCT category_id) FROM sales);

33. Identify employees who haven’t received a salary raise in more than a year.

SELECT [Link]
FROM employees e
JOIN salary_history sh ON [Link] = sh.employee_id
GROUP BY [Link], [Link]
HAVING MAX(sh.raise_date) < CURRENT_DATE - INTERVAL '1 year';

34. Write a query to rank salespeople by monthly sales, resetting the rank every month.

SELECT salesperson_id, sale_month, total_sales,

RANK() OVER (PARTITION BY sale_month ORDER BY total_sales DESC) AS monthly_rank
FROM (
SELECT salesperson_id, DATE_TRUNC('month', sale_date) AS sale_month, SUM(amount) AS
total_sales
FROM sales
GROUP BY salesperson_id, sale_month
) AS monthly_sales;

35. Calculate the percentage change in sales compared to the previous month for each product.

SELECT product_id, sale_month, total_sales,

(total_sales - LAG(total_sales) OVER (PARTITION BY product_id ORDER BY sale_month))
* 100.0 /
LAG(total_sales) OVER (PARTITION BY product_id ORDER BY sale_month) AS pct_change
FROM (
SELECT product_id, DATE_TRUNC('month', sale_date) AS sale_month, SUM(amount) AS
total_sales
FROM sales
GROUP BY product_id, sale_month
) monthly_sales;
SQL Interview Questions & Solutions Page 8 of 21

36. Find employees who earn more than the average salary across the company but less than the
highest salary in their department.

SELECT *
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees)
AND salary < (
SELECT MAX(salary)
FROM employees
WHERE department_id = e.department_id
);

37. Retrieve the last 5 orders for each customer.

SELECT *
FROM (
SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS
rn
FROM orders o
) sub
WHERE rn <= 5;

38. Find employees with no salary changes in the last 2 years.

SELECT e.*
FROM employees e
LEFT JOIN salary_history sh ON [Link] = sh.employee_id AND sh.change_date >= CURRENT_DATE -
INTERVAL '2 years'
WHERE sh.employee_id IS NULL;

39. Find the department with the lowest average salary.

SELECT department_id, AVG(salary) AS avg_salary

FROM employees
GROUP BY department_id
ORDER BY avg_salary ASC
LIMIT 1;

40. List employees whose names start and end with the same letter.

SELECT *
FROM employees
WHERE LEFT(name, 1) = RIGHT(name, 1);
SQL Interview Questions & Solutions Page 9 of 21

41. Write a query to detect circular references in employee-manager hierarchy (cycles).

WITH RECURSIVE mgr_path (id, manager_id, path) AS (

SELECT id, manager_id, ARRAY[id]
FROM employees
WHERE manager_id IS NOT NULL
UNION ALL
SELECT [Link], e.manager_id, path || [Link]
FROM employees e
JOIN mgr_path mp ON e.manager_id = [Link]
WHERE NOT [Link] = ANY(path)
)
SELECT DISTINCT id
FROM mgr_path
WHERE id = ANY(path);

42. Write a query to get the running total of sales per customer, ordered by sale date.

SELECT customer_id, sale_date, amount,

SUM(amount) OVER (PARTITION BY customer_id ORDER BY sale_date) AS running_total
FROM sales;

43. Find the department-wise salary percentile (e.g., 90th percentile) using window functions.

SELECT department_id, salary,

PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY
department_id) AS pct_90_salary
FROM employees;

44. Find employees whose salary is a prime number.

WITH primes AS (
SELECT generate_series(2, (SELECT MAX(salary) FROM employees)) AS num
EXCEPT
SELECT num FROM (
SELECT num, UNNEST(ARRAY(
SELECT generate_series(2, FLOOR(SQRT(num))) AS divisor
)) AS divisor
WHERE num % divisor = 0
) AS composite
)
SELECT *
FROM employees
WHERE salary IN (SELECT num FROM primes);

Note: This is a conceptual approach—some databases may not support this exact syntax fully.
SQL Interview Questions & Solutions Page 10 of 21

45. Find employees who have worked for multiple departments over time.

SELECT employee_id
FROM employee_department_history
GROUP BY employee_id
HAVING COUNT(DISTINCT department_id) > 1;

46. Use window function to find the difference between current row’s sales and previous row’s sales
partitioned by product.

SELECT product_id, sale_date, amount,

amount - LAG(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS
sales_diff
FROM sales;

47. Write a query to find all employees who are at the lowest level in the hierarchy (no subordinates).

SELECT *
FROM employees e
WHERE NOT EXISTS (
SELECT 1 FROM employees sub WHERE sub.manager_id = [Link]
);

48. Find average order value per month and product category.

SELECT DATE_TRUNC('month', order_date) AS order_month, category_id, AVG(order_value) AS

avg_order_value
FROM orders
GROUP BY order_month, category_id;

49. Write a query to create a running count of how many employees joined in each year.

SELECT join_year, COUNT(*) AS yearly_hires,

SUM(COUNT(*)) OVER (ORDER BY join_year) AS running_total_hires
FROM (
SELECT EXTRACT(YEAR FROM hire_date) AS join_year
FROM employees
) sub
GROUP BY join_year
ORDER BY join_year;
SQL Interview Questions & Solutions Page 11 of 21

50. Write a query to find the second most recent order date per customer.

SELECT customer_id, order_date

FROM (
SELECT customer_id, order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders
) sub
WHERE rn = 2;

51. Find employees who have never made a sale.

SELECT [Link], [Link]

FROM employees e
LEFT JOIN sales s ON [Link] = s.employee_id
WHERE s.sale_id IS NULL;

52. Find the average tenure of employees by department.

SELECT department_id, AVG(DATE_PART('year', CURRENT_DATE - hire_date)) AS avg_tenure_years

FROM employees
GROUP BY department_id;

53. Get employees with salary in the top 10% in their department.

SELECT *
FROM (
SELECT e.*, NTILE(10) OVER (PARTITION BY department_id ORDER BY salary DESC) AS decile
FROM employees e
) sub
WHERE decile = 1;

54. Find customers who purchased more than once in the same day.

SELECT customer_id, purchase_date, COUNT(*) AS purchase_count

FROM sales
GROUP BY customer_id, purchase_date
HAVING COUNT(*) > 1;

55. List all departments and their employee counts, including departments with zero employees.

SELECT d.department_id, d.department_name, COUNT([Link]) AS employee_count

FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;
SQL Interview Questions & Solutions Page 12 of 21

56. Write a query to find duplicate rows based on multiple columns.

SELECT column1, column2, COUNT(*)

FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;

57. Write a recursive query to calculate factorial of a number (e.g., 5).

WITH RECURSIVE factorial(n, fact) AS (

SELECT 1, 1
UNION ALL
SELECT n + 1, fact * (n + 1)
FROM factorial
WHERE n < 5
)
SELECT fact FROM factorial WHERE n = 5;

58. Write a query to calculate the cumulative percentage of total sales per product.

SELECT product_id, sale_amount,

SUM(sale_amount) OVER (ORDER BY sale_amount DESC) * 100.0 / SUM(sale_amount) OVER
() AS cumulative_pct
FROM sales;

59. Write a query to get employees who reported directly or indirectly to a given manager (hierarchy
traversal).

WITH RECURSIVE reporting AS (

SELECT id, name, manager_id
FROM employees
WHERE manager_id = 101 -- replace 101 with manager's id
UNION ALL
SELECT [Link], [Link], e.manager_id
FROM employees e
INNER JOIN reporting r ON e.manager_id = [Link]
)
SELECT * FROM reporting;

60. Find the average number of orders per customer and standard deviation.

SELECT AVG(order_count) AS avg_orders, STDDEV(order_count) AS stddev_orders

FROM (
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
) sub;
SQL Interview Questions & Solutions Page 13 of 21

Advanced-Level SQL Questions

61. Find gaps in date sequences for each customer (missing days).

WITH dates AS (
SELECT customer_id, purchase_date,
LAG(purchase_date) OVER (PARTITION BY customer_id ORDER BY purchase_date) AS
prev_date
FROM sales
)
SELECT customer_id, prev_date + INTERVAL '1 day' AS missing_date
FROM dates
WHERE purchase_date > prev_date + INTERVAL '1 day';

62. Rank employees by salary within their department, and calculate percent rank.

SELECT name, department_id, salary,

RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank,
PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS
salary_percent_rank
FROM employees;

63. Find products that have never been sold.

SELECT p.product_id, p.product_name

FROM products p
LEFT JOIN sales s ON p.product_id = s.product_id
WHERE s.sale_id IS NULL;

64. Write a query to find consecutive days where sales were above a threshold.

WITH flagged_sales AS (
SELECT sale_date, amount,
CASE WHEN amount > 1000 THEN 1 ELSE 0 END AS flag
FROM sales
),
groups AS (
SELECT sale_date, amount, flag,
sale_date - INTERVAL ROW_NUMBER() OVER (ORDER BY sale_date) DAY AS grp
FROM flagged_sales
WHERE flag = 1
)
SELECT MIN(sale_date) AS start_date, MAX(sale_date) AS end_date, COUNT(*) AS
consecutive_days
FROM groups
GROUP BY grp
ORDER BY consecutive_days DESC;
SQL Interview Questions & Solutions Page 14 of 21

65. Write a query to concatenate employee names in each department (string aggregation).

SELECT department_id, STRING_AGG(name, ', ') AS employee_names

FROM employees
GROUP BY department_id;

66. Find employees whose salary is above the average salary of their department but below the
company-wide average.

SELECT *
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id)
AND salary < (SELECT AVG(salary) FROM employees);

67. List the customers who purchased all products in a specific category.

SELECT customer_id
FROM sales
WHERE category_id = 10
GROUP BY customer_id
HAVING COUNT(DISTINCT product_id) = (
SELECT COUNT(DISTINCT product_id) FROM products WHERE category_id = 10
);

68. Retrieve the Nth highest salary from the employees table.

SELECT DISTINCT salary

FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET N-1;

69. Find employees with no corresponding entries in the salary_history table.

SELECT e.*
FROM employees e
LEFT JOIN salary_history sh ON [Link] = sh.employee_id
WHERE sh.employee_id IS NULL;

70. Show the department with the highest number of employees and the count.

SELECT department_id, COUNT(*) AS employee_count

FROM employees
GROUP BY department_id
ORDER BY employee_count DESC
LIMIT 1;
SQL Interview Questions & Solutions Page 15 of 21

71. Write a recursive query to list all ancestors (managers) of a given employee.

WITH RECURSIVE ancestors AS (

SELECT id, name, manager_id
FROM employees
WHERE id = 123
UNION ALL
SELECT [Link], [Link], e.manager_id
FROM employees e
JOIN ancestors a ON [Link] = a.manager_id
)
SELECT * FROM ancestors WHERE id != 123;

72. Calculate the median salary by department using window functions.

SELECT DISTINCT department_id,

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY
department_id) AS median_salary
FROM employees;

73. Find the first purchase date and last purchase date for each customer, including customers who
never purchased anything.

SELECT c.customer_id,
MIN(s.purchase_date) AS first_purchase,
MAX(s.purchase_date) AS last_purchase
FROM customers c
LEFT JOIN sales s ON c.customer_id = s.customer_id
GROUP BY c.customer_id;

74. Find the percentage difference between each month’s total sales and the previous month’s total
sales.

WITH monthly_sales AS (
SELECT DATE_TRUNC('month', sale_date) AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY month
)
SELECT month, total_sales,
(total_sales - LAG(total_sales) OVER (ORDER BY month)) * 100.0 / LAG(total_sales)
OVER (ORDER BY month) AS pct_change
FROM monthly_sales;
SQL Interview Questions & Solutions Page 16 of 21

75. Write a query to find employees who have the longest tenure within their department.

WITH tenure AS (
SELECT *,
RANK() OVER (PARTITION BY department_id ORDER BY hire_date ASC) AS tenure_rank
FROM employees
)
SELECT *
FROM tenure
WHERE tenure_rank = 1;

76. Generate a report that shows sales and sales growth percentage compared to the same month
last year.

WITH monthly_sales AS (
SELECT DATE_TRUNC('month', sale_date) AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY month
)
SELECT [Link], ms1.total_sales,
((ms1.total_sales - ms2.total_sales) * 100.0 / ms2.total_sales) AS growth_pct
FROM monthly_sales ms1
LEFT JOIN monthly_sales ms2 ON [Link] = [Link] + INTERVAL '1 year';

77. Write a query to identify overlapping shifts for employees.

SELECT s1.employee_id, s1.shift_id AS shift1, s2.shift_id AS shift2

FROM shifts s1
JOIN shifts s2 ON s1.employee_id = s2.employee_id AND s1.shift_id <> s2.shift_id
WHERE s1.start_time < s2.end_time AND s1.end_time > s2.start_time;

78. Calculate the total revenue for each customer, and rank them from highest to lowest spender.

SELECT customer_id, SUM(amount) AS total_revenue,

RANK() OVER (ORDER BY SUM(amount) DESC) AS revenue_rank
FROM sales
GROUP BY customer_id;

79. Write a query to find the employee(s) who have never received a promotion.

SELECT e.*
FROM employees e
LEFT JOIN promotions p ON [Link] = p.employee_id
WHERE p.employee_id IS NULL;
SQL Interview Questions & Solutions Page 17 of 21

80. Write a query to find the top 3 products with the highest total sales amount each month.

WITH monthly_product_sales AS (
SELECT product_id, DATE_TRUNC('month', sale_date) AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id, month
),
ranked_sales AS (
SELECT *, RANK() OVER (PARTITION BY month ORDER BY total_sales DESC) AS sales_rank
FROM monthly_product_sales
)
SELECT product_id, month, total_sales
FROM ranked_sales
WHERE sales_rank <= 3
ORDER BY month, sales_rank;

81. Find the customers who placed orders only in the last 30 days.

SELECT DISTINCT customer_id

FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
AND customer_id NOT IN (
SELECT DISTINCT customer_id
FROM orders
WHERE order_date < CURRENT_DATE - INTERVAL '30 days'
);

82. Find products that have never been ordered.

SELECT p.product_id, p.product_name

FROM products p
LEFT JOIN orders o ON p.product_id = o.product_id
WHERE o.order_id IS NULL;

83. Calculate the total sales amount and number of orders per customer in the last year.

SELECT customer_id, COUNT(*) AS total_orders, SUM(amount) AS total_sales

FROM sales
WHERE sale_date >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY customer_id;

84. List the top 5 highest-paid employees per department.

SELECT *
FROM (
SELECT e.*, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
FROM employees e
) sub
WHERE rn <= 5;
SQL Interview Questions & Solutions Page 18 of 21

85. Write a query to identify “gaps and islands” in attendance records (consecutive dates present).

WITH attendance_groups AS (
SELECT employee_id, attendance_date,
attendance_date - INTERVAL ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY
attendance_date) DAY AS grp
FROM attendance
)
SELECT employee_id, MIN(attendance_date) AS start_date, MAX(attendance_date) AS end_date,
COUNT(*) AS consecutive_days
FROM attendance_groups
GROUP BY employee_id, grp
ORDER BY employee_id, start_date;

86. Write a recursive query to list all descendants of a manager in an organizational hierarchy.

WITH RECURSIVE descendants AS (

SELECT id, name, manager_id
FROM employees
WHERE manager_id = 100
UNION ALL
SELECT [Link], [Link], e.manager_id
FROM employees e
INNER JOIN descendants d ON e.manager_id = [Link]
)
SELECT * FROM descendants;

87. Calculate a 3-month moving average of monthly sales per product.

WITH monthly_sales AS (
SELECT product_id, DATE_TRUNC('month', sale_date) AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id, month
)
SELECT product_id, month, total_sales,
AVG(total_sales) OVER (PARTITION BY product_id ORDER BY month ROWS BETWEEN 2
PRECEDING AND CURRENT ROW) AS moving_avg
FROM monthly_sales;

88. Write a query to find employees who have the same hire date as their managers.

SELECT [Link] AS employee_name, [Link] AS manager_name, e.hire_date

FROM employees e
JOIN employees m ON e.manager_id = [Link]
WHERE e.hire_date = m.hire_date;
SQL Interview Questions & Solutions Page 19 of 21

89. Write a query to find products with increasing sales over the last 3 months.

WITH monthly_sales AS (
SELECT product_id, DATE_TRUNC('month', sale_date) AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id, month
),
ranked_sales AS (
SELECT product_id, month, total_sales,
ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY month DESC) AS rn
FROM monthly_sales
)
SELECT ms1.product_id
FROM ranked_sales ms1
JOIN ranked_sales ms2 ON ms1.product_id = ms2.product_id AND [Link] = 1 AND [Link] = 2
JOIN ranked_sales ms3 ON ms1.product_id = ms3.product_id AND [Link] = 3
WHERE ms3.total_sales < ms2.total_sales AND ms2.total_sales < ms1.total_sales;

90. Write a query to get the nth highest salary per department.

SELECT department_id, salary

FROM (
SELECT department_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY
salary DESC) AS rn
FROM employees
) sub
WHERE rn = N;

91. Find employees who have managed more than 3 projects.

SELECT manager_id, COUNT(DISTINCT project_id) AS project_count

FROM projects
GROUP BY manager_id
HAVING COUNT(DISTINCT project_id) > 3;

92. Write a query to calculate the difference in days between each employee's hire date and their
manager’s hire date.

SELECT [Link] AS employee, [Link] AS manager,

DATEDIFF(day, m.hire_date, e.hire_date) AS days_difference
FROM employees e
JOIN employees m ON e.manager_id = [Link];
SQL Interview Questions & Solutions Page 20 of 21

93. Write a query to find the department with the highest average years of experience.

SELECT department_id, AVG(EXTRACT(year FROM CURRENT_DATE - hire_date)) AS

avg_experience_years
FROM employees
GROUP BY department_id
ORDER BY avg_experience_years DESC
LIMIT 1;

94. Identify employees who had overlapping project assignments.

SELECT p1.employee_id, p1.project_id AS project1, p2.project_id AS project2

FROM project_assignments p1
JOIN project_assignments p2 ON p1.employee_id = p2.employee_id AND p1.project_id <>
p2.project_id
WHERE p1.start_date < p2.end_date AND p1.end_date > p2.start_date;

95. Find customers who made purchases in every month of the current year.

WITH customer_months AS (
SELECT customer_id, EXTRACT(MONTH FROM purchase_date) AS month
FROM sales
WHERE EXTRACT(YEAR FROM purchase_date) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY customer_id, EXTRACT(MONTH FROM purchase_date)
)
SELECT customer_id
FROM customer_months
GROUP BY customer_id
HAVING COUNT(DISTINCT month) = 12;

96. List employees who earn more than all their subordinates.

SELECT [Link], [Link], [Link]

FROM employees e
WHERE [Link] > ALL (
SELECT salary FROM employees sub WHERE sub.manager_id = [Link]
);

97. Get the product with the highest sales for each category.

WITH category_sales AS (
SELECT category_id, product_id, SUM(amount) AS total_sales,
RANK() OVER (PARTITION BY category_id ORDER BY SUM(amount) DESC) AS sales_rank
FROM sales
GROUP BY category_id, product_id
)
SELECT category_id, product_id, total_sales
FROM category_sales
WHERE sales_rank = 1;
SQL Interview Questions & Solutions Page 21 of 21

98. Find customers who haven’t ordered in the last 6 months.

SELECT customer_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id
HAVING MAX(o.order_date) < CURRENT_DATE - INTERVAL '6 months' OR MAX(o.order_date) IS
NULL;

99. Find the maximum salary gap between any two employees within the same department.

SELECT department_id, MAX(salary) - MIN(salary) AS salary_gap

FROM employees
GROUP BY department_id;

100. Write a recursive query to compute the total budget under each manager (including
subordinates).

WITH RECURSIVE manager_budget AS (

SELECT id, manager_id, budget
FROM departments
UNION ALL
SELECT [Link], d.manager_id, [Link]
FROM departments d
JOIN manager_budget mb ON d.manager_id = [Link]
)
SELECT manager_id, SUM(budget) AS total_budget
FROM manager_budget
GROUP BY manager_id;