SQL Interview Questions

3 Sample Questions30 Total Questions

SQL problems from real data analyst/engineer interviews. Each problem is structured as: Problem → Hint → Answer → Explanation → Interview Points.

🟢 Sample Questions (3/30)

Question 1. Basic Aggregation

Beginner

[Problem] Calculate the total revenue and average order amount for completed orders in January 2024.

Tables: src_orders, src_order_items

💡 View Hint

Use JOIN to connect orders and order items
Filter by date and status using WHERE
Use SUM, AVG aggregate functions

✅ Answer Code


SELECT
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(oi.sale_price) AS total_revenue,
    AVG(oi.sale_price) AS avg_item_price,
    SUM(oi.sale_price) / COUNT(DISTINCT o.order_id) AS avg_order_value
FROM src_orders o
JOIN src_order_items oi ON o.order_id = oi.order_id
WHERE o.created_at >= '2024-01-01'
  AND o.created_at < '2024-02-01'
  AND o.status = 'Complete';

📖 Detailed Explanation

Key Points:

COUNT(DISTINCT o.order_id): Calculate actual order count by removing duplicates
Date filtering: >= AND < pattern is clearer than BETWEEN
Average order value: Calculate per-order average, not item average

Common Mistakes:

Using COUNT(*) gives order item count (not order count)
Confusion about boundary value inclusion when using BETWEEN

Interviewer Point:

“Why did you use DISTINCT?” → Explain preventing duplicates in 1:N relationships

Question 2. GROUP BY + HAVING

Beginner

[Problem] Among customers who ordered 10 or more times, find the top 5 by total purchase amount.

💡 View Hint

Aggregate by customer using GROUP BY
Filter for 10+ orders using HAVING
Get top 5 with ORDER BY + LIMIT

✅ Answer Code


SELECT
    o.user_id,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(oi.sale_price) AS total_spent
FROM src_orders o
JOIN src_order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'Complete'
GROUP BY o.user_id
HAVING COUNT(DISTINCT o.order_id) >= 10
ORDER BY total_spent DESC
LIMIT 5;

📖 Detailed Explanation

WHERE vs HAVING:

WHERE: Row-level filtering before grouping
HAVING: Group-level filtering after grouping


-- WHERE: Individual row filter (before grouping)
WHERE status = 'Complete'
 
-- HAVING: Aggregate result filter (after grouping)
HAVING COUNT(*) >= 10

Execution Order:


FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Interviewer Point:

“Could you solve this with a subquery instead of HAVING?” → Discuss performance differences

Question 3. Understanding LEFT JOIN

Beginner

[Problem] Get a list of customers who have never placed an order.

💡 View Hint

Use LEFT JOIN
Utilize the property that unmatched rows become NULL

✅ Answer Code


-- Method 1: LEFT JOIN + IS NULL
SELECT u.user_id, u.email, u.created_at
FROM src_users u
LEFT JOIN src_orders o ON u.user_id = o.user_id
WHERE o.order_id IS NULL;
 
-- Method 2: NOT EXISTS (can be more efficient)
SELECT user_id, email, created_at
FROM src_users u
WHERE NOT EXISTS (
    SELECT 1
    FROM src_orders o
    WHERE o.user_id = u.user_id
);
 
-- Method 3: NOT IN (beware of NULL)
SELECT user_id, email, created_at
FROM src_users
WHERE user_id NOT IN (
    SELECT DISTINCT user_id
    FROM src_orders
    WHERE user_id IS NOT NULL
);

📖 Detailed Explanation

How LEFT JOIN Works:


Users        Orders           Result (LEFT JOIN)
------       ------           -----------------
user_id=1    user_id=1   →   user_id=1, order_id=100
user_id=2    (none)      →   user_id=2, order_id=NULL  ← Filter this
user_id=3    user_id=3   →   user_id=3, order_id=101

Comparing Three Methods:

Method	Pros	Cons
LEFT JOIN + NULL	Intuitive	Can be slow on large datasets
NOT EXISTS	Efficient	Complex syntax
NOT IN	Concise	Requires NULL handling caution

Interviewer Point:

“What happens with NULL values in NOT IN?” → Entire result becomes empty set

🔒 Premium Questions (27 Questions)

All 30 Questions Breakdown

Difficulty	Questions	Main Topics
🟢 Beginner	10 questions	JOIN, GROUP BY, Date Functions, CASE WHEN
🟡 Intermediate	10 questions	Window Functions, LAG/LEAD, Cumulative Sum, CTE
🔴 Advanced	10 questions	Cohort Analysis, Funnels, Sessionization, Pivot

What You’ll Learn in Premium

✅ Master Window Functions: ROW_NUMBER, RANK, LAG, LEAD, SUM OVER
✅ Cohort Retention Analysis: SQL used in real companies
✅ Funnel Analysis: Conversion rate calculation queries
✅ Sessionization: Assigning session IDs with 30-minute gaps
✅ Anti-Join Pattern: Finding churned customers
✅ Answer Points Interviewers Expect

🎯 Purchase All 30 Questions + Explanations

SQL + Pandas + Statistics + Case Study bundle discount

📝 Practice More for Free

If you need more interview preparation, review the concept sections in the Cookbook: