300 Real SQL Interview Questions Asked at PwC,

Deloitte, EY, KPMG, Tredence, Persistent Systems and

Accenture & More
This comprehensive collection contains medium to advanced SQL questions (01-300) covering real-world scenarios from top
consulting firms and technology companies. Each question includes complete SQL solutions with detailed syntax and
explanations.

Created by: Narendra Kumar

Find the Second Highest Salary
Find the second highest salary from the Employee table.

SELECT MAX(salary) AS SecondHighestSalary

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

This query uses a subquery to find the maximum salary, then finds the maximum salary that is less than that value.

Narendra Kumar
Find Duplicate Records in a Table
Find duplicate records in a table.

SELECT name, COUNT(*)

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

This query groups by name and uses HAVING to filter groups with more than one occurrence, identifying duplicates.
Retrieve Employees Who Earn More Than Their
Manager
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];

This self-join compares employee salaries with their managers' salaries to find employees earning more.

Narendra Kumar
Count Employees in Each Department
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;

This query groups employees by department and filters departments with more than 5 employees using HAVING.

Narendra Kumar
Find Employees Who Joined in the Last 6 Months
Find employees who joined in the last 6 months.

SELECT *
FROM employees
WHERE join_date >= CURRENT_DATE - INTERVAL '6 months';

This query uses date arithmetic to filter employees based on their join date within the last 6 months.

Narendra Kumar
Get Departments With No Employees
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;

This LEFT JOIN identifies departments that have no matching employee records.

Narendra Kumar
Find the Median Salary
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;

This complex query calculates the median by ordering salaries and selecting the middle value(s).

Narendra Kumar
Running Total of Salaries by Department
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;

This window function calculates a cumulative sum of salaries within each department, ordered by employee ID.

Narendra Kumar
Find the Longest Consecutive Streak of Daily Logins
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;

This query uses a clever technique with ROW_NUMBER to identify consecutive date sequences by creating groups.

Narendra Kumar
Recursive Query for Reporting Chain
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;

This recursive CTE traverses the organizational hierarchy from top to bottom, assigning levels to each employee.

Narendra Kumar
Find Gaps in a Sequence of Numbers
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;

This query identifies missing IDs by checking for the absence of the next sequential ID.

Narendra Kumar
Calculate Cumulative Distribution of Salaries
Calculate cumulative distribution (CDF) of salaries.

SELECT name, salary,

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

The CUME_DIST window function calculates the relative position of each salary in the distribution.

Narendra Kumar
Compare Two Tables and Find Differences
Compare two tables and find rows with differences in any column (all columns).

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;

This FULL OUTER JOIN with IS DISTINCT FROM identifies all differences between two tables, including NULL values.
Rank Employees Based on Salary
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;

The RANK function assigns the same rank to employees with identical salaries, with gaps in subsequent ranks.

Narendra Kumar
Find Customers Who Have Not Made Any Purchase
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;

This LEFT JOIN identifies customers with no corresponding sales records.

Conditional Aggregation by Gender
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;

This query uses CASE statements within COUNT to separately count males and females in each department.

Narendra Kumar
Calculate Salary Difference Using LAG Function
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;

The LAG window function accesses the previous row's salary to calculate the difference.
Identify Overlapping Date Ranges for Bookings
Identify overlapping date ranges for bookings.

SELECT b1.booking_id, b2.booking_id

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;

This self-join identifies bookings with overlapping date ranges by checking if start and end dates intersect.

Narendra Kumar
Find Employees With Salary Greater Than Average
Write a query to 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;

This query uses a subquery to calculate the average salary and filters employees earning above that average.
Aggregate JSON Data for Employee Names
Aggregate JSON data (if supported) 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;

The JSON_AGG function aggregates employee names into a JSON array for each department.

Narendra Kumar
Find Employees With Same Salary as Manager
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];

This self-join compares employee and manager salaries to find exact matches.
Get First and Last Purchase Date for Each Customer
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;

This query uses MIN and MAX aggregate functions to find the earliest and latest purchase dates.

Narendra Kumar
Find Departments With Highest Average Salary
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);

This CTE calculates average salaries per department, then filters for the maximum average.
Count Employees in Each Job Title
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;

This simple GROUP BY query counts employees for each distinct job title.

Narendra Kumar
Find Employees Without Department Assignment
Find employees who don't have a department assigned.

SELECT *
FROM employees
WHERE department_id IS NULL;

This query filters for NULL department_id values to find unassigned employees.
Calculate Difference in Days Between Dates
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) in some systems).

Narendra Kumar
Calculate Moving Average of Salaries
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;

