🧠 Scenario-Based SQL Interview Questions

[hirenpatel1903]

🧠 Scenario-Based SQL Interview Questions

A curated collection of scenario-based SQL interview questions with clear, concise solutions.
This repository is designed for beginners to advanced developers preparing for technical interviews, coding tests, and quick SQL revision.

---

🎯 Who Is This For?

• ✅ Beginners to Advanced SQL Developers
• ✅ Technical Interviews & Coding Tests
• ✅ SQL Revision & Practice
• ✅ Backend / Data Engineering Prep

---

📌 Table of Contents

1. Duplicate Records
2. Second Highest Salary
3. Employees Without Department
4. Revenue Per Product
5. Top Paid Employees
6. Customers Without Returns
7. Orders Per Customer
8. Employees Joined in 2023
9. Average Order Value
10. Latest Order Per Customer
11. Unsold Products
12. Most Selling Product
13. Revenue & Orders Per Region
14. Customers With More Than 5 Orders
15. Orders Above Average Value
16. Employees Hired on Weekends
17. Salary Range Filter
18. Monthly Revenue & Orders
19. Salary Ranking by Department
20. Customers Ordering Every Month
21. Moving Average of Sales
22. First & Last Order Date
23. Revenue Distribution %
24. Consecutive Purchases
25. Churned Customers
26. Cumulative Revenue
27. Top Performing Departments
28. Above-Average Customers
29. Revenue From New Customers
30. Department Employee %
31. Salary Difference per Department
32. Pareto (80/20) Products
33. Latest Order with Amount
34. Avg Time Between Purchases
35. Year-over-Year Growth
36. 90th Percentile Orders
37. Longest Gap Between Orders
38. Bottom 10% Revenue Customers

---

1️⃣ Find Duplicate Records (Amazon)

Problem: Find duplicate rows based on multiple columns in a table.
Solution:

SELECT column1, column2, COUNT(*) AS duplicate_count
FROM your_table
GROUP BY column1, column2
HAVING COUNT(*) > 1;

---

2️⃣ Retrieve the Second Highest Salary (Microsoft)

Problem: Find the second highest salary from the Employee table.
Solution:

SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

---

3️⃣ Employees Without a Department (Uber)

Problem: Find employees who are not assigned to any department.
Solution:

SELECT e.*
FROM Employee e
LEFT JOIN Department d ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

---

4️⃣ Total Revenue Per Product (PayPal)

Problem: Calculate total revenue generated for each product.
Solution:

SELECT product_id, SUM(quantity * price) AS total_revenue
FROM Sales
GROUP BY product_id;

---

5️⃣ Top 3 Highest-Paid Employees (Google)

Problem: Retrieve the top 3 highest paid employees.
Solution:

SELECT *
FROM Employee
ORDER BY salary DESC
LIMIT 3;

---

6️⃣ Customers Who Purchased but Never Returned (Walmart)

Problem: Find customers who made purchases but never returned any product.
Solution:

SELECT DISTINCT c.customer_id
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
WHERE c.customer_id NOT IN (SELECT customer_id FROM Returns);

---

7️⃣ Count Orders Per Customer (Meta)

Problem: Count the number of orders placed by each customer.
Solution:

SELECT customer_id, COUNT(*) AS order_count
FROM Orders
GROUP BY customer_id;

---

8️⃣ Employees Joined in 2023 (Amazon)

Problem: Retrieve employees who joined in the year 2023.
Solution:

SELECT *
FROM Employee
WHERE EXTRACT(YEAR FROM hire_date) = 2023;

---

9️⃣ Average Order Value Per Customer (Microsoft)

Problem: Calculate average order value for each customer.
Solution:

SELECT customer_id, AVG(total_amount) AS avg_order_value
FROM Orders
GROUP BY customer_id;

---

🔟 Latest Order Per Customer (Uber)

Problem: Find the latest order date for each customer.
Solution:

SELECT customer_id, MAX(order_date) AS latest_order_date
FROM Orders
GROUP BY customer_id;

---

1️⃣1️⃣ Products Never Sold

Problem: Identify products that have never been sold.
Solution:

SELECT p.product_id
FROM Products p
LEFT JOIN Sales s ON p.product_id = s.product_id
WHERE s.product_id IS NULL;

---

1️⃣2️⃣ Most Selling Product (Adobe/Walmart)

Problem: Find the product with the highest quantity sold.
Solution:

SELECT product_id, SUM(quantity) AS total_qty
FROM Sales
GROUP BY product_id
ORDER BY total_qty DESC
LIMIT 1;

---

1️⃣3️⃣ Revenue & Orders Per Region (Meta)

Problem: Calculate total revenue and number of orders for each region.
Solution:

SELECT region,
       SUM(total_amount) AS total_revenue,
       COUNT(*) AS order_count
FROM Orders
GROUP BY region;

---

1️⃣4️⃣ Customers With More Than 5 Orders (Amazon)

Problem: Count customers who placed more than 5 orders.
Solution:

