50 Complex Business Scenario-Based SQL Interview Questions (With Real-World Code & Detailed Answers)

Master the art of solving real business problems with SQL — from SaaS churn to retail inventory, fintech fraud, and logistics optimization. All answers include working queries, explanations, and production-grade thinking.

🔍 Why This Article Matters SQL isn’t just about SELECT * FROM users. In top tech companies — Stripe, Netflix, Airbnb, Shopify, Uber — SQL interviews are the gatekeeper. 
You won’t be asked to normalize a table. You’ll be asked: “How would you calculate the 30-day retention rate for users who signed up during Black Friday, segmented by acquisition channel and device type?”

That’s not trivia. That’s business intelligence in code. This guide gives you 50 realistic, high-difficulty SQL interview questions, each based on actual business scenarios from:

• E-commerce
• SaaS subscriptions
• Fintech fraud detection
• Logistics & delivery
• Ad tech & marketing
• Healthcare analytics
• Gaming & user engagement

Each question includes: ✅ A business context
✅ Sample schema design
✅ Working SQL solution (PostgreSQL/MySQL compatible)
✅ Step-by-step explanation
✅ Performance tips & edge cases
✅ Why this matters in production

All designed to make you stand out — not just pass, but dominate. 📌 How to Use This Guide

• ✅ For Interviewees: Practice one per day. Time yourself (15–20 mins).
• ✅ For Hiring Managers: Use these as screening benchmarks.
• ✅ For Data Analysts/Engineers: Add to your personal SQL playbook.
• ✅ For Recruiters: Share with candidates to test depth.

💼 Section 1: E-Commerce & Retail Q1: Calculate the Average Order Value (AOV) for customers who made repeat purchases in the last 90 days

Business Context:
Your CFO wants to know if loyal customers spend more. You need to compare AOV of first-time vs repeat buyers.

Schema:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2)
);

Solution:

WITH customer_order_counts AS (
    SELECT 
        customer_id,
        COUNT(*) AS order_count
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY customer_id
),
repeat_customers AS (
    SELECT customer_id
    FROM customer_order_counts
    WHERE order_count > 1
),
first_time_buyers AS (
    SELECT customer_id
    FROM customer_order_counts
    WHERE order_count = 1
)
SELECT
    'Repeat' AS customer_type,
    AVG(o.total_amount) AS avg_order_value,
    COUNT(*) AS total_orders
FROM orders o
JOIN repeat_customers rc ON o.customer_id = rc.customer_id
GROUP BY customer_type

UNION ALL

SELECT
    'First-Time' AS customer_type,
    AVG(o.total_amount) AS avg_order_value,
    COUNT(*) AS total_orders
FROM orders o
JOIN first_time_buyers ftb ON o.customer_id = ftb.customer_id
GROUP BY customer_type;

Explanation:
We use CTEs to segment customers by purchase frequency. Then join back to orders to compute metrics. Avoids subqueries for clarity and performance. Edge Case: What if someone bought 3x in 90 days? Still counted as “repeat.”
Production Tip: Pre-compute this in a materialized view refreshed daily. Q2: Find products that had a spike in sales (≥ 200% increase) compared to the previous week

Business Context:
Marketing team suspects a viral product. Need to auto-flag anomalies for promo. Schema:

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    sale_date DATE,
    quantity INT
);

Solution:

WITH weekly_sales AS (
    SELECT
        product_id,
        DATE_TRUNC('week', sale_date) AS week_start,
        SUM(quantity) AS weekly_quantity
    FROM sales
    WHERE sale_date >= CURRENT_DATE - INTERVAL '14 days'
    GROUP BY product_id, week_start
),
lagged AS (
    SELECT
        product_id,
        week_start,
        weekly_quantity,
        LAG(weekly_quantity) OVER (PARTITION BY product_id ORDER BY week_start) AS prev_week_qty
    FROM weekly_sales
)
SELECT
    product_id,
    week_start AS current_week,
    weekly_quantity AS current_sales,
    prev_week_qty AS previous_sales,
    ROUND(
        ((weekly_quantity::DECIMAL / NULLIF(prev_week_qty, 0)) - 1) * 100, 2
    ) AS percentage_increase
FROM lagged
WHERE prev_week_qty IS NOT NULL
  AND ((weekly_quantity::DECIMAL / NULLIF(prev_week_qty, 0)) - 1) >= 2; -- ≥ 200%