This window function calculates a 3-row moving average of salaries based on hire date order.
Find Most Recent Purchase Per Customer
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;

ROW_NUMBER assigns a rank to each purchase per customer, allowing selection of the most recent.

Narendra Kumar
Detect Hierarchical Depth in Org Chart
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;

This recursive CTE calculates the depth level of each employee in the organizational hierarchy.
Self-Join to Find Employee Pairs in Same Department
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];

This self-join creates unique pairs of employees within the same department using the ID comparison.

Narendra Kumar
Pivot Rows Into Columns
Write a query to pivot rows into columns dynamically (if dynamic pivot is not supported, simulate it for fixed values).

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;

This query uses CASE statements to pivot job titles into separate columns for each department.
Find Customers Who Purchased in Every Category
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);

This query compares the count of distinct categories per customer to the total number of categories.
Identify Employees Without Salary Raise
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';

This query groups salary history by employee and checks if the most recent raise was over a year ago.

Narendra Kumar
Rank Salespeople by Monthly Sales
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;

This query partitions by month and ranks salespeople within each month based on total sales.
Calculate Percentage Change in Sales
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;

This query uses LAG to access the previous month's sales and calculates the percentage change.

Narendra Kumar
Complex Salary Comparison Query
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
);

This query combines multiple conditions using subqueries to filter employees based on company and department salary
metrics.
Retrieve Last 5 Orders for Each Customer
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;

ROW_NUMBER partitioned by customer allows selection of the 5 most recent orders per customer.

Narendra Kumar
Find Employees With No Salary Changes
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;

This LEFT JOIN identifies employees with no salary history records in the specified time period.
Find Department With Lowest Average Salary
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
LIMIT 1;

This query calculates average salaries per department and selects the one with the minimum value.

Narendra Kumar
List Employees With Matching Start and End Letters
List employees whose names start and end with the same letter.

SELECT *
FROM employees
WHERE LEFT(name, 1) = RIGHT(name, 1);

This query uses string functions to compare the first and last characters of employee names.
Detect Circular References in Hierarchy
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);

This recursive query tracks the path of manager relationships to detect circular references.

Narendra Kumar
Running Total of Sales Per Customer
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;

This window function calculates a cumulative sum of sales for each customer over time.
Find Department-Wise Salary Percentile
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;

PERCENTILE_CONT calculates the 90th percentile salary within each department.

Narendra Kumar
Find Employees With Prime Number Salaries
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 syntax fully.
Find Employees Who Worked Multiple Departments
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;

This query counts distinct departments per employee to identify those who changed departments.

Narendra Kumar
Calculate Sales Difference Using Window Functions
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;

LAG accesses the previous row's sales amount within each product partition to calculate differences.
Find Employees at Lowest Hierarchy Level
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]
);

This query uses NOT EXISTS to identify employees who are not managers of anyone.

Narendra Kumar
Find Average Order Value Per Month and Category
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;

This query groups by both month and category to calculate average order values.
Running Count of Employee Hires by Year
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;

This query combines grouping with a window function to calculate both yearly and cumulative hire counts.

Narendra Kumar
Find Second Most Recent Order Date Per Customer
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;

ROW_NUMBER ranks orders per customer, allowing selection of the second most recent.
Find Employees Who Never Made a Sale
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;

This LEFT JOIN identifies employees with no corresponding sales records.

Narendra Kumar
Find Average Tenure by Department
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;

This query calculates the difference between current date and hire date to determine tenure.
Get Employees in Top 10% Salary Range
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;

NTILE divides employees into 10 groups per department, allowing selection of the top decile.

Narendra Kumar
Find Customers With Multiple Purchases Same Day
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;

This query groups by customer and date to identify multiple purchases on the same day.
List All Departments With Employee Counts
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;

LEFT JOIN ensures all departments are included, even those without employees.

Narendra Kumar
Find Duplicate Rows Based on Multiple Columns
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;

This query groups by multiple columns to identify duplicate combinations.

Recursive Query to Calculate Factorial
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;

This recursive CTE multiplies incrementing numbers to calculate factorial.

Narendra Kumar
Calculate Cumulative Percentage of Total Sales
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;

This query uses window functions to calculate both running totals and overall totals for percentage calculation.
Get Employees Reporting to Given Manager
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;

This recursive query traverses the management hierarchy to find all direct and indirect reports.

Narendra Kumar
 Thank You!
Thank you for exploring these 300 SQL interview questions! I hope this resource helps you ace your next interview.

If you found this helpful, please save and share it with others who might benefit.

Stay connected and keep learning!

Connect over LinkedIn

Narendra Kumar | Java Backend Engineer