Scenario-Based SQL Interview Questions: Real Business Cases

The Problem Situation

An e-commerce business wants to track its checkout funnel efficiency. Specifically, the product manager needs to identify users who added items to their shopping cart but did not complete a purchase within the same session.

Table Schema: events

• event_id (INT)
• user_id (INT)
• session_id (VARCHAR)
• event_name (VARCHAR) — 'view_item', 'add_to_cart', 'checkout_start', 'purchase'
• created_at (TIMESTAMP)

Quick Definition & Formula

The cart abandonment rate is calculated as:

The Solution Query

WITH session_activity AS (
    SELECT 
        DATE(created_at) AS activity_date,
        session_id,
        MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS added_to_cart,
        MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS purchased
    FROM events
    GROUP BY DATE(created_at), session_id
)
SELECT 
    activity_date,
    SUM(added_to_cart) AS total_cart_add_sessions,
    SUM(CASE WHEN added_to_cart = 1 AND purchased = 0 THEN 1 ELSE 0 END) AS abandoned_sessions,
    ROUND(
        100.0 * SUM(CASE WHEN added_to_cart = 1 AND purchased = 0 THEN 1 ELSE 0 END) 
        / NULLIF(SUM(added_to_cart), 0), 2
    ) AS cart_abandonment_rate_pct
FROM session_activity
WHERE added_to_cart = 1
GROUP BY activity_date
ORDER BY activity_date DESC;

The Problem Situation

A subscription-based SaaS startup needs to track customer churn. The executive team wants a clear, month-over-month view showing how many active users cancelled their plans, indicating a loss in recurring revenue.

Table Schema: subscriptions

• subscription_id (INT)
• user_id (INT)
• start_date (DATE)
• end_date (DATE) — NULL if the subscription is still active

The Solution Query

WITH RECURSIVE months AS (
    SELECT MIN(DATE_TRUNC('month', start_date)) AS month_date FROM subscriptions
    UNION ALL
    SELECT month_date + INTERVAL '1 month' FROM months 
    WHERE month_date < ( SELECT MAX(DATE_TRUNC('month', start_date)) FROM subscriptions )
),
monthly_active_users AS (
    SELECT 
        m.month_date,
        s.user_id
    FROM months m
    JOIN subscriptions s 
      ON s.start_date <= m.month_date 
     AND (s.end_date IS NULL OR s.end_date > m.month_date)
    GROUP BY m.month_date, s.user_id
),
retention_matrix AS (
    SELECT 
        month_date,
        COUNT(user_id) AS active_users,
        LAG(COUNT(user_id)) OVER (ORDER BY month_date) AS previous_month_active_users,
        COUNT(CASE WHEN user_id NOT IN (
            SELECT user_id FROM monthly_active_users mau2 WHERE mau2.month_date = mau1.month_date + INTERVAL '1 month'
        ) THEN 1 END) AS churned_users_this_month
    FROM monthly_active_users mau1
    GROUP BY month_date
)
SELECT 
    month_date,
    active_users,
    previous_month_active_users,
    churned_users_this_month,
    ROUND(
        100.0 * churned_users_this_month / NULLIF(active_users, 0), 2
    ) AS monthly_churn_rate_pct
FROM retention_matrix
ORDER BY month_date DESC;

The Problem Situation

A fintech platform notices a spike in coordinated financial fraud. Bad actors are executing rapid transactions across multiple accounts within incredibly tight windows to exploit systemic clearing delays.

Table Schema: transactions

• transaction_id (INT)
• user_id (INT)
• amount (DECIMAL)
• transaction_time (TIMESTAMP)

The Solution Query

WITH sequential_transactions AS (
    SELECT 
        user_id,
        transaction_id AS current_tx_id,
        transaction_time AS current_tx_time,
        LEAD(transaction_id) OVER (PARTITION BY user_id ORDER BY transaction_time) AS next_tx_id,
        LEAD(transaction_time) OVER (PARTITION BY user_id ORDER BY transaction_time) AS next_tx_time
    FROM transactions
)
SELECT 
    user_id,
    current_tx_id,
    next_tx_id,
    current_tx_time,
    next_tx_time,
    (next_tx_time - current_tx_time) AS transaction_time_gap
FROM sequential_transactions
WHERE next_tx_time IS NOT NULL
  AND next_tx_time <= current_tx_time + INTERVAL '10 minutes'
ORDER BY user_id, current_tx_time;