Topic – Top 20 SQL Interview Questions and Answers
Top 20 SQL Interview Questions and Answers

Welcome to the 5-day series of SQL, Real-world analytics questions with answers

If you want to make a career in Analytics(from Fresher to 10 years of experience in Analytics/Non-Analytics domain), then you can check out our 2200+ Analytics Interview Questions across 23 topics to crack ANY Analytics Interview

20 Real World SQL Interview Questions

1. Find restaurants where the average order value on rainy days is at least 20% higher than on non-rainy days.
2. Identify users whose average order frequency (days between orders) has decreased over the last 3 months.
3. Detect users who have ordered from 3 or more new restaurants in the last 30 days.
4. Calculate the rolling 14-day retention rate of users based on their first order date.
5. Find delivery partners with a median delivery time above the 90th percentile of all deliveries.
6. Identify users who reactivated — i.e., placed an order after 60+ days of inactivity.
7. For each restaurant, calculate the standard deviation of daily revenue in the last 60 days.
8. Determine the share of first-time users who placed a second order within 7 days.
9. Find the most common delivery delay reason per restaurant (based on complaint logs).
10. Calculate the average basket size (items per order) by day of the week and user segment.
11. Identify cuisine types that see a revenue spike of over 50% during festivals or holidays.
12. Detect users who placed orders across at least 3 different cities in the past year.
13. Compare average delivery distance vs. average order value by region and flag outliers.
14. Build a leaderboard of users who referred others that ended up being high spenders.
15. Find restaurants where over 25% of monthly orders are rated below 3 stars consistently for 3 months.
16. Calculate revenue lost due to cancelled orders and break it down by cuisine and city.
17. Identify product categories where upselling (e.g., extra toppings) increases order value by >30%.
18. Determine the most common combination of 2 items ordered together by city.
19. Find users who placed an order within 15 minutes of receiving a push notification.
20. Create a cohort table showing weekly retention of users based on their signup week.

20 Real World SQL Interview Questions With Solution

Q1: Identify users whose average order frequency (days between orders) has decreased over the last 3 months

WITH order_dates AS (
SELECT user_id, order_date,
LAG(order_date) OVER (PARTITION BY user_id ORDER BY order_date) AS prev_order
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '6 months'
),
order_gaps AS (
SELECT user_id, order_date,
prev_order,
EXTRACT(DAY FROM order_date - prev_order) AS gap,
CASE WHEN order_date >= CURRENT_DATE - INTERVAL '3 months' THEN 'recent' ELSE 'past' END AS period
FROM order_dates
WHERE prev_order IS NOT NULL
),
avg_gaps AS (
SELECT user_id, period, AVG(gap) AS avg_gap
FROM order_gaps
GROUP BY user_id, period
),
pivoted AS (
SELECT user_id,
MAX(CASE WHEN period = 'past' THEN avg_gap END) AS past_gap,
MAX(CASE WHEN period = 'recent' THEN avg_gap END) AS recent_gap
FROM avg_gaps
GROUP BY user_id
)
SELECT user_id
FROM pivoted
WHERE recent_gap < past_gap;

Q2: Restaurants where avg order value on rainy days is 20% higher

WITH rainy AS (
SELECT restaurant_id, AVG(order_value) AS rainy_avg
FROM orders
WHERE weather = 'Rainy'
GROUP BY restaurant_id
),
non_rainy AS (
SELECT restaurant_id, AVG(order_value) AS non_rainy_avg
FROM orders
WHERE weather != 'Rainy'
GROUP BY restaurant_id
)
SELECT r.restaurant_id
FROM rainy r
JOIN non_rainy n ON r.restaurant_id = n.restaurant_id
WHERE r.rainy_avg > 1.2 * n.non_rainy_avg;

Q3: Users who ordered from 3+ new restaurants in last 30 days

WITH recent_orders AS (
SELECT user_id, restaurant_id, order_date
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
),
first_orders AS (
SELECT user_id, restaurant_id, MIN(order_date) AS first_order_date
FROM orders
GROUP BY user_id, restaurant_id
)
SELECT user_id
FROM first_orders
WHERE first_order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY user_id
HAVING COUNT(DISTINCT restaurant_id) >= 3;

Q4: Rolling 14-day retention rate

WITH first_orders AS (
SELECT user_id, MIN(order_date) AS signup_date
FROM orders
GROUP BY user_id
),
retention_window AS (
SELECT f.user_id, f.signup_date, o.order_date,
DATE_PART('day', o.order_date - f.signup_date) AS days_since_signup
FROM first_orders f
JOIN orders o ON f.user_id = o.user_id
),
retention_flag AS (
SELECT signup_date,
CASE WHEN COUNT(DISTINCT user_id) FILTER (WHERE days_since_signup BETWEEN 1 AND 14) > 0 THEN 1 ELSE 0 END AS retained
FROM retention_window
GROUP BY signup_date
)
SELECT signup_date,
AVG(retained) AS retention_rate
FROM retention_flag
GROUP BY signup_date;

Q5: Delivery partners with median delivery time above 90th percentile

WITH delivery_stats AS (
SELECT delivery_partner_id, delivery_time,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY delivery_time) OVER (PARTITION BY delivery_partner_id) AS median_time
FROM deliveries
),
overall_90th AS (
SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY delivery_time) AS p90
FROM deliveries
)
SELECT DISTINCT d.delivery_partner_id
FROM delivery_stats d, overall_90th o
WHERE d.median_time > o.p90;

Best e-book for Real World Interview Questions

You can get these types of SQL questions, as well as questions on multiple topics like Python(Only analytics-related coding questions), Pandas, Numpy, Case Studies, Machine Learning Algorithms, MS Excel, Hadoop, AWS, Statistics, Big Data Technologies, and many more in our master ebook

2200+ Interview Questions to Become a Full Stack Analytics Professional

If you want to crack the SQL Interview, then go for our ebook:

Ace ANY SQL Interview – 200+ Advance SQL interview Questions

Follow The Data Monk on Instagram for our Giveaway ebooks
Subscribe The Data Monk YouTube channel for company-wise Interview Questions

1:1 Complete Mentorship by The Data Monk