SELECT COUNT(*) AS customer_count
FROM (
    SELECT customer_id
    FROM Orders
    GROUP BY customer_id
    HAVING COUNT(*) > 5
) t;

---

1️⃣5️⃣ Orders Above Average Order Value (PayPal)

Problem: Find customers with orders above the average order value.
Solution:

SELECT DISTINCT customer_id
FROM Orders
WHERE total_amount > (SELECT AVG(total_amount) FROM Orders);

---

1️⃣6️⃣ Employees Hired on Weekends (Google)

Problem: Find employees hired on Saturdays or Sundays.
Solution:

SELECT *
FROM Employee
WHERE EXTRACT(DOW FROM hire_date) IN (0, 6);

---

1️⃣7️⃣ Salary Between 50K and 100K (Microsoft)

Problem: Retrieve employees with salary between 50,000 and 100,000.
Solution:

SELECT *
FROM Employee
WHERE salary BETWEEN 50000 AND 100000;

---

1️⃣8️⃣ Monthly Sales Revenue & Orders (Google)

Problem: Calculate monthly revenue and order count.
Solution:

SELECT TO_CHAR(order_date, 'YYYY-MM') AS month,
       SUM(total_amount) AS total_revenue,
       COUNT(order_id) AS order_count
FROM Orders
GROUP BY month;

---

1️⃣9️⃣ Rank Employees by Salary per Department (Amazon)

Problem: Rank employees by salary within each department.
Solution:

SELECT employee_id, department_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM Employee;

---

2️⃣0️⃣ Find Customers Who Placed Orders Every Month in 2023 (Meta)

Problem: Find customers who placed at least one order every month in 2023.
Solution:

SELECT customer_id
FROM Orders
WHERE EXTRACT(YEAR FROM order_date) = 2023
GROUP BY customer_id
HAVING COUNT(DISTINCT TO_CHAR(order_date, 'YYYY-MM')) = 12;

---

2️⃣1️⃣ Find Moving Average of Sales Over the Last 3 Days (Microsoft)

Problem: Calculate 3-day moving average of sales.
Solution:

