1.
Find the Second Highest Salary Without Using LIMIT or TOP
You have an Employees table:
id | name | salary
---+-------+--------
1 | Alice | 5000
2 | Bob | 6000
3 | Carol | 7000
4 | Dave | 6000
Question: Write an SQL query to find the second highest salary without using LIMIT, TOP, or
OFFSET.
SELECT MAX(salary) AS second_highest_salary
FROM Employees
WHERE salary < (SELECT MAX(salary) FROM Employees);
---
2. Find Employees Who Earn More Than Their Manager
You have an Employees table with a manager_id column:
id | name | salary | manager_id
---+-------+--------+-----------
1 | Alice | 5000 | NULL
2 | Bob | 6000 | 1
3 | Carol | 7000 | 1
4 | Dave | 5500 | 2
Question: Find employees who earn more than their manager.
SELECT [Link], [Link]
FROM Employees e1
JOIN Employees e2 ON e1.manager_id = [Link]
WHERE [Link] > [Link];
---
3. Find Duplicate Emails in a Table
You have a Users table:
id | email
---+----------------
1 | a@[Link]
2 | b@[Link]
3 | a@[Link]
4 | c@[Link]
Question: Write an SQL query to find duplicate emails.
SELECT email
FROM Users
GROUP BY email
HAVING COUNT(*) > 1;
---
4. Find the Department with the Highest Average Salary
You have Employees and Departments tables:
Employees:
id | name | salary | department_id
---+-------+--------+--------------
1 | Alice | 5000 | 1
2 | Bob | 6000 | 2
3 | Carol | 7000 | 2
4 | Dave | 5500 | 1
Departments:
id | department_name
---+----------------
1 | HR
2 | Engineering
Question: Find the department with the highest average salary.
SELECT d.department_name, AVG([Link]) AS avg_salary
FROM Employees e
JOIN Departments d ON e.department_id = [Link]
GROUP BY d.department_name
ORDER BY avg_salary DESC
LIMIT 1;
---
5. Find Consecutive Records With the Same Value
You have a Logs table:
id | num
---+----
1 |1
2 |1
3 |2
4 |1
5 |1
6 |1
7 |3
Question: Find numbers that appear at least three times consecutively.
SELECT DISTINCT [Link]
FROM Logs l1, Logs l2, Logs l3
WHERE [Link] = [Link] AND [Link] = [Link]
AND [Link] = [Link] - 1 AND [Link] = [Link] - 1;
1. Find the Third Highest Salary
You have an Employees table:
id | name | salary
---+-------+--------
1 | Alice | 5000
2 | Bob | 6000
3 | Carol | 7000
4 | Dave | 6000
Question: Find the third highest salary using a subquery.
SELECT DISTINCT salary
FROM Employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;
---
2. Find Employees Who Don’t Have a Manager
SELECT name
FROM Employees
WHERE manager_id IS NULL;
---
3. Get the First Purchase of Each Customer
You have a Purchases table:
id | customer_id | purchase_date | amount
---+------------+--------------+-------
1 | 101 | 2024-01-01 | 100
2 | 102 | 2024-01-02 | 200
3 | 101 | 2024-01-05 | 150
4 | 103 | 2024-01-03 | 300
Question: Get the first purchase of each customer.
SELECT *
FROM Purchases p1
WHERE purchase_date = (
SELECT MIN(purchase_date) FROM Purchases p2
WHERE p1.customer_id = p2.customer_id
);
---
4. Find All Employees in the Same Department as ‘Alice’
SELECT name
FROM Employees
WHERE department_id = (
SELECT department_id FROM Employees WHERE name = 'Alice'
);
---
5. Find Customers Who Made More Than One Purchase
SELECT customer_id
FROM Purchases
GROUP BY customer_id
HAVING COUNT(*) > 1;
---
6. Find the Median Salary in a Table
SELECT AVG(salary) AS median_salary
FROM (
SELECT salary FROM Employees
ORDER BY salary
LIMIT 2 OFFSET (SELECT COUNT(*) FROM Employees) / 2 - 1
) sub;
---
7. Find Employees Hired in the Last 30 Days
SELECT name
FROM Employees
WHERE hire_date >= NOW() - INTERVAL 30 DAY;
---
8. Find Customers Who Have Never Made a Purchase
You have a Customers and Purchases table.
SELECT [Link]
FROM Customers c
LEFT JOIN Purchases p ON [Link] = p.customer_id
WHERE [Link] IS NULL;
---
9. Find Consecutive Duplicates in a Table
SELECT DISTINCT [Link]
FROM Logs l1, Logs l2
WHERE [Link] = [Link] AND [Link] = [Link] - 1;
---
10. Find the Most Frequent Product Sold
SELECT product_id, COUNT(*) AS freq
FROM Purchases
GROUP BY product_id
ORDER BY freq DESC
LIMIT 1;
Find Employees Who Have the Same Salary as Someone Else in the Same Department
You have an Employees table:
id | name | salary | department_id
---+-------+--------+--------------
1 | Alice | 5000 | 1
2 | Bob | 6000 | 2
3 | Carol | 7000 | 2
4 | Dave | 6000 | 2
5 | Eve | 5000 | 1
6 | Frank | 8000 | 3
Question: Find employees who have at least one other employee in the same department with
the same salary.
SELECT [Link], [Link], e1.department_id
FROM Employees e1
JOIN Employees e2
ON e1.department_id = e2.department_id
AND [Link] = [Link]
AND [Link] <> [Link];
Explanation:
The query self-joins the Employees table (e1 and e2).
It ensures that the employees are in the same department (e1.department_id =
e2.department_id).
It filters for employees with the same salary ([Link] = [Link]).
It avoids matching the same employee by checking [Link] <> [Link].
Expected Output:
name | salary | department_id
------+--------+--------------
Alice | 5000 | 1
Eve | 5000 | 1
Bob | 6000 | 2
Dave | 6000 | 2
Both Alice & Eve and Bob & Dave have matching salaries in the same department.
This type of self-join is useful for finding duplicates within groups (departments, cities, etc.).
Let me know if you need more self-join challenges!
Here’s another high-complexity SQL question that tests knowledge of GROUP BY, HAVING,
COUNT, STRING_AGG, CASE, and WINDOW FUNCTIONS.
---
Find Customers Who Ordered More Than One Unique Product and List Their Products in a
Single Row
You have an Orders table:
id | customer_id | product | order_date
---+------------+---------+------------
1 | 101 |A | 2024-03-01
2 | 101 |B | 2024-03-02
3 | 102 |A | 2024-03-01
4 | 103 |C | 2024-03-01
5 | 101 |A | 2024-03-03
6 | 102 |B | 2024-03-04
7 | 103 |C | 2024-03-05
8 | 102 |C | 2024-03-06
9 | 103 |D | 2024-03-07
---
Question:
1. Find customers who have ordered more than one unique product.
2. List all the unique products they have ordered as a comma-separated string.
3. Add a customer category column:
"Frequent Buyer" if they ordered more than 3 times.
"Casual Buyer" otherwise.
---
Query:
WITH CustomerOrders AS (
SELECT customer_id,
product,
COUNT(*) OVER (PARTITION BY customer_id) AS total_orders
FROM Orders
GROUP BY customer_id, product
)
SELECT customer_id,
STRING_AGG(product, ', ') AS ordered_products,
CASE
WHEN MAX(total_orders) > 3 THEN 'Frequent Buyer'
ELSE 'Casual Buyer'
END AS customer_category
FROM CustomerOrders
GROUP BY customer_id
HAVING COUNT(DISTINCT product) > 1;
---
Explanation:
GROUP BY customer_id, product: Gets unique products per customer.
COUNT(*) OVER (PARTITION BY customer_id): Counts total orders for each customer.
STRING_AGG(product, ', '): Aggregates unique products into a comma-separated list.
HAVING COUNT(DISTINCT product) > 1: Filters customers with more than one unique product.
CASE: Categorizes Frequent Buyers (more than 3 total orders).
---
Expected Output:
customer_id | ordered_products | customer_category
------------+-----------------+-----------------
101 | A, B | Frequent Buyer
102 | A, B, C | Frequent Buyer
---
Functions Tested:
✅ GROUP BY – Aggregation of customer orders
✅ HAVING COUNT(DISTINCT ...) – Filtering based on unique product count
✅ STRING_AGG(...) – Concatenating product names
✅ CASE – Applying conditional logic
✅ WINDOW FUNCTION (COUNT() OVER ...) – Counting total orders
This query combines multiple advanced SQL concepts in a real-world scenario like customer
segmentation.
Sure! Here are 5 tricky SQL questions that will test your problem-solving skills with joins,
window functions, subqueries, and conditional logic.
1. Find Consecutive Login Streaks for Users
You have a Logins table:
user_id | login_date
--------+------------
1 | 2024-03-01
1 | 2024-03-02
1 | 2024-03-04
2 | 2024-03-01
2 | 2024-03-02
2 | 2024-03-03
2 | 2024-03-05
Question: Find users who logged in for at least 3 consecutive days.
Query:
WITH LoginStreak AS (
SELECT user_id, login_date,
login_date - INTERVAL '1 day' *
RANK() OVER (PARTITION BY user_id ORDER BY login_date) AS streak_group
FROM Logins
)
SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date
FROM LoginStreak
GROUP BY user_id, streak_group
HAVING COUNT(*) >= 3;
Concepts Tested: WINDOW FUNCTIONS, RANK(), DATE MANIPULATION
2. Find Employees Who Earn More Than Their Manager
You have an Employees table:
id | name | salary | manager_id
---+------+--------+-----------
1 | Alice | 8000 | NULL
2 | Bob | 5000 | 1
3 | Carol | 9000 | 1
4 | Dave | 6000 | 2
5 | Eve | 7000 | 2
Question: Find employees who earn more than their direct manager.
Query:
SELECT [Link] AS employee, [Link], [Link] AS manager, [Link] AS
manager_salary
FROM Employees e1
JOIN Employees e2
ON e1.manager_id = [Link]
WHERE [Link] > [Link];
Concepts Tested: SELF JOIN, COMPARISON OPERATORS
3. Find the Running Sum of Sales Per Month
You have a Sales table:
id | sale_date | amount
---+-----------+------
1 | 2024-01-05 | 100
2 | 2024-01-12 | 200
3 | 2024-02-03 | 150
4 | 2024-02-20 | 300
5 | 2024-03-10 | 250
Question: Calculate the running total of sales per month.
Query:
SELECT DATE_TRUNC('month', sale_date) AS sale_month,
amount,
SUM(amount) OVER (PARTITION BY DATE_TRUNC('month', sale_date) ORDER BY
sale_date) AS running_total
FROM Sales;
Concepts Tested: WINDOW FUNCTIONS, DATE_TRUNC(), SUM() OVER (...)
4. Find the Nth Highest Salary Without Using LIMIT or OFFSET
You have an Employees table:
id | name | salary
---+------+--------
1 | Alice | 5000
2 | Bob | 6000
3 | Carol | 7000
4 | Dave | 6000
5 | Eve | 8000
Question: Find the 3rd highest salary without using LIMIT or OFFSET.
Query:
SELECT DISTINCT salary
FROM Employees e1
WHERE 3 = (
SELECT COUNT(DISTINCT salary)
FROM Employees e2
WHERE [Link] >= [Link]
);
Concepts Tested: SUBQUERIES, COUNT(), DISTINCT
5. Find Users Who Have Purchased All Products
You have Users and Orders tables:
Users:
id | name
---+------
1 | Alice
2 | Bob
3 | Carol
Orders:
user_id | product
--------+--------
1 |A
1 |B
2 |A
2 |B
2 |C
3 |A
3 |C
Question: Find users who have purchased all available products.
Query:
SELECT user_id
FROM Orders
GROUP BY user_id
HAVING COUNT(DISTINCT product) = (SELECT COUNT(DISTINCT product) FROM Orders);
Concepts Tested: HAVING, COUNT(), SUBQUERIES