SQL Interview Questions & Answers (Practice Set)
Q1. Find the second highest salary from an Employees table.
We can use either a window function or ORDER BY with LIMIT/OFFSET. Using DENSE_RANK
handles ties correctly (if two people share highest salary, next distinct salary is rank 2).
-- Using window function (ANSI SQL)
SELECT salary
FROM (
SELECT salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) t
WHERE rnk = 2;
-- Using ORDER BY + OFFSET (works in MySQL/Postgres)
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
Q2. Get the nth highest salary (generic solution).
We extend the idea from the second■highest salary using DENSE_RANK and filter on rank = n.
-- :n is a placeholder for the desired rank
SELECT salary
FROM (
SELECT salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) t
WHERE rnk = :n;
Q3. List all customers who have never placed an order.
Use an anti■join pattern with LEFT JOIN and filter rows where the order side is NULL.
SELECT c.customer_id,
c.customer_name
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
Q4. Find total revenue per day.
Group orders by order_date and sum total_amount.
SELECT order_date,
SUM(total_amount) AS daily_revenue
FROM orders
GROUP BY order_date
ORDER BY order_date;
Q5. Find the top 3 customers by total spend.
First aggregate spend per customer, then rank using a window function and filter for rank ≤ 3.
WITH customer_spend AS (
SELECT customer_id,
SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
)
SELECT customer_id,
total_spent
FROM (
SELECT customer_id,
total_spent,
DENSE_RANK() OVER (ORDER BY total_spent DESC) AS rnk
FROM customer_spend
) t
WHERE rnk <= 3;
Q6. Get the running (cumulative) revenue by date.
We first compute daily revenue, then apply a window SUM ordered by date to get the running total.
SELECT order_date,
SUM(total_amount) AS daily_revenue,
SUM(SUM(total_amount)) OVER (
ORDER BY order_date
) AS running_revenue
FROM orders
GROUP BY order_date
ORDER BY order_date;
Q7. For each customer, show the current and previous order amount.
Use LAG() partitioned by customer to access the prior row's total_amount based on order_date.
SELECT customer_id,
order_id,
order_date,
total_amount,
LAG(total_amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS prev_order_amount
FROM orders
ORDER BY customer_id, order_date;
Q8. Count number of orders per customer and filter those with at least 3 orders.
Use GROUP BY on customer_id with HAVING to filter on the aggregated count.
SELECT customer_id,
COUNT(*) AS num_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 3;
Q9. Find products that have never been ordered.
LEFT JOIN products to order_items and filter where order_items.product_id is NULL.
SELECT p.product_id,
p.product_name
FROM products p
LEFT JOIN order_items oi
ON p.product_id = oi.product_id
WHERE oi.product_id IS NULL;
Q10. For each department, find the highest paid employee(s).
We can use a window function RANK() partitioned by dept_id and filter rank = 1 to keep ties.
SELECT emp_id,
emp_name,
dept_id,
salary
FROM (
SELECT e.*,
RANK() OVER (
PARTITION BY dept_id
ORDER BY salary DESC
) AS rnk
FROM employees e
) t
WHERE rnk = 1;
Q11. List employees who earn more than the average salary of their department.
First compute average salary per department, then join back and compare each employee's salary.
WITH dept_avg AS (
SELECT dept_id,
AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
)
SELECT e.emp_id,
e.emp_name,
e.dept_id,
[Link],
d.avg_salary
FROM employees e
JOIN dept_avg d
ON e.dept_id = d.dept_id
WHERE [Link] > d.avg_salary;
Q12. Get monthly order counts for the last year.
Extract year and month from order_date, group by them, and count orders.
SELECT EXTRACT(YEAR FROM order_date) AS order_year,
EXTRACT(MONTH FROM order_date) AS order_month,
COUNT(*) AS num_orders
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date)
ORDER BY order_year, order_month;
Q13. Compute average order value (AOV) per customer.
For each customer, take AVG(total_amount) over their orders.
SELECT c.customer_id,
c.customer_name,
AVG(o.total_amount) AS avg_order_value
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;
Q14. Return all departments along with their total headcount.
Use GROUP BY dept_id and COUNT(*) on employees.
SELECT d.dept_id,
d.dept_name,
COUNT(e.emp_id) AS num_employees
FROM departments d
LEFT JOIN employees e
ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY d.dept_id;
Q15. Get a list of duplicate emails from a Users table.
Group by email and use HAVING COUNT(*) > 1 to detect duplicates.
SELECT email,
COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
Q16. Delete duplicate rows based on email, keeping the smallest user_id.
Use a subquery to identify rows to delete where user_id is not the minimum for that email.
DELETE FROM users
WHERE user_id NOT IN (
SELECT MIN(user_id)
FROM users
GROUP BY email
);
Q17. Explain the difference between WHERE and HAVING with an example.
WHERE filters individual rows before grouping; HAVING filters groups after aggregation.
-- Example: customers with at least 3 orders over $100 total
-- Use WHERE to restrict rows that enter the aggregation
SELECT customer_id,
COUNT(*) AS num_orders,
SUM(total_amount) AS total_spent
FROM orders
WHERE total_amount >= 100
GROUP BY customer_id
HAVING COUNT(*) >= 3;
Q18. Create an index to speed up lookups on orders by customer_id and order_date.
Composite indexes help queries that filter or sort by multiple columns in that left■to■right order.
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);
Q19. Explain INNER JOIN vs LEFT JOIN with a query example.
INNER JOIN keeps only matching rows, while LEFT JOIN keeps all rows from the left table and NULLs
when no match.
-- INNER JOIN: only customers who have at least one order
SELECT c.customer_id,
c.customer_name,
o.order_id
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id;
-- LEFT JOIN: all customers, with NULL order_id if no orders
SELECT c.customer_id,
c.customer_name,
o.order_id
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id;
Q20. Find the percentage contribution of each product to total revenue.
First compute total revenue; then for each product compute its revenue and divide by the grand total.
WITH product_rev AS (
SELECT p.product_id,
p.product_name,
SUM([Link] * [Link]) AS revenue
FROM order_items oi
JOIN products p
ON oi.product_id = p.product_id
GROUP BY p.product_id, p.product_name
),
total_rev AS (
SELECT SUM(revenue) AS total_revenue
FROM product_rev
)
SELECT pr.product_id,
pr.product_name,
[Link],
ROUND(100.0 * [Link] / tr.total_revenue, 2) AS revenue_pct
FROM product_rev pr
CROSS JOIN total_rev tr
ORDER BY [Link] DESC;
Q21. Get a 7-day moving average of daily revenue.
Aggregate to daily level, then use a window AVG over a 6■day preceding window plus current row.
WITH daily AS (
SELECT order_date,
SUM(total_amount) AS daily_revenue
FROM orders
GROUP BY order_date
)
SELECT order_date,
daily_revenue,
AVG(daily_revenue) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS mov_avg_7d
FROM daily
ORDER BY order_date;
Q22. For each employee, show how their salary compares to the department average
(difference).
Use a window AVG partitioned by dept_id and subtract from salary.
SELECT emp_id,
emp_name,
dept_id,
salary,
AVG(salary) OVER (
PARTITION BY dept_id
) AS dept_avg_salary,
salary - AVG(salary) OVER (
PARTITION BY dept_id
) AS diff_from_avg
FROM employees;
Q23. Pivot: Count orders by status in separate columns (status_new, status_shipped,
status_cancelled).
One way is to use conditional aggregation.
SELECT customer_id,
SUM(CASE WHEN status = 'NEW' THEN 1 ELSE 0 END) AS status_new,
SUM(CASE WHEN status = 'SHIPPED' THEN 1 ELSE 0 END) AS status_shipped,
SUM(CASE WHEN status = 'CANCELLED' THEN 1 ELSE 0 END) AS status_cancelled
FROM orders
GROUP BY customer_id;
Q24. Retrieve the most recent order per customer.
Use ROW_NUMBER partitioned by customer ordered by order_date DESC and filter row_number = 1.
SELECT customer_id,
order_id,
order_date,
total_amount
FROM (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC, order_id DESC
) AS rn
FROM orders o
) t
WHERE rn = 1;
Q25. Explain normalization up to 3NF with a simple example.
We store data in separate tables to reduce redundancy.
1NF: atomic columns, no repeating groups.
2NF: no partial dependency on part of a composite key.
3NF: no transitive dependency (non■key depending on another non■key).
-- Example: separate customers and orders
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
city VARCHAR(100)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT REFERENCES customers(customer_id),
order_date DATE,
total_amount DECIMAL(10,2)
);
Q26. Demonstrate a simple transaction that transfers money between two accounts.
We must update both accounts in a single transaction so either both succeed or both rollback.
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;
-- If all good
COMMIT;
-- If something fails
-- ROLLBACK;
Q27. Explain ACID properties in the context of SQL databases.
Atomicity: all operations in a transaction succeed or none.
Consistency: transaction takes DB from one valid state to another.
Isolation: concurrent transactions appear as if run sequentially.
Durability: once committed, changes survive crashes.
Q28. Write a query to find users who logged in on consecutive days.
We can use LAG() on the login_date and check if the difference is exactly 1 day.
SELECT user_id,
login_date,
LAG(login_date) OVER (
PARTITION BY user_id
ORDER BY login_date
) AS prev_login_date
FROM logins;
-- Wrap it to keep only strictly consecutive pairs
SELECT *
FROM (
SELECT user_id,
login_date,
LAG(login_date) OVER (
PARTITION BY user_id
ORDER BY login_date
) AS prev_login_date
FROM logins
) t
WHERE login_date = prev_login_date + INTERVAL '1 day';
Q29. Find the median salary from the Employees table.
We can use window functions to simulate MEDIAN: compute the row_number from both ends and keep
the middle row(s).
WITH ordered AS (
SELECT salary,
ROW_NUMBER() OVER (ORDER BY salary) AS rn_asc,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn_desc
FROM employees
)
SELECT AVG(salary) AS median_salary
FROM ordered
WHERE rn_asc = rn_desc
OR rn_asc + 1 = rn_desc
OR rn_desc + 1 = rn_asc;
Q30. Get the ratio of each customer's spend to the total spend across all customers.
Use a window SUM over all rows and divide each customer's spend by that total.
WITH customer_spend AS (
SELECT customer_id,
SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
)
SELECT customer_id,
total_spent,
total_spent / SUM(total_spent) OVER () AS spend_ratio
FROM customer_spend
ORDER BY total_spent DESC;