SELECT order_date,
       AVG(total_amount) OVER (
         ORDER BY order_date
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_avg_3day
FROM Orders;

---

2️⃣2️⃣ Identify the First and Last Order Date for Each Customer (Uber)

Problem: Get the first and last order dates for each customer.
Solution:

SELECT customer_id,
       MIN(order_date) AS first_order,
       MAX(order_date) AS last_order
FROM Orders
GROUP BY customer_id;

---

2️⃣3️⃣ Show Product Sales Distribution (Percent of Total Revenue) (PayPal)

Problem: Calculate percentage revenue contribution of each product.
Solution:

WITH TotalRevenue AS (
  SELECT SUM(quantity * price) AS total FROM Sales
)
SELECT s.product_id,
       SUM(s.quantity * s.price) AS revenue,
       SUM(s.quantity * s.price) * 100.0 / t.total AS revenue_pct
FROM Sales s
CROSS JOIN TotalRevenue t
GROUP BY s.product_id, t.total;

---

2️⃣4️⃣ Retrieve Customers Who Made Consecutive Purchases (2 Days) (Walmart)

Problem: Find customers who made purchases on consecutive days.
Solution:

WITH cte AS (
  SELECT customer_id,
         order_date,
         LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_date
  FROM Orders
)
SELECT customer_id, order_date, prev_order_date
FROM cte
WHERE order_date - prev_order_date = INTERVAL '1' DAY;

---

2️⃣5️⃣ Find Churned Customers (No Orders in the Last 6 Months) (Amazon)

Problem: Identify customers with no orders in the last 6 months.
Solution:

SELECT customer_id
FROM Orders
GROUP BY customer_id
HAVING MAX(order_date) < (NOW() - INTERVAL '6 months');

---

2️⃣6️⃣ Calculate Cumulative Revenue by Day (Adobe)

Problem: Calculate cumulative revenue day by day.
Solution:

SELECT order_date, SUM(total_amount) OVER (ORDER BY order_date) AS cumulative_revenue
FROM Orders;

---

2️⃣7️⃣ Identify Top-Performing Departments by Average Salary (Google)

Problem: Find departments with highest average salary.
Solution:

SELECT department_id, AVG(salary) AS avg_salary
FROM Employee
GROUP BY department_id
ORDER BY avg_salary DESC;

---

2️⃣8️⃣ Find Customers Who Ordered More Than the Average Number of Orders Per Customer (Meta)

Problem: Identify customers who placed more orders than the average customer.
Solution:

WITH customer_orders AS (
  SELECT customer_id, COUNT(*) AS order_count
  FROM Orders
  GROUP BY customer_id
)
SELECT customer_id, order_count
FROM customer_orders
WHERE order_count > (SELECT AVG(order_count) FROM customer_orders);

---

2️⃣9️⃣ Calculate Revenue Generated from New Customers (First-Time Orders) (Microsoft)

Problem: Calculate revenue from customers on their first order only.
Solution:

WITH first_orders AS (
  SELECT customer_id, MIN(order_date) AS first_order_date
  FROM Orders
  GROUP BY customer_id
)
SELECT SUM(o.total_amount) AS new_revenue
FROM Orders o
JOIN first_orders f ON o.customer_id = f.customer_id
WHERE o.order_date = f.first_order_date;

---

3️⃣0️⃣ Find the Percentage of Employees in Each Department (Uber)

Problem: Calculate employee percentage per department.
Solution:

SELECT department_id, COUNT(*) AS emp_count, COUNT(*) * 100.0 / (SELECT COUNT(*) FROM Employee) AS pct
FROM Employee
GROUP BY department_id;

---

3️⃣1️⃣ Retrieve the Maximum Salary Difference Within Each Department (PayPal)

Problem: Find the salary range in each department.
Solution:

SELECT department_id,
       MAX(salary) - MIN(salary) AS salary_diff
FROM Employee
GROUP BY department_id;

---

3️⃣2️⃣ Find Products That Contribute to 80% of the Revenue (Pareto Principle) (Walmart)

Problem: Identify products that make up 80% of revenue.
Solution:

WITH sales_cte AS (
  SELECT product_id, SUM(quantity * price) AS revenue
  FROM Sales
  GROUP BY product_id
),
total_revenue AS (
  SELECT SUM(revenue) AS total FROM sales_cte
)
SELECT product_id, revenue, cumulative_revenue
FROM (
  SELECT s.product_id,
         s.revenue,
         SUM(s.revenue) OVER (ORDER BY s.revenue DESC) AS cumulative_revenue,
         t.total
  FROM sales_cte s CROSS JOIN total_revenue t
) sub
WHERE cumulative_revenue <= total * 0.8;

---

3️⃣3️⃣ Show Last Purchase for Each Customer Along with Order Amount (Google)

Problem: Get the last order with amount for each customer.
Solution:

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

---

3️⃣4️⃣ Calculate Average Time Between Two Purchases for Each Customer (Meta)

Problem: Calculate average days between purchases for each customer.
Solution:

WITH cte AS (
  SELECT customer_id, order_date,
         LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_date
  FROM Orders
)
SELECT customer_id,
       AVG(DATE_DIFF('day', prev_date, order_date)) AS avg_gap_days
FROM cte
WHERE prev_date IS NOT NULL
GROUP BY customer_id;

---

3️⃣5️⃣ Calculate Year-Over-Year Growth in Revenue (Microsoft)

Problem: Calculate yearly revenue and growth from previous year.
Solution:

WITH yearly AS (
  SELECT EXTRACT(YEAR FROM order_date) AS year,
         SUM(total_amount) AS revenue
  FROM Orders
  GROUP BY EXTRACT(YEAR FROM order_date)
)
SELECT year,
       revenue,
       revenue - LAG(revenue) OVER (ORDER BY year) AS yoy_growth
FROM yearly;

---

3️⃣6️⃣ Detect Customers Whose Purchase Amount Is Higher Than Their Historical 90th Percentile (Amazon)

Problem: Find orders where purchase amount is in the top 10% for the customer.
Solution:

WITH ranked_orders AS (
  SELECT customer_id, order_id, total_amount,
         NTILE(10) OVER (PARTITION BY customer_id ORDER BY total_amount) AS decile
  FROM Orders
)
SELECT customer_id, order_id, total_amount
FROM ranked_orders
WHERE decile = 10;

---

3️⃣7️⃣ Retrieve the Longest Gap Between Orders for Each Customer (Meta)

Problem: Find the longest number of days between two orders for each customer.
Solution:

WITH cte AS (
  SELECT customer_id, order_date,
         LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_date
  FROM Orders
)
SELECT customer_id,
       MAX(DATE_DIFF('day', prev_order_date, order_date)) AS max_gap_days
FROM cte
WHERE prev_order_date IS NOT NULL
GROUP BY customer_id;

---

3️⃣8️⃣ Identify Customers with Revenue Below the 10th Percentile (Google)

Problem: Find customers with total revenue below the 10th percentile.
Solution:

WITH c AS (
  SELECT customer_id, SUM(total_amount) AS total_revenue
  FROM Orders
  GROUP BY customer_id
)
SELECT customer_id, total_revenue
FROM c
WHERE total_revenue < (
  SELECT PERCENTILE_CONT(0.1) WITHIN GROUP (ORDER BY total_revenue) FROM c
);

---

🧠 Interview Tips

• Clarify NULL handling and edge cases
• Prefer JOINs over subqueries
• Practice window functions
• Think about performance and indexing

---

---

🌐 Follow & Subscribe

Stay connected and support us online:

Platform	Link
Twitter	https://twitter.com/einzigartigetec/
Facebook	https://www.facebook.com/einzigartige/
LinkedIn	https://www.linkedin.com/company/einzigartige/
YouTube	https://www.youtube.com/@einzigartige_/
Website	http://einzigartige.in/

---

📊 Live YouTube Statistics

👉 Subscribe here: https://www.youtube.com/@einzigartige_/