Explanation:
Uses LAG() window function to get prior week’s value. Uses NULLIF to avoid division by zero. Filters for ≥ 200% increase. Why it matters: Replaces manual Excel spotting. Can be automated into alerting system (e.g., via Airflow + Slack).

Q3: Identify cart abandonment rate by device type for users who added items but didn’t complete checkout within 24 hours

Business Context:
Mobile users abandon carts more. Optimize UX accordingly. Schema:

CREATE TABLE user_events (
    event_id INT PRIMARY KEY,
    user_id INT,
    event_type VARCHAR(50), -- 'add_to_cart', 'checkout_start', 'purchase_complete'
    event_timestamp TIMESTAMP,
    device_type VARCHAR(20) -- 'mobile', 'desktop', 'tablet'
);

Solution:

WITH cart_added AS (
    SELECT
        user_id,
        device_type,
        event_timestamp AS cart_time
    FROM user_events
    WHERE event_type = 'add_to_cart'
),
checkout_started AS (
    SELECT
        user_id,
        event_timestamp AS checkout_time
    FROM user_events
    WHERE event_type = 'checkout_start'
),
purchased AS (
    SELECT
        user_id,
        event_timestamp AS purchase_time
    FROM user_events
    WHERE event_type = 'purchase_complete'
),
abandoned AS (
    SELECT
        ca.user_id,
        ca.device_type,
        ca.cart_time,
        COALESCE(cs.checkout_time, ca.cart_time) AS latest_activity
    FROM cart_added ca
    LEFT JOIN checkout_started cs ON ca.user_id = cs.user_id AND cs.checkout_time >= ca.cart_time
    LEFT JOIN purchased p ON ca.user_id = p.user_id AND p.purchase_time >= ca.cart_time
    WHERE p.purchase_time IS NULL
      AND ca.cart_time >= CURRENT_TIMESTAMP - INTERVAL '24 hours'
)
SELECT
    device_type,
    COUNT(*) AS total_abandoned_carts,
    COUNT(*) * 100.0 / (
        SELECT COUNT(*) FROM cart_added
        WHERE cart_time >= CURRENT_TIMESTAMP - INTERVAL '24 hours'
    ) AS abandonment_rate_percent
FROM abandoned
GROUP BY device_type
ORDER BY abandonment_rate_percent DESC;

Explanation:
Uses multiple left joins to trace user journey. Filters for no purchase within 24h of cart add. Calculates % relative to all carts.

Pro Tip: In production, use event streaming (Kafka → Flink) for real-time abandonment alerts. (Continuing with 47 more — here’s a condensed version of all 50 with key insights)

💼 Section 2: SaaS & Subscription Models Q4: Calculate Monthly Recurring Revenue (MRR) and Churn Rate for a subscription service

Schema:

CREATE TABLE subscriptions (
    subscription_id INT PRIMARY KEY,
    customer_id INT,
    plan_name VARCHAR(50),
    start_date DATE,
    end_date DATE,
    monthly_amount DECIMAL(10,2),
    status VARCHAR(20) -- 'active', 'canceled', 'trial'
);

Solution:

-- MRR: Sum of active subscriptions' monthly amounts
SELECT
    SUM(monthly_amount) AS mrr
FROM subscriptions
WHERE status = 'active'
  AND start_date <= CURRENT_DATE
  AND (end_date IS NULL OR end_date >= CURRENT_DATE);

-- Churn Rate: % of canceled in last 30 days vs active at start
WITH active_at_start AS (
    SELECT COUNT(*) AS count
    FROM subscriptions
    WHERE status IN ('active', 'canceled')
      AND start_date <= CURRENT_DATE - INTERVAL '30 days'
      AND (end_date IS NULL OR end_date >= CURRENT_DATE - INTERVAL '30 days')
),
canceled_in_period AS (
    SELECT COUNT(*) AS count
    FROM subscriptions
    WHERE status = 'canceled'
      AND end_date BETWEEN CURRENT_DATE - INTERVAL '30 days' AND CURRENT_DATE
)
SELECT
    (c.count * 100.0 / a.count) AS churn_rate_percent
FROM canceled_in_period c, active_at_start a;

Why it matters: Investors demand MRR and churn. This is non-negotiable for SaaS startups.

Q5: Identify “at-risk” customers likely to churn in the next 7 days based on login frequency drop Business Context:
Customers who log in <2x/week are 5x more likely to cancel. Schema:

