🚀 Real-World SQL Scenario Based Interview Questions with Answers

📌 Question 1: Find Customers Who Purchased in Consecutive Months

Table: orders customer_id, order_date

Requirement: Identify customers who placed orders in consecutive months.

WITH monthly_orders AS (
SELECT DISTINCT
customer_id,
DATE_TRUNC('month', order_date) AS order_month
FROM orders
),
consecutive_orders AS (
SELECT
customer_id,
order_month,
LAG(order_month) OVER (
PARTITION BY customer_id
ORDER BY order_month
) AS prev_month
FROM monthly_orders
)
SELECT customer_id
FROM consecutive_orders
WHERE order_month = prev_month + INTERVAL '1 month';

📌 Question 2: Find the Top 3 Customers by Revenue Each Month
Table: orders customer_id, amount, order_date

WITH customer_revenue AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
customer_id,
SUM(amount) AS revenue
FROM orders
GROUP BY 1, 2
)

SELECT *
FROM (
SELECT *,
DENSE_RANK() OVER (
PARTITION BY month
ORDER BY revenue DESC
) AS rnk
FROM customer_revenue
) t
WHERE rnk <= 3;

📌 Question 3: Calculate Running Total Revenue

Table: sales sale_date, amount
Requirement: Show cumulative revenue over time.

SELECT
sale_date,
amount,
SUM(amount) OVER (
ORDER BY sale_date
) AS running_revenue
FROM sales;

📌 Question 4: Find Users Who Have Not Logged In During the Last 30 Days
Tables: users user_id, logins user_id, login_date

SELECT u.user_id
FROM users u
LEFT JOIN logins l
ON u.user_id = l.user_id
GROUP BY u.user_id
HAVING MAX(login_date) < CURRENT_DATE - INTERVAL '30 days'
OR MAX(login_date) IS NULL;

📌 Question 5: Detect Duplicate Transactions

Table: transactions transaction_id, customer_id, amount, transaction_date
Requirement: Find duplicate transactions based on customer, amount, and date.

SELECT
customer_id,
amount,
transaction_date,
COUNT(*) AS duplicate_count
FROM transactions
GROUP BY customer_id, amount, transaction_date
HAVING COUNT(*) > 1;

📌 Question 6: Calculate Average Order Value by Month

Table: orders order_id, amount, order_date

SELECT
DATE_TRUNC('month', order_date) AS month,
ROUND(AVG(amount), 2) AS avg_order_value
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

📌 Question 7: Find the Most Recent Order for Each Customer
Table: orders order_id, customer_id, order_date

WITH ranked_orders AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC
) AS rn
FROM orders
)
SELECT
customer_id,
order_id,
order_date
FROM ranked_orders
WHERE rn = 1;

📌 Question 8: Calculate Product Contribution to Total Revenue

Table: sales product_id, amount
Requirement: Find percentage contribution of each product.

SELECT
product_id,
SUM(amount) AS revenue,
ROUND(
100.0 * SUM(amount) /
SUM(SUM(amount)) OVER (),
2
) AS contribution_pct
FROM sales
GROUP BY product_id;

📌 Question 9: Find Customers with No Orders
Tables: customers customer_id, orders customer_id

SELECT c.customer_id
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

📌 Question 10: Calculate 7-Day Moving Average Sales

Table: sales sale_date, amount

SELECT
sale_date,
amount,
ROUND(
AVG(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
),
2
) AS moving_avg_7_days
FROM sales;

❤️ Double Tap For More