Tajamul Khan

50 SQL
Interview
Queries

@Tajamulkhann
1. Find duplicate records in a table

SELECT column1, column2, COUNT(*)

FROM your_table
GROUP BY column1, column2
n n
h a
k
HAVING COUNT(*) > 1;

u l
ja m
T a
@

@Tajamulkhann
2. Retrieve the second highest salary
from the Employee table

SELECT MAX(salary) AS
SecondHighestSalary
n n
h a
k
FROM Employee

u l
WHERE salary < (SELECT MAX(salary)

ja m
FROM Employee);

T a
@

@Tajamulkhann
3. Find employees without
department (Left Join usage)

SELECT e.*
FROM Employee e
n n
h a
k
LEFT JOIN Department d

u
ON e.department_id =l
ja m
d.department_id

T a
WHERE d.department_id IS NULL;
@

@Tajamulkhann
4. Calculate the total revenue per
product

SELECT product_id,

n n
a
SUM(quantity * price) AS
total_revenue
l k h
FROM Sales
m u
a ja
GROUP BY product_id;

@ T

@Tajamulkhann
5. Get the top 3 highest-paid
employees.

SELECT TOP 3 *

n n
a
FROM Employee

l
ORDER BY salary DESC;
k h
m u
a ja
@ T

@Tajamulkhann
6. Customers who made purchases
but never returned products.

SELECT DISTINCT c.customer_id

FROM Customers c
n n
h a
k
JOIN Orders o ON c.customer_id =
o.customer_id
u l
j a m
WHERE c.customer_id NOT IN (

T a
SELECT customer_id FROM Returns
@
);

@Tajamulkhann
7. Show the count of orders per
customer.

SELECT customer_id,
COUNT(*) AS order_count
n n
h a
FROM Orders

u l k
m
GROUP BY customer_id;

a j a
@ T

@Tajamulkhann
8. Retrieve all employees who joined
in 2023.

SELECT *
FROM Employee
n n
h a
k
WHERE YEAR(hire_date) = 2023;

u l
ja m
T a
@

@Tajamulkhann
9. Calculate the average order value
per customer.

SELECT customer_id,
AVG(total_amount) AS
n n
h a
k
avg_order_value
FROM Orders
u l
ja m
GROUP BY customer_id;

T a
@

@Tajamulkhann
10. Get the latest order placed by
each customer.

SELECT customer_id,
MAX(order_date) AS
n n
h a
k
latest_order_date
FROM Orders
u l
ja m
GROUP BY customer_id;

T a
@

@Tajamulkhann
11. Find products that were never
sold.

SELECT p.product_id
FROM Products p
n n
LEFT JOIN Sales s
h a
u l k
ON p.product_id = s.product_id

ja m
WHERE s.product_id IS NULL;

T a
@

@Tajamulkhann
12. Identify the most selling product.

SELECT TOP 1 product_id,

SUM(quantity) AS total_qty
n n
h a
k
FROM Sales

u
GROUP BY product_id l
ja m
ORDER BY total_qty DESC;

T a
@

@Tajamulkhann
13. Get the total revenue and the
number of orders per region.

SELECT region,

n
SUM(total_amount) AS total_revenue,n
h a
k
COUNT(*) AS order_count
FROM Orders
u l
ja m
GROUP BY region;

T a
@

@Tajamulkhann
14. Count how many customers
placed more than 5 orders.

SELECT COUNT(*) AS customer_count

FROM (
SELECT customer_id FROM a n n
l k h Orders

u
GROUP BY customer_id
m
j a
HAVING COUNT(*)
a
> 5

T
) AS subquery;
@

@Tajamulkhann
15. Retrieve customers with orders
above the average order value.

SELECT *
FROM Orders
n n
h a
k
WHERE total_amount >

u l
(SELECT AVG(total_amount)

ja
FROM Orders); m
T a
@