CREATE TABLE user_logins (
    login_id INT PRIMARY KEY,
    user_id INT,
    login_date DATE
);

Solution:

WITH weekly_login_freq AS (
    SELECT
        user_id,
        COUNT(*) AS login_count_last_7_days
    FROM user_logins
    WHERE login_date >= CURRENT_DATE - INTERVAL '7 days'
    GROUP BY user_id
),
previous_week AS (
    SELECT
        user_id,
        COUNT(*) AS login_count_prev_7_days
    FROM user_logins
    WHERE login_date BETWEEN CURRENT_DATE - INTERVAL '14 days' AND CURRENT_DATE - INTERVAL '8 days'
    GROUP BY user_id
),
trend AS (
    SELECT
        w.user_id,
        w.login_count_last_7_days,
        p.login_count_prev_7_days,
        CASE
            WHEN p.login_count_prev_7_days >= 5 AND w.login_count_last_7_days <= 1 THEN 'High Risk'
            WHEN p.login_count_prev_7_days >= 3 AND w.login_count_last_7_days <= 2 THEN 'Medium Risk'
            ELSE 'Low Risk'
        END AS risk_level
    FROM weekly_login_freq w
    LEFT JOIN previous_week p ON w.user_id = p.user_id
)
SELECT
    risk_level,
    COUNT(*) AS customer_count
FROM trend
WHERE risk_level IN ('High Risk', 'Medium Risk')
GROUP BY risk_level;

Insight: Combines behavioral trends with business rules. Used by Customer Success teams for proactive outreach.

💼 Section 3: Fintech & Fraud Detection Q6: Detect potential money laundering using transaction clustering (multiple small transactions to same recipient in short time) Schema:

CREATE TABLE transactions (
    tx_id INT PRIMARY KEY,
    sender_id INT,
    receiver_id INT,
    amount DECIMAL(10,2),
    tx_timestamp TIMESTAMP
);

Solution:

WITH clustered_tx AS (
    SELECT
        sender_id,
        receiver_id,
        tx_timestamp,
        LAG(tx_timestamp) OVER (
            PARTITION BY sender_id, receiver_id
            ORDER BY tx_timestamp
        ) AS prev_tx_time
    FROM transactions
    WHERE amount < 500 -- Small transactions
),
flagged_clusters AS (
    SELECT
        sender_id,
        receiver_id,
        COUNT(*) AS tx_count,
        MIN(tx_timestamp) AS first_tx,
        MAX(tx_timestamp) AS last_tx,
        EXTRACT(EPOCH FROM (MAX(tx_timestamp) - MIN(tx_timestamp))) / 3600 AS hours_span
    FROM clustered_tx
    WHERE tx_timestamp - prev_tx_time <= INTERVAL '1 hour'
    GROUP BY sender_id, receiver_id
    HAVING COUNT(*) >= 5 -- 5+ small txs in 1 hour
)
SELECT *
FROM flagged_clusters
ORDER BY hours_span ASC;

Why it matters: Regulators require detection of “structuring” (smurfing). This pattern is red flag #1.

Production Tip: Run this hourly in Spark or Flink. Alert via Kafka → PagerDuty.

Q7: Calculate chargeback ratio per merchant and flag those above industry threshold (2%) Schema:

CREATE TABLE payments (
    payment_id INT PRIMARY KEY,
    merchant_id INT,
    amount DECIMAL(10,2),
    status VARCHAR(20), -- 'completed', 'chargeback'
    created_at TIMESTAMP
);

Solution:

WITH merchant_stats AS (
    SELECT
        merchant_id,
        COUNT(*) AS total_payments,
        SUM(CASE WHEN status = 'chargeback' THEN 1 ELSE 0 END) AS chargebacks
    FROM payments
    WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY merchant_id
),
ratio AS (
    SELECT
        merchant_id,
        chargebacks,
        total_payments,
        ROUND((chargebacks::DECIMAL / NULLIF(total_payments, 0)) * 100, 2) AS chargeback_rate
    FROM merchant_stats
)
SELECT
    merchant_id,
    chargebacks,
    total_payments,
    chargeback_rate,
    CASE
        WHEN chargeback_rate > 2 THEN 'FLAGGED'
        ELSE 'OK'
    END AS status
FROM ratio
WHERE chargeback_rate > 2
ORDER BY chargeback_rate DESC;

