Scenario-based SQL Interview
Questions
1. Find Duplicate Records in a Table (Amazon)
SELECT column1, column2, COUNT(*)
FROM your_table
GROUP BY column1, column2
HAVING COUNT(#) > 1;
[Link] the Second Highest Salary from Employee Table
SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);
3. Find Employees Without Department (Uber)
SELECT e.*
FROM Employee e
LEFT JOIN Department d ON e.department_id = d.department_id
WHERE d.department_id IS NULL;
4. Calculate the Total Revenue Per Product (PayPal)
SELECT product_id, SUM(quantity * price) AS total_revenue
FROM Sales
GROUP BY product_id;
WHERE c(customer_id) NOT IN (SELECT customer_id FROM RETURNS);
7. Show the Count of Orders Per Customer (Meta)
SELECT customer_id, COUNT(*) AS order_count
FROM Orders
GROUP BY customer_id;
[Link] All Employees Who Joined in 2023 (Amazon)
SELECT * FROM Employee
WHERE EXTRACT(YEAR FROM hire_date) = 2023;
9. Calculate Average Order Value Per Customer (Microsoft)
SELECT customer_id, AVG(total_amount) AS avg_order_value
FROM Orders
GROUP BY customer_id;
10. Get the Latest Order Placed by Each Customer (Uber)
SELECT customer_id, MAX(order date) AS latest_order date
FROM Orders
GROUP BY customer_id;
11. Find Products That Were Never Sold
FROM Sales
GROUP BY product_id
ORDER BY total_qty DESC LIMIT 1;
13. Get Total Revenue and Number of Orders Per Region (Meta)
SELECT region, SUM(total_amount) AS total_revenue, COUNT(*) AS order-count
FROM Orders
GROUP BY region;
14. Count Customers with More Than 5 Orders (Amazon)
SELECT COUNT(*) AS customer_count
FROM (
SELECT customer_id
FROM Orders
GROUP BY customer_id
HAVING COUNT(*) > 5
) AS subquery;
[Link] Customers with Orders Above Average Order Value (PayPal)
SELECT DISTINCT customer_id
FROM Orders
WHERE total_amount > (SELECT Avg(total_amount) FROM Orders);
16. Find All Employees Hired on Weekends (Google)
18. Get Monthly Sales Revenue and Order Count (Google)
SELECT TO_CHAR(order, 'YYYY-MM') AS month,
SUM(total_amount) AS total_revenue,
COUNT(order_id) AS order-count
FROM Orders
GROUP BY TO_CHAR(order, 'YYYY-MM')
19. Rank Employees by Salary Within Each Department (Amazon)
SELECT employee_id, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rk
FROM Employee;
20. Find Customers Who Placed Orders Every Month in 2023 (Meta)
SELECT customer_id
FROM Orders
WHERE EXTRACT(YEAR FROM order_date) = 2023
GROUP BY customer_id
HAVING COUNT(DISTINCT TO_CHAR(order, 'YYYY-MM') = 12;
21. Find Moving Average of Sales Over the Last 3 Days (Microsoft)
SELECT order_date,
AVG(total_amount) OVER (
ORDER BY order_date
23. Show Product Sales Distribution (Percent of Total Revenue) (PayPal)
WITH TotalRevenue AS (
SELECT SUM(quantity * price) AS total FROM Sales
)
SELECT s.product_id,
SUM([Link] * [Link]) AS revenue,
SUM([Link] * s-price) * 100 / [Link] AS revenue_pct
FROM Sales s
CROSS JOIN TotalRevenue t
GROUP BY s/product_id, [Link];
[Link] Customers Who Made Consecutive Purchases (2 Days) (Walmart)
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;
25. Find Churned Customers (No Orders in the Last 6 Months) (Amazon)
SELECT customer_id
FROM Orders
27. Identify Top-Performing Departments by Average Salary (Google)
SELECT department_id, AVG(salary) AS avg_salary
FROM Employee
GROUP BY department_id
ORDER BY avg_salary DESC;
28. Find Customers Who Ordered More Than the Average Number of Orders Per Customer (Meta)
WITH customer-orders AS (
SELECT customer_id, COUNT(*) AS order_count
FROM Orders
GROUP BY customer_id
)
SELECT *
FROM customer-orders
WHERE order_count > (SELECT AVG(order) FROM customer-orders);
29. Calculate Revenue Generated from New Customers (First-Time Orders) (Microsoft)
WITH first orders AS (
SELECT customer_id, MIN(order) AS first_order
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 [Link] date = f-first order_date;
GROUP BY department_id;
[Link] the Maximum Salary Difference Within Each Department (PayPal)
SELECT department_id,
MAX(salary) - MIN(salary) AS salary_diff
FROM Employee
GROUP BY department_id;
32. Find Products That Contribute to 80% of the Revenue (Pareto Principle) (Walmart)
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, [Link],
SUM([Link]) OVER (ORDER BY [Link] DESC) AS cumulative_revenue,
[Link]
FROM sales_cte s CROSS JOIN total_revenue t)
WHERE cumulative_revenue <= total * 0.8;
33. Show Last Purchase for Each Customer Along with Order Amount (Google)
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
LAG(order date,
LAG(order date) OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS prev_date
FROM Orders
) SELECT
customer_id,
_avg(datetime DIFF(DAY, prev_date, order_date) AS
avg_gap_days
FROM cte
WHERE prev_date IS NOT NULL
GROUP BY customer_id;
35. Calculate Year-Over-Year Growth in Revenue (Microsoft)
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;
36. Detect Customers Whose Purchase Amount Is Higher Than Their Historical 90th Percentile
(Amazon)
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
)
prev_order date
FROM Orders
)
SELECT customer_id,
MAX(DATE_DIFF(DAY, prev_order, order_date)) AS max_gap_days
FROM cte
WHERE prev_order DATE IS NOT NULL
GROUP BY customer_id;
38. Identify Customers with Revenue Below the 10th Percentile (Google)
WITH cte AS (
SELECT customer_id, SUM(total_amount) AS total_revenue
FROM Orders
GROUP BY customer_id
SELECT customer_id, total_revenue
FROM cte
WHERE total_revenue < (
SELECT percentile_cont( ) WITHIN GROUP (ORDER BY
total_revenue) FROM cte )