SQL & Database Interview | Data For Dummies

1

Find the second highest salary without using LIMIT, OFFSET, or TOP Code

Input table: Employees

EmpID	Name	Salary
1	Alice	60000
2	Bob	80000
3	Charlie	75000
4	David	80000
5	Eve	90000

Expected output: Second_Highest_Salary = 80000

Try your SQL

2

Given a table of orders, find the running total (cumulative sum) of revenue for each day Code

Input table: Orders

OrderID	OrderDate	Revenue
101	2024-01-01	100
102	2024-01-01	200
103	2024-01-02	150
104	2024-01-03	300
105	2024-01-03	100

In the try-it below (SQLite), use a CTE: first SELECT OrderDate, SUM(Revenue) AS Daily_Revenue ... GROUP BY OrderDate, then SUM(Daily_Revenue) OVER (ORDER BY OrderDate) in the outer query.

Try your SQL

3

Write an SQL query to identify employees who earn more than their managers Code

Input table: Employees

EmpID	Name	Salary	ManagerID
1	Alice	70000	NULL
2	Bob	80000	1
3	Charlie	60000	1
4	David	90000	2
5	Eve	85000	2

Output: Bob 80000, David 90000, Eve 85000.

Try your SQL

4

Find products that were never purchased by any customer Code

Tables: Products (ProductID, ProductName); OrderItems (OrderID, ProductID, Quantity). Sample: Products 1–4 (Shoes, Bag, Watch, Hat); OrderItems has 101→1, 102→3, 103→1.

Output: 2 Bag, 4 Hat.

Try your SQL

5

Find employees who have the same salary as another employee in the same department Code

Input: Employees (EmpID, EmpName, Department, Salary). Goal: list employees whose salary matches someone else’s in the same department.

SELECT e1.EmpID, e1.EmpName, e1.Department, e1.Salary
FROM Employees e1
JOIN Employees e2 ON e1.Department = e2.Department AND e1.Salary = e2.Salary AND e1.EmpID <> e2.EmpID;

6

Find the top N customers by purchase amount; ensure no duplicates for same amount (use DENSE_RANK) Code

Input: Purchases (CustomerID, CustomerName, PurchaseAmount). Goal: top N distinct purchase amounts and corresponding customers. N=2 → Bob 7000, Charlie 7000, David 6500.

WITH RankedPurchases AS (
  SELECT CustomerID, CustomerName, PurchaseAmount,
         DENSE_RANK() OVER (ORDER BY PurchaseAmount DESC) AS Rank
  FROM Purchases
)
SELECT CustomerID, CustomerName, PurchaseAmount
FROM RankedPurchases WHERE Rank <= 2;

7

Retrieve the first order for each customer, handling ties (same earliest date) correctly Code

Use RANK() so ties all get rank 1. ROW_NUMBER() would pick only one row per customer.

WITH RankedOrders AS (
  SELECT *, RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate ASC) AS rnk
  FROM Orders
)
SELECT OrderID, CustomerID, OrderDate, Amount FROM RankedOrders WHERE rnk = 1;

8

Rank orders by value per customer and return only the top 3 orders per customer Code

WITH RankedOrders AS (
  SELECT *, RANK() OVER (PARTITION BY CustomerID ORDER BY OrderValue DESC) AS rnk
  FROM Orders
)
SELECT OrderID, CustomerID, OrderValue FROM RankedOrders WHERE rnk <= 3;

9

Find the median salary using SQL (without built-in median) Code

Middle value (odd count) or average of two middle values (even count).

WITH RankedSalaries AS (
  SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary) AS rn, COUNT(*) OVER () AS total_count
  FROM Employees
),
MedianCalc AS (
  SELECT Salary FROM RankedSalaries
  WHERE rn IN ((total_count + 1) / 2, total_count / 2, total_count / 2 + 1)
)
SELECT ROUND(AVG(Salary), 2) AS MedianSalary FROM MedianCalc;

10

Find the moving average of sales for the last 7 days for each product Code

SELECT ProductID, SaleDate,
  ROUND(AVG(SaleAmount) OVER (
    PARTITION BY ProductID ORDER BY SaleDate
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2) AS MovingAvg7Days
FROM Sales;

11

Rank products by total sales per year (reset ranking each year) Code

WITH YearlySales AS (
  SELECT EXTRACT(YEAR FROM SaleDate) AS SalesYear, ProductID, SUM(SaleAmount) AS TotalSales
  FROM Sales GROUP BY EXTRACT(YEAR FROM SaleDate), ProductID
),
RankedProducts AS (
  SELECT *, RANK() OVER (PARTITION BY SalesYear ORDER BY TotalSales DESC) AS SalesRank
  FROM YearlySales
)
SELECT * FROM RankedProducts ORDER BY SalesYear, SalesRank;

12

Identify consecutive login streaks (at least 3 consecutive days) Code

Use ROW_NUMBER; then LoginDate − rn gives a constant for consecutive days. Group by UserID and that constant, then HAVING COUNT(*) ≥ 3.

WITH RankedLogins AS (
  SELECT UserID, LoginDate, ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY LoginDate) AS rn
  FROM UserLogins
),
Streaks AS (
  SELECT UserID, LoginDate, DATE(LoginDate, '-' || rn || ' days') AS StreakGroup
  FROM RankedLogins
),
GroupedStreaks AS (
  SELECT UserID, MIN(LoginDate) AS StartDate, MAX(LoginDate) AS EndDate, COUNT(*) AS StreakLength
  FROM Streaks GROUP BY UserID, StreakGroup
)
SELECT * FROM GroupedStreaks WHERE StreakLength >= 3;