@Tajamulkhann
16. Find all employees hired on
weekends.

SELECT *
FROM Employee
n n
h a
k
WHERE DATENAME(WEEKDAY, hire_date)

u l
IN ('Saturday', 'Sunday');

j a m
T a
@

@Tajamulkhann
17. Find all employees hired on
weekends.

SELECT *
FROM Employee
n n
h a
k
WHERE EXTRACT(DOW FROM hire_date)
IN (0, 6);
u l
j a m
T a
@

@Tajamulkhann
18. Get monthly sales revenue and
order count.

SELECT

n
FORMAT(date, 'yyyy-MM') AS month,n
h a
k
SUM(amount) AS total_revenue,

u l
COUNT(order_id) AS order_count

j
FROM Orders
a m
T a
GROUP BY
@
FORMAT(date, 'yyyy-MM');

@Tajamulkhann
19. Rank employees by salary within
each department.

SELECT employee_id, department_id,

n
salary, RANK() OVER (PARTITION BY n
h a
k
department_id

u l
ORDER BY salary DESC) AS salary_rk

ja m
FROM Employee;

T a
@

@Tajamulkhann
20. Find customers who placed
orders every month in 2023.

SELECT customer_id
FROM Orders
n n
h a
GROUP BY customer_id l k
WHERE YEAR(order_date) = 2023

m u
j a
HAVING COUNT(DISTINCT
a
@ T
FORMAT(order_date,'yyyy-MM')) = 12

@Tajamulkhann
21. Find moving average of sales over
the last 3 days.

SELECT order_date,

n
AVG(total_amount) OVER (ORDER BY n
h a
k
order_date ROWS BETWEEN 2 PRECEDING

u l
AND CURRENT ROW) AS moving_avg
FROM Orders; m
a j a
@ T

@Tajamulkhann
22. Identify the first and last order
date for each customer.

SELECT customer_id,

n
MIN(order_date) AS first_order, n
h a
k
MAX(order_date) AS last_order
FROM Orders
u l
j a m
GROUP BY customer_id;

T a
@

@Tajamulkhann
23. Show product sales distribution
(percent of total revenue).

WITH TotalRevenue AS (
SELECT

n n
h a
SUM(quantity * price) AS total FROM Sales)
SELECT s.product_id,

u l k
m
SUM([Link] * [Link]) AS revenue,

ja
SUM([Link] * [Link]) * 100/ [Link]

T a
AS revenue_pct

@
FROM Sales s
CROSS JOIN TotalRevenue t
GROUP BY s.product_id, [Link];

@Tajamulkhann
24. Retrieve customers who made
consecutive purchases (2 Days)

WITH cte AS (
SELECT id, order_date,

n n
h a
LAG(order_date) OVER (PARTITION BY id

l k
ORDER BY order_date) AS prev_order_date

u
m
FROM Orders)

ja
SELECT id, order_date, prev_odate
FROM cte
T a
@
WHERE
DATEDIFF(DAY, prev_odate, order_date) = 1;

@Tajamulkhann
25. Find churned customers
(no orders in the last 6 months).

SELECT customer_id
FROM Orders
n n
h a
k
GROUP BY customer_id
HAVING
u l
ja m
MAX(order_date) <

T a
DATEADD(MONTH,-6,GETDATE());
@

@Tajamulkhann
26. Calculate cumulative revenue by
day.

SELECT order_date,
SUM(total_amount) OVER
n n
h a
k
(ORDER BY order_date) AS

u
cumulative_revenue l
ja
FROM Orders; m
T a
@

@Tajamulkhann
27. Identify top-performing
departments by average salary.

SELECT department_id,
AVG(salary) AS avg_salary
n n
h a
k
FROM Employee

u l
GROUP BY department_id

ja m
ORDER BY avg_salary DESC;

T a
@

@Tajamulkhann
28. Find customers who ordered
more than the average number of
orders per customer.