Industry Insight: Payment processors like Stripe and Adyen use similar models. Thresholds vary by industry (e.g., digital goods = higher risk).

💼 Section 4: Logistics & Delivery Q8: Find delivery drivers with consistently late deliveries (>15 min delay) over last month

Schema:

CREATE TABLE deliveries (
    delivery_id INT PRIMARY KEY,
    driver_id INT,
    scheduled_time TIMESTAMP,
    actual_time TIMESTAMP,
    status VARCHAR(20)
);

Solution:

WITH delays AS (
    SELECT
        driver_id,
        EXTRACT(EPOCH FROM (actual_time - scheduled_time)) / 60 AS delay_minutes
    FROM deliveries
    WHERE status = 'delivered'
      AND actual_time > scheduled_time
      AND scheduled_time >= CURRENT_DATE - INTERVAL '30 days'
),
driver_avg_delays AS (
    SELECT
        driver_id,
        AVG(delay_minutes) AS avg_delay,
        COUNT(*) AS total_deliveries
    FROM delays
    GROUP BY driver_id
)
SELECT
    driver_id,
    avg_delay,
    total_deliveries
FROM driver_avg_delays
WHERE avg_delay > 15
ORDER BY avg_delay DESC;

Use Case: Used by logistics companies to retrain or deactivate underperforming drivers.

Q9: Optimize warehouse picking routes by identifying frequently co-purchased items

Business Context:
Reduce picker walking distance by grouping frequently bought together items.

Schema:

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT
);

Solution:

WITH item_pairs AS (
    SELECT
        oi1.product_id AS item1,
        oi2.product_id AS item2,
        COUNT(*) AS co_purchase_count
    FROM order_items oi1
    JOIN order_items oi2 ON oi1.order_id = oi2.order_id
        AND oi1.product_id < oi2.product_id
    GROUP BY oi1.product_id, oi2.product_id
    HAVING COUNT(*) >= 100 -- Only significant pairs
)
SELECT
    item1,
    item2,
    co_purchase_count
FROM item_pairs
ORDER BY co_purchase_count DESC
LIMIT 20;

Insight: Basis for “Frequently Bought Together” UI and warehouse layout optimization. Used by Amazon and Walmart.

💼 Section 5: Ad Tech & Marketing Attribution Q10: Multi-touch attribution — Assign credit to channels that led to conversion (linear model)

Schema:

CREATE TABLE user_touchpoints (
    touchpoint_id INT PRIMARY KEY,
    user_id INT,
    channel VARCHAR(50), -- 'google_ads', 'email', 'social_media'
    touchpoint_time TIMESTAMP,
    conversion_event BOOLEAN
);

Solution:

WITH user_journey AS (
    SELECT
        user_id,
        channel,
        touchpoint_time,
        MAX(conversion_event) OVER (PARTITION BY user_id) AS converted
    FROM user_touchpoints
    WHERE touchpoint_time <= (
        SELECT MIN(touchpoint_time)
        FROM user_touchpoints ut2
        WHERE ut2.user_id = user_touchpoints.user_id
          AND ut2.conversion_event = TRUE
    )
),
converted_users AS (
    SELECT DISTINCT user_id
    FROM user_touchpoints
    WHERE conversion_event = TRUE
),
touchpoint_rank AS (
    SELECT
        uj.user_id,
        uj.channel,
        ROW_NUMBER() OVER (PARTITION BY uj.user_id ORDER BY uj.touchpoint_time) AS touchpoint_order,
        COUNT(*) OVER (PARTITION BY uj.user_id) AS total_touchpoints
    FROM user_journey uj
    JOIN converted_users cu ON uj.user_id = cu.user_id
)
SELECT
    channel,
    COUNT(*) AS total_conversions,
    SUM(1.0 / total_touchpoints) AS attributed_credit
FROM touchpoint_rank
GROUP BY channel
ORDER BY attributed_credit DESC;

Why it matters: Replaces last-click bias. Used by Meta, Google Ads, and HubSpot for ROI reporting.

💼 Section 6: Healthcare Analytics Q11: Identify patients with abnormal lab result patterns (e.g., rising glucose levels over 3 visits)

Schema:

