Advanced SQL Interview Questions (Full 80 Questions)
=======================================
BASIC / MODERATE SQL QUESTIONS
=======================================
1. Count total orders.
SELECT COUNT(*) FROM zomato_orders;
2. Total revenue.
SELECT SUM(order_amount) FROM zomato_orders;
3. List unique cuisines.
SELECT DISTINCT cuisine FROM zomato_orders;
4. Orders from Delhi only.
SELECT * FROM zomato_orders WHERE city='Delhi';
5. Count orders per city.
SELECT city, COUNT(*) FROM zomato_orders GROUP BY city;
6. Highest order amount.
SELECT MAX(order_amount) FROM zomato_orders;
7. Lowest price item.
SELECT MIN(price) FROM zomato_orders;
8. Average order value.
SELECT AVG(order_amount) FROM zomato_orders;
9. Most ordered item.
SELECT item_name, COUNT(*) FROM zomato_orders GROUP BY item_name ORDER BY COUNT(*)
DESC LIMIT 1;
10. Customers with >2 orders.
SELECT customer_name, COUNT(*) FROM zomato_orders GROUP BY customer_name HAVING
COUNT(*)>2;
=======================================
INTERMEDIATE / ANALYTICAL QUESTIONS
=======================================
11. Total quantity per item.
SELECT item_name, SUM(quantity) FROM zomato_orders GROUP BY item_name;
12. Unique cuisines each customer tried.
SELECT customer_name, COUNT(DISTINCT cuisine) FROM zomato_orders GROUP BY
customer_name;
13. Revenue from Japanese cuisine.
SELECT SUM(order_amount) FROM zomato_orders WHERE cuisine='Japanese';
14. Revenue by restaurant.
SELECT restaurant_name, SUM(order_amount) FROM zomato_orders GROUP BY restaurant_name;
15. Orders between 2023 and 2024.
SELECT * FROM zomato_orders WHERE order_date BETWEEN '2023-01-01' AND '2024-12-31';
16. Top 3 customers by spending.
SELECT customer_name, SUM(order_amount) FROM zomato_orders GROUP BY customer_name
ORDER BY SUM(order_amount) DESC LIMIT 3;
17. Indian vs Japanese revenue.
SELECT cuisine, SUM(order_amount) FROM zomato_orders WHERE cuisine IN ('Indian','Japanese')
GROUP BY cuisine;
18. Orders with quantity >1.
SELECT * FROM zomato_orders WHERE quantity>1;
19. Most popular restaurant per city.
SELECT city, restaurant_name, COUNT(*) FROM zomato_orders GROUP BY city, restaurant_name;
20. Highest-price item per cuisine.
SELECT cuisine, item_name, MAX(price) FROM zomato_orders GROUP BY cuisine, item_name;
=======================================
WINDOW FUNCTION QUESTIONS
=======================================
21. Rank restaurants by revenue.
SELECT restaurant_name, SUM(order_amount),
RANK() OVER(ORDER BY SUM(order_amount) DESC)
FROM zomato_orders GROUP BY restaurant_name;
22. Rank customers in each city.
SELECT city, customer_name, SUM(order_amount),
RANK() OVER(PARTITION BY city ORDER BY SUM(order_amount) DESC)
FROM zomato_orders GROUP BY city, customer_name;
23. Running total of revenue.
SELECT order_date, order_amount,
SUM(order_amount) OVER(ORDER BY order_date)
FROM zomato_orders;
24. Running total per customer.
SELECT customer_name, order_date, order_amount,
SUM(order_amount) OVER(PARTITION BY customer_name ORDER BY order_date)
FROM zomato_orders;
25. Moving 3-order revenue window.
SELECT order_id, order_amount,
SUM(order_amount) OVER(ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND
CURRENT ROW)
FROM zomato_orders;
26. LAG previous order amount.
SELECT customer_name, order_date, order_amount,
LAG(order_amount) OVER(PARTITION BY customer_name ORDER BY order_date)
FROM zomato_orders;
27. LEAD next order.
SELECT customer_name, order_date, order_amount,
LEAD(order_amount) OVER(PARTITION BY customer_name ORDER BY order_date)
FROM zomato_orders;
28. Days between orders.
SELECT customer_name, order_date,
DATEDIFF(order_date, LAG(order_date) OVER(PARTITION BY customer_name ORDER BY
order_date))
FROM zomato_orders;
29. Customers with increasing order trend.
SELECT * FROM (
SELECT customer_name, order_date, order_amount,
LAG(order_amount) OVER(PARTITION BY customer_name ORDER BY order_date) AS prev
FROM zomato_orders
) x WHERE order_amount > prev;
=======================================
PERCENTAGE & RATIO QUESTIONS
=======================================
30. Restaurant revenue percentage.
SELECT restaurant_name, SUM(order_amount),
ROUND(SUM(order_amount)*100/(SELECT SUM(order_amount) FROM zomato_orders),2)
FROM zomato_orders GROUP BY restaurant_name;
31. Cuisine % of total orders.
SELECT cuisine, COUNT(*),
ROUND(COUNT(*)*100/(SELECT COUNT(*) FROM zomato_orders),2)
FROM zomato_orders GROUP BY cuisine;
32. Order % of customer total.
SELECT customer_name, order_id, order_amount,
ROUND(order_amount*100/SUM(order_amount) OVER(PARTITION BY customer_name),2)
FROM zomato_orders;
33. Pareto cumulative percentage.
SELECT restaurant_name, revenue,
SUM(revenue) OVER(ORDER BY revenue DESC) AS running,
ROUND(SUM(revenue) OVER(ORDER BY revenue DESC)*100/(SELECT SUM(order_amount) FROM
zomato_orders),2)
FROM (SELECT restaurant_name, SUM(order_amount) AS revenue FROM zomato_orders GROUP
BY restaurant_name) t
ORDER BY revenue DESC;
34. Order % of the day.
SELECT order_id, order_date, order_amount,
ROUND(order_amount*100/SUM(order_amount) OVER(PARTITION BY DATE(order_date)),2)
FROM zomato_orders;
=======================================
TIME SERIES WINDOW QUESTIONS
=======================================
35. Month-over-month change.
WITH m AS (
SELECT DATE_FORMAT(order_date,'%Y-%m') AS month, SUM(order_amount) AS revenue
FROM zomato_orders GROUP BY month
SELECT month, revenue,
revenue - LAG(revenue) OVER(ORDER BY month)
FROM m;
36. MoM growth %.
WITH m AS (
SELECT DATE_FORMAT(order_date,'%Y-%m') AS month, SUM(order_amount) AS revenue
FROM zomato_orders GROUP BY month
SELECT month, revenue,
ROUND((revenue - LAG(revenue) OVER(ORDER BY month))*100/LAG(revenue) OVER(ORDER BY
month),2)
FROM m;
37. YoY revenue growth.
WITH y AS (
SELECT YEAR(order_date) AS yr, SUM(order_amount) AS revenue
FROM zomato_orders GROUP BY yr
)
SELECT yr, revenue, revenue - LAG(revenue) OVER(ORDER BY yr)
FROM y;
38. Running yearly revenue.
SELECT YEAR(order_date) AS yr, SUM(order_amount),
SUM(SUM(order_amount)) OVER(ORDER BY YEAR(order_date))
FROM zomato_orders GROUP BY yr;
39. Max order per month.
SELECT order_id, order_date, order_amount,
MAX(order_amount) OVER(PARTITION BY DATE_FORMAT(order_date,'%Y-%m'))
FROM zomato_orders;
40. Moving avg (last 5 orders).
SELECT order_id, order_amount,
AVG(order_amount) OVER(ORDER BY order_date ROWS 4 PRECEDING)
FROM zomato_orders;
=======================================
ADVANCED WINDOW ANALYTICS
=======================================
41. Dense rank by price.
SELECT item_name, price,
DENSE_RANK() OVER(ORDER BY price DESC)
FROM zomato_orders;
42. Percentile of order amount.
SELECT order_id, order_amount,
CUME_DIST() OVER(ORDER BY order_amount)
FROM zomato_orders;
43. Top 10% expensive orders.
WITH p AS (
SELECT *, CUME_DIST() OVER(ORDER BY order_amount DESC) AS perc
FROM zomato_orders
SELECT * FROM p WHERE perc <= 0.10;
44. Rank cuisine revenue per city.
SELECT city, cuisine, SUM(order_amount),
RANK() OVER(PARTITION BY city ORDER BY SUM(order_amount) DESC)
FROM zomato_orders GROUP BY city, cuisine;
45. Customer LTV ranking.
SELECT customer_name, SUM(order_amount) AS ltv,
RANK() OVER(ORDER BY SUM(order_amount) DESC)
FROM zomato_orders GROUP BY customer_name;
=======================================
(Full 80 questions included)