WITH customer_orders AS (

n n
a
SELECT customer_id, COUNT(*) AS order_count

h
FROM Orders
GROUP BY customer_id)
u l k
ja m
SELECT * FROM customer_orders

T a
WHERE order_count > (SELECT

@
AVG(order_count) FROM customer_orders);

@Tajamulkhann
29. Calculate revenue generated from
new customers (first-time orders).

WITH first_orders AS (
SELECT customer_id, MIN(order_date) AS

n n
first_order_date FROM Orders

h a
GROUP BY customer_id)

u l k
m
SELECT SUM(o.total_amount) AS new_revenue

ja
FROM Orders o JOIN first_orders f

T a
ON o.customer_id = f.customer_id

@
WHERE o.order_date = f.first_order_date;

@Tajamulkhann
30. Find the percentage of
employees in each department.

SELECT
department_id,
n n
h a
k
COUNT(*) AS emp_count,

u l
COUNT(*) * 100.0 / (SELECT

ja m
COUNT(*) FROM Employee)

T a
AS pct FROM Employee
@
GROUP BY department_id;

@Tajamulkhann
31. Retrieve the maximum salary
difference within each department.

SELECT
department_id,
n n
h a
k
MAX(salary) - MIN(salary) AS
salary_diff
u l
ja
FROM Employeem
T a
GROUP BY department_id;
@

@Tajamulkhann
32. Find products that contribute to
80% of the revenue (Pareto Principle).

WITH sales_cte AS (

n
SELECT product_id, SUM(qty * price) AS revenue
FROM Sales GROUP BY product_id),

a n
total_revenue AS (

l k h
u
SELECT SUM(revenue) AS total FROM sales_cte)

m
SELECT s.product_id, [Link],

ja
SUM([Link]) OVER

T a
(ORDER BY [Link] DESC ROWS BETWEEN UNBOUNDED

@
PRECEDING AND CURRENT ROW) AS running_total
FROM sales_cte s, total_revenue t
WHERE SUM([Link]) OVER (ORDER BY [Link] DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW) <= [Link] * 0.8;

@Tajamulkhann
33. Calculate average time between
two purchases for each customer.

WITH cte AS (
SELECT customer_id, order_date,
n n
h
LAG(order_date) OVER (PARTITION BY
a
customer_id

u l k
m
ORDER BY order_date) AS prev_date

a
FROM Orders)
ja
@ T
SELECT customer_id,
AVG(DATEDIFF(DAY, prev_date, order_date))
AS avg_gap_days FROM cte
WHERE prev_date IS NOT NULL
GROUP BY customer_id;

@Tajamulkhann
34. Show last purchase for each
customer along with order amount.

WITH ranked_orders AS
(SELECT customer_id, order_id,
n n
h
total_amount, ROW_NUMBER() OVER
a
u l k
(PARTITION BY customer_id ORDER BY

ja m
order_date DESC) AS rn FROM Orders)

T a
SELECT customer_id, order_id,

@
total_amount
FROM ranked_orders
WHERE rn = 1;

@Tajamulkhann
35. Calculate year-over-year growth
in revenue.

SELECT FORMAT(order_date, 'yyyy') AS year,

SUM(total_amount) AS revenue,

n n
h a
SUM(total_amount) - LAG(SUM(total_amount))

l k
OVER (ORDER BY FORMAT(order_date, 'yyyy'))

u
m
AS yoy_growth

ja
FROM Orders

T a
GROUP BY FORMAT(order_date, 'yyyy');

@Tajamulkhann
36. Detect customers whose
purchase amount is higher than their
historical 90th percentile.

WITH ranked_orders AS (
n n
SELECT customer_id, order_id,
h a
total_amount,

u l k
m
NTILE(10) OVER (PARTITION BY customer_id

a ja
ORDER BY total_amount) AS decile

@ T
FROM Orders)
SELECT customer_id, order_id, total_amount
FROM ranked_orders
WHERE decile = 10;