13

Find customers who made transactions in every month of the year Code

SELECT CustomerID
FROM (
  SELECT CustomerID, EXTRACT(MONTH FROM TransactionDate) AS txn_month
  FROM Transactions
  WHERE EXTRACT(YEAR FROM TransactionDate) = 2024
  GROUP BY CustomerID, EXTRACT(MONTH FROM TransactionDate)
) AS monthly_txns
GROUP BY CustomerID
HAVING COUNT(DISTINCT txn_month) = 12;

14

First and last occurrence of each event per user Code

SELECT UserID, EventName,
  MIN(EventTime) AS FirstOccurrence,
  MAX(EventTime) AS LastOccurrence
FROM Events
GROUP BY UserID, EventName;

15

Users who ordered in two consecutive months but not in the third Logic

Extract year-month, use LAG/LEAD to get prev/next month; filter where prev is consecutive and next has a gap.

WITH MonthData AS (
  SELECT UserID, DATE_TRUNC('month', OrderDate) AS MonthStart
  FROM Orders GROUP BY UserID, DATE_TRUNC('month', OrderDate)
),
WithLagLead AS (
  SELECT UserID, MonthStart,
    LAG(MonthStart) OVER (PARTITION BY UserID ORDER BY MonthStart) AS PrevMonth,
    LEAD(MonthStart) OVER (PARTITION BY UserID ORDER BY MonthStart) AS NextMonth
  FROM MonthData
)
SELECT DISTINCT UserID FROM WithLagLead
WHERE (NextMonth IS NULL OR (NextMonth - MonthStart) > INTERVAL '1 month')
  AND (MonthStart - PrevMonth) = INTERVAL '1 month';

16

Detect duplicate records and delete extra copies, keeping one Code

MySQL/Postgres: DELETE WHERE CustomerID NOT IN (SELECT MIN(CustomerID) FROM Customers GROUP BY Name, Email). With CTE (Postgres):

WITH Duplicates AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Name, Email ORDER BY CustomerID) AS rn
  FROM Customers
)
DELETE FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Duplicates WHERE rn > 1);

17

How would you optimize a slow query with multiple joins and aggregations? Normal

Techniques: (1) Add indexes on JOIN and WHERE columns. (2) Use EXPLAIN ANALYZE to find full scans and missing indexes. (3) Avoid SELECT *; project only needed columns. (4) Use CTEs or temp tables for intermediate results. (5) Join smaller filtered sets first. (6) Partitioning and batch deletes/inserts. (7) Materialized views or summary tables for precomputed aggregations. (8) Prefer window functions over correlated subqueries where applicable.

18

Department with the highest total salary Code

SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees GROUP BY Department
ORDER BY TotalSalary DESC LIMIT 1;

Without LIMIT: use a CTE with RANK() OVER (ORDER BY TotalSalary DESC) and WHERE rnk = 1.

19

Pivot sales: one row per customer, columns Jan, Feb, Mar Code

SELECT CustomerID,
  SUM(CASE WHEN strftime('%m', SaleDate) = '01' THEN SaleAmount ELSE 0 END) AS Jan,
  SUM(CASE WHEN strftime('%m', SaleDate) = '02' THEN SaleAmount ELSE 0 END) AS Feb,
  SUM(CASE WHEN strftime('%m', SaleDate) = '03' THEN SaleAmount ELSE 0 END) AS Mar
FROM Sales GROUP BY CustomerID;

Use MONTH(SaleDate) or EXTRACT(MONTH FROM SaleDate) in MySQL/Postgres.

20

Most frequently purchased product category per user Code

WITH LastYearOrders AS (
  SELECT * FROM Orders WHERE OrderDate >= CURRENT_DATE - INTERVAL '12 months'
),
CategoryCounts AS (
  SELECT UserID, Category, COUNT(*) AS CategoryCount
  FROM LastYearOrders GROUP BY UserID, Category
),
Ranked AS (
  SELECT *, RANK() OVER (PARTITION BY UserID ORDER BY CategoryCount DESC) AS rnk
  FROM CategoryCounts
)
SELECT UserID, Category, CategoryCount FROM Ranked WHERE rnk = 1;

SQL & Database Interview Questions

Find the second highest salary without using LIMIT, OFFSET, or TOP Code

Try your SQL

Given a table of orders, find the running total (cumulative sum) of revenue for each day Code

Try your SQL

Write an SQL query to identify employees who earn more than their managers Code

Try your SQL

Find products that were never purchased by any customer Code

Try your SQL

Find employees who have the same salary as another employee in the same department Code

Find the top N customers by purchase amount; ensure no duplicates for same amount (use DENSE_RANK) Code

Retrieve the first order for each customer, handling ties (same earliest date) correctly Code

Rank orders by value per customer and return only the top 3 orders per customer Code

Find the median salary using SQL (without built-in median) Code

Find the moving average of sales for the last 7 days for each product Code

Rank products by total sales per year (reset ranking each year) Code

Identify consecutive login streaks (at least 3 consecutive days) Code

Find customers who made transactions in every month of the year Code

First and last occurrence of each event per user Code

Users who ordered in two consecutive months but not in the third Logic

Detect duplicate records and delete extra copies, keeping one Code

How would you optimize a slow query with multiple joins and aggregations? Normal

Department with the highest total salary Code

Pivot sales: one row per customer, columns Jan, Feb, Mar Code

Most frequently purchased product category per user Code

🛑 Wait! Are you sure?