HackerRank-Style SQL Practice Questions with
Solutions
This document contains SQL practice problems grouped by difficulty (Easy, Medium, Hard), with
solutions provided in two styles: using Subqueries and using Common Table Expressions (CTEs).
Easy
Find the total number of employees in each department.
Subquery:
SELECT dep_id, COUNT(*) AS total_emps FROM Employees GROUP BY dep_id;
CTE:
WITH cte AS (SELECT dep_id FROM Employees)
SELECT dep_id, COUNT(*) FROM cte GROUP BY dep_id;
List the customers who have never placed an order.
Subquery:
SELECT c.cust_id, [Link] FROM Customers c WHERE c.cust_id NOT IN (SELECT cust_id
FROM Orders);
CTE:
WITH cte AS (SELECT DISTINCT cust_id FROM Orders)
SELECT c.cust_id, [Link] FROM Customers c LEFT JOIN cte o ON c.cust_id=o.cust_id
WHERE o.cust_id IS NULL;
Retrieve the top 5 highest salaries from the Employees table.
Subquery:
SELECT * FROM Employees e WHERE 5 > (SELECT COUNT(DISTINCT salary) FROM Employees
WHERE salary > [Link]);
CTE:
WITH cte AS (SELECT emp_id, emp_name, salary, DENSE_RANK() OVER (ORDER BY salary
DESC) AS rnk FROM Employees)
SELECT emp_id, emp_name, salary FROM cte WHERE rnk <= 5;
Count how many students scored more than 80 marks in Math.
Subquery:
SELECT COUNT(*) FROM Students WHERE subject='Math' AND marks>80;
CTE:
WITH cte AS (SELECT * FROM Students WHERE subject='Math')
SELECT COUNT(*) FROM cte WHERE marks>80;
Get distinct list of cities from which customers are located.
Subquery:
SELECT DISTINCT city FROM Customers;
CTE:
WITH cte AS (SELECT city FROM Customers)
SELECT DISTINCT city FROM cte;
Medium
Find the 3rd highest salary in the Employees table.
Subquery:
SELECT MIN(salary) FROM (SELECT DISTINCT TOP 3 salary FROM Employees ORDER BY salary
DESC) t;
CTE:
WITH cte AS (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM
Employees)
SELECT salary FROM cte WHERE rnk=3;
List employees whose salary is above the department average.
Subquery:
SELECT e.* FROM Employees e WHERE salary > (SELECT AVG(salary) FROM Employees WHERE
dep_id=e.dep_id);
CTE:
WITH dept_avg AS (SELECT dep_id, AVG(salary) AS avg_sal FROM Employees GROUP BY
dep_id)
SELECT e.* FROM Employees e JOIN dept_avg d ON e.dep_id=d.dep_id WHERE
[Link]>d.avg_sal;
For each customer, count the number of orders placed in 2023.
Subquery:
SELECT cust_id, COUNT(*) AS order_count FROM Orders WHERE YEAR(order_date)=2023
GROUP BY cust_id;
CTE:
WITH cte AS (SELECT * FROM Orders WHERE YEAR(order_date)=2023)
SELECT cust_id, COUNT(*) FROM cte GROUP BY cust_id;
Find customers who ordered products from more than 3 categories.
Subquery:
SELECT cust_id FROM Orders o JOIN Products p ON o.product_id=p.product_id GROUP BY
cust_id HAVING COUNT(DISTINCT [Link])>3;
CTE:
WITH cte AS (SELECT o.cust_id, [Link] FROM Orders o JOIN Products p ON
o.product_id=p.product_id)
SELECT cust_id FROM cte GROUP BY cust_id HAVING COUNT(DISTINCT category)>3;
Get the monthly sales revenue per region using the Orders table.
Subquery:
SELECT region, YEAR(order_date) AS yr, MONTH(order_date) AS mn, SUM(amount) AS
revenue FROM Orders GROUP BY region,YEAR(order_date),MONTH(order_date);
CTE:
WITH cte AS (SELECT region, order_date, amount FROM Orders)
SELECT region, YEAR(order_date), MONTH(order_date), SUM(amount) FROM cte GROUP BY
region,YEAR(order_date),MONTH(order_date);
Hard
Find the top 3 products by revenue in each region using window functions.
Subquery:
SELECT * FROM (SELECT region, product_id, SUM(amount) AS total_sales, RANK() OVER
(PARTITION BY region ORDER BY SUM(amount) DESC) rnk FROM Sales GROUP BY
region,product_id) t WHERE rnk<=3;
CTE:
WITH cte AS (SELECT region, product_id, SUM(amount) AS total_sales, RANK() OVER
(PARTITION BY region ORDER BY SUM(amount) DESC) rnk FROM Sales GROUP BY
region,product_id)
SELECT * FROM cte WHERE rnk<=3;
Identify players who logged in again the day after their first login.
Subquery:
SELECT player_id FROM Logins l WHERE EXISTS (SELECT 1 FROM Logins l2 WHERE
l.player_id=l2.player_id AND DATEDIFF(day,(SELECT MIN(login_date) FROM Logins WHERE
player_id=l.player_id), l2.login_date)=1);
CTE:
WITH first_login AS (SELECT player_id, MIN(login_date) AS first_day FROM Logins
GROUP BY player_id) SELECT f.player_id FROM first_login f JOIN Logins l ON
f.player_id=l.player_id AND DATEDIFF(day,f.first_day,l.login_date)=1;
From an Employee hierarchy table, retrieve the manager chain for a given
employee.
Subquery:
SELECT e.emp_id, e.emp_name, m.emp_name AS manager FROM Employees e JOIN Employees m
ON e.manager_id=m.emp_id;
CTE:
WITH RecursiveCTE AS (SELECT emp_id, emp_name, manager_id FROM Employees WHERE
emp_id=@emp_id UNION ALL SELECT e.emp_id, e.emp_name, e.manager_id FROM Employees e
JOIN RecursiveCTE r ON e.emp_id=r.manager_id) SELECT * FROM RecursiveCTE;
Pivot sales data so that each column represents a month and each row a
product.
Subquery:
SELECT product_id, SUM(CASE WHEN MONTH(date)=1 THEN amount ELSE 0 END) AS Jan,
SUM(CASE WHEN MONTH(date)=2 THEN amount ELSE 0 END) AS Feb FROM Sales GROUP BY
product_id;
CTE:
WITH cte AS (SELECT product_id, date, amount FROM Sales) SELECT product_id, SUM(CASE
WHEN MONTH(date)=1 THEN amount ELSE 0 END) AS Jan, SUM(CASE WHEN MONTH(date)=2 THEN
amount ELSE 0 END) AS Feb FROM cte GROUP BY product_id;
For each department, find employees who earn more than the average salary
of their department and rank them by salary.
Subquery:
SELECT * FROM (SELECT e.emp_id, e.emp_name, e.dep_id, [Link], RANK() OVER
(PARTITION BY dep_id ORDER BY salary DESC) rnk FROM Employees e WHERE
[Link]>(SELECT AVG(salary) FROM Employees WHERE dep_id=e.dep_id)) t;
CTE:
WITH dept_avg AS (SELECT dep_id, AVG(salary) AS avg_sal FROM Employees GROUP BY
dep_id), cte AS (SELECT e.emp_id, e.emp_name, e.dep_id, [Link], RANK() OVER
(PARTITION BY e.dep_id ORDER BY [Link] DESC) rnk FROM Employees e JOIN dept_avg d
ON e.dep_id=d.dep_id WHERE [Link]>d.avg_sal) SELECT * FROM cte;