@Tajamulkhann
37. Find continuous login streaks
(e.g., users who logged in 3 or more
consecutive days).

WITH cte AS (
n n
SELECT user_id, login_date,
h a
u l k
DATEDIFF(DAY, ROW_NUMBER() OVER

m
(PARTITION BY user_id ORDER BY login_date),

a ja
login_date) AS grp FROM Logins)

@ T
SELECT user_id, MIN(login_date) AS
streak_start, MAX(login_date) AS streak_end,
COUNT(*) AS streak_length FROM cte
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

@Tajamulkhann
38. Calculate customer retention by
month (Cohort analysis).

WITH Cohorts AS ( SELECT customer_id,

n
MIN(DATEFROMPARTS(YEAR(order_date),

a n
MONTH(order_date), 1)) AS cohort_month FROM Orders
GROUP BY customer_id),

l k h
u
OrdersByMonth AS (

m
SELECT customer_id, DATEFROMPARTS(YEAR(order_date),

ja
MONTH(order_date), 1)

T a
AS order_month FROM Orders)

@
SELECT c.cohort_month, o.order_month,
COUNT(DISTINCT o.customer_id) AS active_customers
FROM Cohorts c
JOIN OrdersByMonth o ON c.customer_id= o.customer_id
GROUP BY c.cohort_month, o.order_month;

@Tajamulkhann
39. Find products that are always sold
together (Market basket analysis).

SELECT A.product_id AS product_A,

B.product_id AS product_B,

n n
COUNT(*) AS count_together

h a
FROM Order_Details A

u l k
m
JOIN Order_Details B

ja
ON A.order_id = B.order_id
AND
T a
@
A.product_id < B.product_id
GROUP BY A.product_id, B.product_id
HAVING COUNT(*) > 10;

@Tajamulkhann
40. Calculate income inequality
(Gini coefficient).

WITH income_cte AS (
SELECT salary,

n n
a
SUM(salary) OVER (ORDER BY salary) AS
cum_incom,

l k h
COUNT(*) OVER() AS n,

m u
ja
ROW_NUMBER() OVER (ORDER BY salary) AS r

a
FROM Employee)

@ T
SELECT 1 - (2 * SUM((cum_income) / (SUM(salary)
OVER ()) * (1.0 / n)) ) AS gini_coefficient
FROM income_cte;

@Tajamulkhann
41. Compute the day when cumulative
revenue first exceeded 50% of total
revenue (median sales day).

WITH cte AS ( SELECT order_date,

n n
SUM(total_amount) AS daily_rev

h a
l k
FROM Orders GROUP BY order_date),
cum_cte AS (
u
ja m
SELECT order_date, daily_rev, SUM(daily_rev) OVER

T a
(ORDER BY order_date) AS cum_rev, SUM(daily_rev)

@
OVER() AS total_rev FROM cte)
SELECT TOP 1 order_date FROM cum_cte
WHERE cum_rev >= total_rev / 2
ORDER BY order_date;

@Tajamulkhann
42. Find percentiles (25th, 50th, 75th)
of employee salaries.

SELECT

n
(SELECT PERCENTILE_CONT(0.25) WITHIN GROUP

a n
(ORDER BY salary) OVER () FROM Employee) AS p25,

l k h
(SELECT PERCENTILE_CONT(0.50) WITHIN GROUP

u
(ORDER BY salary) OVER () FROM Employee) AS p50,

m
ja
(SELECT PERCENTILE_CONT(0.75) WITHIN GROUP

a
(ORDER BY salary) OVER () FROM Employee) AS p75;

@ T

@Tajamulkhann
43. Retrieve customers with increasing
order amounts over their last 3 orders.