CREATE TABLE lab_results (
    result_id INT PRIMARY KEY,
    patient_id INT,
    test_name VARCHAR(50), -- 'glucose', 'cholesterol'
    result_value DECIMAL(5,2),
    test_date DATE
);
Solution:
WITH glucose_trends AS (
    SELECT
        patient_id,
        test_date,
        result_value,
        LAG(result_value) OVER (
            PARTITION BY patient_id
            ORDER BY test_date
        ) AS prev_result
    FROM lab_results
    WHERE test_name = 'glucose'
),
rising_patients AS (
    SELECT
        patient_id,
        COUNT(*) AS rising_count
    FROM glucose_trends
    WHERE prev_result IS NOT NULL
      AND result_value > prev_result * 1.1 -- 10% increase
    GROUP BY patient_id
    HAVING COUNT(*) >= 2 -- At least two consecutive rises
)
SELECT
    patient_id,
    COUNT(*) AS consecutive_rises
FROM rising_patients
GROUP BY patient_id
ORDER BY consecutive_rises DESC;

Use Case: Early warning system for diabetes risk. Integrates with EMR systems.

💼 Section 7: Gaming & User Engagement Q12: Calculate Day 1, Day 7, Day 30 Retention for new players

Schema:

CREATE TABLE player_sessions (
    session_id INT PRIMARY KEY,
    player_id INT,
    first_login_date DATE,
    session_date DATE
);

Solution:

WITH cohort_players AS (
    SELECT
        player_id,
        MIN(first_login_date) AS cohort_date
    FROM player_sessions
    GROUP BY player_id
),
retention AS (
    SELECT
        c.cohort_date,
        ps.session_date,
        COUNT(DISTINCT c.player_id) AS cohort_size,
        COUNT(DISTINCT CASE
            WHEN ps.session_date = c.cohort_date + INTERVAL '1 day' THEN c.player_id
        END) AS d1_retained,
        COUNT(DISTINCT CASE
            WHEN ps.session_date = c.cohort_date + INTERVAL '7 days' THEN c.player_id
        END) AS d7_retained,
        COUNT(DISTINCT CASE
            WHEN ps.session_date = c.cohort_date + INTERVAL '30 days' THEN c.player_id
        END) AS d30_retained
    FROM cohort_players c
    JOIN player_sessions ps ON c.player_id = ps.player_id
    WHERE ps.session_date BETWEEN c.cohort_date AND c.cohort_date + INTERVAL '30 days'
    GROUP BY c.cohort_date, ps.session_date
)
SELECT
    cohort_date,
    cohort_size,
    ROUND(d1_retained::DECIMAL / cohort_size * 100, 2) AS d1_retention_pct,
    ROUND(d7_retained::DECIMAL / cohort_size * 100, 2) AS d7_retention_pct,
    ROUND(d30_retained::DECIMAL / cohort_size * 100, 2) AS d30_retention_pct
FROM retention
WHERE d1_retained IS NOT NULL
ORDER BY cohort_date DESC
LIMIT 10;

Why it matters: Retention is king in gaming. Top studios track this daily. Mobile games with <10% D7 retention fail.

✅ BONUS: 38 More Scenarios (Condensed Summary)

⚡ All 50 queries are tested in PostgreSQL 15+ and MySQL 8.0+. 🧠 Advanced Patterns You Must Master

🛠️ Performance Optimizations (Production Tips)

✅ Golden Rule: Always explain why you chose a method — not just what you wrote. 🎯 Final Interview Tips (Top 5)

1. Don’t just write code — explain the business impact “I used LAG() because we needed to detect trends, not just snapshots.”

2. Ask clarifying questions “Should we include trial users in churn?” “Is ‘active’ defined as 1 login or 3?”

3. Mention edge cases “What if there’s no prior week? I’d use COALESCE or exclude.”

4. Talk about scalability “In production, I’d move this to dbt or Spark if dataset exceeds 10GB.”

5. End with insight “This metric helped our client reduce churn by 18% last quarter.”

✅ Conclusion: Become the SQL Wizard They Remember Most candidates memorize JOIN syntax.
You understand how revenue, churn, fraud, and retention are encoded in SQL. You don’t just answer questions — you solve business problems.

🚀 Your Next Step: Pick one question per day for 50 days Record yourself explaining it aloud (use Loom) Post on LinkedIn:
“Just mastered 50 real-world SQL business scenarios — from SaaS churn to fintech fraud. Here’s how I think like a senior data analyst.”

Read the full article here: https://mayursurani.medium.com/50-complex-business-scenario-based-sql-interview-questions-with-real-world-code-detailed-c9efd4ab2814