SQL
1. What is the difference between WHERE and HAVING in SQL ?
Ans. WHERE filters rows before grouping, and it can’t use aggregate functions. HAVING filters groups after GROUP BY, and it is used with aggregate functions. For example, in IPL data, to find players with more than 2 centuries:
SELECT player, COUNT(*) AS centuries
FROM batting_stats
WHERE runs >= 100
GROUP BY player
HAVING COUNT(*) > 2;Here WHERE first keeps only the 100+ run innings, then GROUP BY groups them per player, and HAVING keeps only the players with more than 2 such innings.
2. What is the difference between INNER JOIN and LEFT JOIN? When would you use each one ?
Ans. This combines rows from two tables in the result of a query
1. INNER JOIN returns only the rows that have a match in both tables. Unmatched rows are dropped.
2. LEFT JOIN returns all rows from the left table, plus the matching rows from the right table. If there is no match, the right-side columns show NULL.
Example: Say an employee has department_id = NULL, or a department has no employees yet.
-- Only employees who have a valid department
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
-- ALL employees, even those with no department (department_name will be NULL)
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;3. Write a query to find the number of employees in each department. The result must also include departments that have zero employees.
employees(emp_id, name, department_id, salary, manager_id, hire_date)
departments(department_id, department_name)
SELECT d.department_id,
d.department_name,
COUNT(e.emp_id) AS number_of_employees
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;4. Write a query to find the second highest salary from the employees table
employees(emp_id, name, department_id, salary, manager_id, hire_date)
Approach 1: LIMIT/OFFSET
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;Approach 2: Subquery
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);Approach 3: Window function
WITH ranked AS (
SELECT salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT DISTINCT salary
FROM ranked
WHERE rnk = 2;5. Write a query to find all duplicate names in the employees table, along with how many times each name appears.
SELECT name, COUNT(*) AS occurrences
FROM employees
GROUP BY name
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;Follow-up the interviewer may ask: “How would you delete the duplicates but keep one copy ?
WITH numbered AS (
SELECT emp_id,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY emp_id) AS rn
FROM employees
)
DELETE FROM employees
WHERE emp_id IN (SELECT emp_id FROM numbered WHERE rn > 1);6. Write a query to find the highest-paid employee in each department. Show the department name, the employee name and the salary
employees(emp_id, name, department_id, salary, manager_id, hire_date)
departments(department_id, department_name)
SELECT d.department_name, e.name, e.salary
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
WHERE e.salary = (
SELECT MAX(salary)
FROM employees
WHERE department_id = e.department_id
);Approach 2: Window function
WITH ranked AS (
SELECT d.department_name,
e.name,
e.salary,
DENSE_RANK() OVER (
PARTITION BY e.department_id
ORDER BY e.salary DESC
) AS rnk
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
)
SELECT department_name, name, salary
FROM ranked
WHERE rnk = 1;7. What is the difference between ROW_NUMBER(), RANK() and DENSE_RANK()? Explain with a small example of salaries.
Ans.All three are window functions that assign a number to rows based on ORDER BY, optionally restarting per PARTITION BY
SELECT salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;
- ROW_NUMBER(): unique number for every row. Ties get different numbers, and which one comes first is arbitrary unless you add a tiebreaker in
ORDER BY. - RANK(): ties get the same rank, and the next rank is skipped (2, 2, then 4).
- DENSE_RANK(): ties get the same rank, and the next rank is not skipped (2, 2, then 3).
8. Write a query to find the top 3 highest-paid employees in each department.
employees(emp_id, name, department_id, salary, manager_id, hire_date)
SELECT d.department_name, e.name, e.salary
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
WHERE 3 > (
SELECT COUNT(DISTINCT e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id
AND e2.salary > e.salary
)
ORDER BY d.department_name, e.salary DESC;
Approach 2: Window function
# Case A: If Ties Allowed Hain (Top 3 Distinct Salaries - DENSE_RANK)
WITH ranked_employees AS (
SELECT
d.department_name,
e.name,
e.salary,
DENSE_RANK() OVER (
PARTITION BY e.department_id
ORDER BY e.salary DESC
) AS rnk
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
)
SELECT department_name, name, salary
FROM ranked_employees
WHERE rnk <= 3
ORDER BY department_name, salary DESC;
==================================================================
# Case B: If exactly 3 rows per department are required (row_number)
WITH ranked_employees AS (
SELECT
d.department_name,
e.name AS employee_name,
e.salary,
ROW_NUMBER() OVER (
PARTITION BY e.department_id
ORDER BY e.salary DESC, e.emp_id ASC
) AS row_num
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
)
SELECT department_name, employee_name, salary
FROM ranked_employees
WHERE row_num <= 3
ORDER BY department_name, salary DESC;
==================================================================
# Case C: If there is a tie, but no more than 3 people qualify (Rank).
WITH ranked_employees AS (
SELECT
d.department_name,
e.name AS employee_name,
e.salary,
RANK() OVER (
PARTITION BY e.department_id
ORDER BY e.salary DESC
) AS rnk
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
)
SELECT department_name, employee_name, salary
FROM ranked_employees
WHERE rnk <= 3
ORDER BY department_name, salary DESC;9. Write a query to find all employees who earn more than their manager. Show the employee name, the employee salary, the manager name and the manager salary.
employees(emp_id, name, department_id, salary, manager_id, hire_date)
SELECT emp.name AS employee_name,
emp.salary AS employee_salary,
mgr.name AS manager_name,
mgr.salary AS manager_salary
FROM employees emp
INNER JOIN employees mgr
ON emp.manager_id = mgr.emp_id
WHERE emp.salary > mgr.salary;10 . Write a query to find employees who are not managers of anyone, meaning no other employee reports to them.
employees(emp_id, name, department_id, salary, manager_id, hire_date)
# Method 1: LEFT JOIN ... WHERE ... IS NULL
SELECT
e.emp_id,
e.name AS employee_name
FROM employees e
LEFT JOIN employees m
ON e.emp_id = m.manager_id
WHERE m.emp_id IS NULL;
========================================================================
# Method 2: NOT EXISTS
SELECT
e.emp_id,
e.name AS employee_name
FROM employees e
WHERE NOT EXISTS (
SELECT 1
FROM employees m
WHERE m.manager_id = e.emp_id
);
==========================================================================
# Method 3: NOT IN
SELECT
e.emp_id,
e.name AS employee_name
FROM employees e
WHERE e.emp_id NOT IN (
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL -- MUST filter out NULLs!
);
I’d use NOT EXISTS. It’s NULL-safe, it short-circuits on the first matching report, and it reads clearly as ‘no one reports to this employee.’ NOT IN is risky because a single NULL in the subquery makes it return nothing, so I’d only use it with an explicit IS NOT NULL filter. LEFT JOIN … IS NULL works too, but it’s less readable, and on most databases it ends up with the same plan as NOT EXISTS.
11 . Write a query to find the running total of order amount for each customer, ordered by order date.
orders(order_id, customer_id, order_date, amount)
SELECT order_id,
customer_id,
order_date,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders
ORDER BY customer_id, order_date, order_id;12 . Write a query to compare each order’s amount with the customer’s previous order amount, and show the difference. The first order of each customer should show NULL for the previous amount.
orders(order_id, customer_id, order_date, amount)
WITH t AS (
SELECT order_id, customer_id, order_date, amount,
LAG(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date, order_id
) AS previous_amount
FROM orders
)
SELECT *, amount - previous_amount AS difference
FROM t
ORDER BY customer_id, order_date, order_id;
==================================================
# What if you want 0 instead of NULL for the first order?
1. WITH t AS (
SELECT order_id, customer_id, order_date, amount,
LAG(amount,1,0) OVER (
PARTITION BY customer_id
ORDER BY order_date, order_id
) AS previous_amount
FROM orders
)
SELECT *, amount - previous_amount AS difference
FROM t
ORDER BY customer_id, order_date, order_id;
2. SELECT order_id,
customer_id,
order_date,
amount,
COALESCE(LAG(amount) OVER w, 0) AS previous_amount,
(amount - COALESCE(LAG(amount) OVER w, 0)) AS difference
FROM orders
WINDOW w AS (
PARTITION BY customer_id
ORDER BY order_date, order_id
)
ORDER BY customer_id, order_date, order_id;13 . Write a query to find customers who placed orders on at least 3 consecutive days.
orders(order_id, customer_id, order_date, amount)
WITH distinct_orders AS (
SELECT DISTINCT customer_id, DATE(order_date) AS order_date
FROM orders
),
checked_consecutive AS (
SELECT customer_id,
order_date,
LEAD(order_date, 1) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_date_1,
LEAD(order_date, 2) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_date_2
FROM distinct_orders
)
SELECT DISTINCT customer_id
FROM checked_consecutive
WHERE DATEDIFF(next_date_1, order_date) = 1
AND DATEDIFF(next_date_2, next_date_1) = 1;
WITH distinct_orders AS (
SELECT DISTINCT customer_id, DATE(order_date) AS order_date
FROM orders
),
grouped_dates AS (
SELECT customer_id,
order_date,
DATE_SUB(
order_date,
INTERVAL ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY order_date
) DAY
) AS date_group
FROM distinct_orders
)
SELECT DISTINCT customer_id
FROM grouped_dates
GROUP BY customer_id, date_group
HAVING COUNT(*) >= 3;14 . What is the difference between a CTE and a subquery? When would you use a CTE, and what is a recursive CTE?
Ans. In SQL, both CTEs (Common Table Expressions) and subqueries are temporary result sets used to break down complex queries.
Get Javed Khan’s stories in your inbox
Join Medium for free to get updates from this writer.
A subquery is a query nested inside another query, and a CTE is a named result set defined with WITH before the main query. They usually give the same result and similar performance, but a CTE is more readable, can be referenced multiple times, and can be recursive.
Difference Between CTE and Subquery
Example: Let’s assume we need data for only those employees whose salary is higher than the company’s average salary.
Using Subquery:
SELECT employee_id, emp_name, salary
FROM employees
WHERE salary > (
-- Nested Subquery
SELECT AVG(salary)
FROM employees
);Using CTE:
-- CTE
WITH avg_salary_cte AS (
SELECT AVG(salary) AS avg_sal
FROM employees
)
SELECT e.employee_id, e.emp_name, e.salary
FROM employees e, avg_salary_cte a
WHERE e.salary > a.avg_sal;Recursive CTE : A recursive CTE is a special type of CTE that repeatedly calls (references) itself until a specific condition (stop condition) is met.
It is used to generate series of numbers or dates, or to handle hierarchical data (such as a manager-employee tree).
A recursive CTE has two parts:
1. Anchor member: The first row/base value that starts the loop.
2. Recursive member: The query following the UNION ALL operator that calls itself to generate the next row.
Example: Generate Number From 1 To 5
WITH RECURSIVE NumberSeries AS (
-- 1. Anchor Member (Starting Point)
SELECT 1 AS num
UNION ALL
-- 2. Recursive Member (Loop)
SELECT num + 1
FROM NumberSeries
WHERE num < 5 -- Stop Condition (It will run as long as num < 5
)
SELECT num FROM NumberSeries;15 . Write a query to find customers who have never placed an order ?
Ans. customers(customer_id, customer_name, city)
orders(order_id, customer_id, order_date, amount)
SELECT c.customer_id, c.customer_name, c.city
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;SELECT customer_id,
customer_name,
city
FROM customers
WHERE customer_id NOT IN ( -- (101, 102);
SELECT customer_id
FROM orders
WHERE customer_id IS NOT NULL
);SELECT c.customer_id,
c.customer_name,
c.city
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);15 . Write a query to find the total sales for each month, and also show the percentage each month contributes to the total sales of the year.
orders(order_id, customer_id, order_date, amount)
WITH monthly_sales AS (
SELECT
DATE_FORMAT(order_date, '%Y-%m') AS sale_month,
SUM(amount) AS month_sales
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT
sale_month,
month_sales,
ROUND((month_sales / SUM(month_sales) OVER()) * 100, 2) AS pct_contribution
FROM monthly_sales
ORDER BY sale_month;SELECT
DATE_FORMAT(order_date, '%Y-%m') AS sale_month,
SUM(amount) AS month_sales,
ROUND((SUM(amount) / SUM(SUM(amount)) OVER()) * 100, 2) AS pct_contribution
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY sale_month;16 . What percentage of the total customers churned, and what percentage were retained ?
Ans. 1. demographic(CustomerId, Gender, Age, Salary, Geopgraphy, Churned)
2. account(AcountId,CustomerId, Tenure, Balance, NumOfProduct, HasCreditCard,IsActiveMember)
SELECT Gender, SUM(Churned) AS total_group_churned,
ROUND((SUM(Churned) / SUM(SUM(Churned)) OVER())*100,2) AS churned_percentag
FROM demographic
GROUP BY Gender;
SELECT
Gender,
COUNT(Churned) AS Total_Customers,
SUM(Churned) AS Total_Churned,
ROUND((SUM(Churned) / SUM(COUNT(Churned)) OVER()) * 100, 2) AS Churned_Percentage,
ROUND(((COUNT(Churned) - SUM(Churned)) / SUM(COUNT(Churned)) OVER()) * 100, 2) AS Retained_Percentage
FROM demographic
GROUP BY Gender;