WITH cte AS (

n
SELECT customer_id, order_date, total_amount,

a
LAG(total_amount, 2) OVER (PARTITION BY
n
k h
customer_id ORDER BY order_date) AS amt_t_minus_2,

l
u
LAG(total_amount, 1) OVER (PARTITION BY

m
customer_id ORDER BY order_date) AS amt_t_minus_1

a
FROM Orders)
ja
T
SELECT customer_id, order_date, total_amount

@
FROM cte
WHERE amt_t_minus_2 < amt_t_minus_1
AND amt_t_minus_1 < total_amount;

@Tajamulkhann
44. Calculate conversion funnel
between different stages
→ →
(e.g., visits signups purchases).

SELECT
n n
h a
SUM(CASE WHEN stage = 'visit' THEN 1

u
ELSE 0 END) AS visits,
l k
ja m
SUM(CASE WHEN stage = 'sign_up' THEN 1

a
ELSE 0 END) AS sign_ups,

T
@
SUM(CASE WHEN stage = 'purchase' THEN 1
ELSE 0 END) AS purchases
FROM Funnel;

@Tajamulkhann
45. Find the percentage of total sales
contributed by top 10% of customers.

WITH cte AS (SELECT customer_id,

SUM(total_amount) AS revenue

n n
h a
FROM Orders GROUP BY customer_id),

l k
ranked AS (SELECT *, NTILE(10) OVER

u
m
(ORDER BY revenue DESC) AS decile FROM cte)

ja
SELECT

T a
SUM(revenue) * 100.0 / (SELECT SUM(revenue)

@
FROM cte) AS pct_top_10
FROM ranked
WHERE decile = 1;

@Tajamulkhann
46. Calculate weekly active users

SELECT DATEPART(YEAR, login_date) AS year,

DATEPART(WEEK, login_date) AS week,

n
COUNT(DISTINCT user_id) AS wau
FROM Logins
a n
l k h
GROUP BY DATEPART(YEAR, login_date),

u
DATEPART(WEEK, login_date);

m
a ja
@ T

@Tajamulkhann
47. Find employees with salary higher
than department average.

WITH dept_avg AS (
SELECT department_id, AVG(salary) AS

n n
avg_salary

h a
FROM Employee

u l k
m
GROUP BY department_id)

ja
SELECT e.* FROM Employee e JOIN dept_avg d

T a
ON e.department_id = d.department_id

@
WHERE [Link] > d.avg_salary;

@Tajamulkhann
48. Calculate time between user
signup and their first purchase.

WITH first_purchase AS (
SELECT user_id, MIN(purchase_date) AS

n n
h a
first_purchase_date FROM Purchases
GROUP BY user_id)

u l k
m
SELECT u.user_id,

ja
DATEDIFF(DAY, u.signup_date,

T a
f.first_purchase_date) AS days_to_purchase

@
FROM Users u JOIN first_purchase f
ON u.user_id = f.user_id;

@Tajamulkhann
49. Retrieve the longest gap between
orders for each customer.

WITH cte AS (
SELECT customer_id, order_date,

n n
h
LAG(order_date) OVER (PARTITION BY
a
l k
customer_id ORDER BY order_date) AS

u
m
prev_order_date FROM Orders)

ja
SELECT customer_id, MAX(DATEDIFF(DAY,

T a
prev_order_date, order_date)) AS max_gap

@
FROM cte
WHERE prev_order_date IS NOT NULL
GROUP BY customer_id;

@Tajamulkhann
50. Identify customers with revenue
below the 10th percentile.

WITH cte AS (

n
SELECT customer_id, SUM(total_amount) AS
n
total_revenue

h a
FROM Orders

u l k
m
GROUP BY customer_id)

ja
SELECT customer_id, total_revenue

T a
FROM cte

@
WHERE total_revenue <
(SELECT PERCENTILE_CONT(0.1) WITHIN GROUP
(ORDER BY total_revenue) FROM cte);

@Tajamulkhann
 Found
Helpful ?
Repost

Follow for more!