Swiggy Business Analyst Interview Questions and Answers 2026

WITH delivery_times AS (
    SELECT
        city,
        order_id,
        TIMESTAMPDIFF(MINUTE, order_placed_at, delivered_at) AS delivery_minutes
    FROM deliveries
    WHERE delivered_at IS NOT NULL
),
city_percentiles AS (
    SELECT
        city,
        PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY delivery_minutes) AS p50,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY delivery_minutes) AS p75,
        PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY delivery_minutes) AS p90,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY delivery_minutes) AS p95
    FROM delivery_times
    GROUP BY city
)
SELECT
    city,
    ROUND(p50, 1) AS p50_minutes,
    ROUND(p75, 1) AS p75_minutes,
    ROUND(p90, 1) AS p90_minutes,
    ROUND(p95, 1) AS p95_minutes,
    CASE WHEN p90 > 45 THEN 'FLAG: p90 > 45 min' ELSE 'OK' END AS sla_flag
FROM city_percentiles
ORDER BY p90 DESC;

WITH restaurant_stats AS (
    SELECT
        restaurant_id,
        COUNT(order_id)                                       AS total_orders,
        COUNT(CASE WHEN status = 'accepted'               THEN 1 END)  AS accepted_count,
        COUNT(CASE WHEN status = 'cancelled_by_restaurant' THEN 1 END) AS cancelled_count,
        COUNT(DISTINCT DATE_FORMAT(order_date, '%Y-%u'))      AS total_weeks
    FROM orders
    GROUP BY restaurant_id
)
SELECT
    restaurant_id,
    total_orders,
    ROUND(100.0 * accepted_count / NULLIF(total_orders, 0), 1)     AS acceptance_rate_pct,
    ROUND(100.0 * cancelled_count / NULLIF(total_orders, 0), 1)    AS cancellation_rate_pct,
    ROUND(1.0 * total_orders / NULLIF(total_weeks, 0), 1)          AS avg_weekly_orders,
    CASE
        WHEN (100.0 * accepted_count / NULLIF(total_orders, 0)) < 80
          OR (
               (100.0 * cancelled_count / NULLIF(total_orders, 0)) > 10
               AND (1.0 * total_orders / NULLIF(total_weeks, 0)) > 50
             )
        THEN 'FLAG'
        ELSE 'OK'
    END AS health_flag
FROM restaurant_stats
ORDER BY health_flag DESC, cancellation_rate_pct DESC;

-- (a) Top 5 cancellation reasons by volume
SELECT
    cancel_reason,
    COUNT(order_id)                                      AS cancellations,
    ROUND(100.0 * COUNT(order_id) / SUM(COUNT(order_id)) OVER (), 1) AS pct_of_total
FROM orders
WHERE cancelled_at IS NOT NULL
GROUP BY cancel_reason
ORDER BY cancellations DESC
LIMIT 5;

-- (b) Time-to-cancel distribution (minutes buckets)
SELECT
    CASE
        WHEN TIMESTAMPDIFF(MINUTE, order_placed_at, cancelled_at) <= 2  THEN '0-2 min'
        WHEN TIMESTAMPDIFF(MINUTE, order_placed_at, cancelled_at) <= 5  THEN '3-5 min'
        WHEN TIMESTAMPDIFF(MINUTE, order_placed_at, cancelled_at) <= 10 THEN '6-10 min'
        WHEN TIMESTAMPDIFF(MINUTE, order_placed_at, cancelled_at) <= 20 THEN '11-20 min'
        ELSE '20+ min'
    END AS cancel_bucket,
    COUNT(order_id) AS cancellations
FROM orders
WHERE cancelled_at IS NOT NULL
  AND cancel_by = 'user'
GROUP BY cancel_bucket
ORDER BY MIN(TIMESTAMPDIFF(MINUTE, order_placed_at, cancelled_at));

-- (c) Restaurants with highest user-initiated cancellation rate
SELECT
    restaurant_id,
    COUNT(order_id)                                                          AS total_orders,
    COUNT(CASE WHEN cancel_by = 'user' THEN 1 END)                          AS user_cancels,
    ROUND(100.0 * COUNT(CASE WHEN cancel_by = 'user' THEN 1 END)
          / NULLIF(COUNT(order_id), 0), 1)                                   AS user_cancel_rate_pct
FROM orders
GROUP BY restaurant_id
HAVING COUNT(order_id) >= 50
ORDER BY user_cancel_rate_pct DESC
LIMIT 10;

WITH first_order AS (
    SELECT
        user_id,
        MIN(order_date) AS first_order_date
    FROM orders
    GROUP BY user_id
),
orders_with_segment AS (
    SELECT
        o.order_id,
        o.user_id,
        DATE_FORMAT(o.order_date, '%Y-%m')   AS order_month,
        o.order_value,
        o.promo_discount,
        CASE
            WHEN DATE_FORMAT(o.order_date, '%Y-%m')
               = DATE_FORMAT(f.first_order_date, '%Y-%m')
            THEN 'New User'
            ELSE 'Returning User'
        END AS user_segment
    FROM orders o
    JOIN first_order f ON o.user_id = f.user_id
)
SELECT
    order_month,
    user_segment,
    COUNT(DISTINCT user_id)               AS unique_users,
    COUNT(order_id)                        AS total_orders,
    ROUND(SUM(order_value), 0)             AS total_revenue,
    ROUND(AVG(order_value), 1)             AS avg_order_value,
    ROUND(AVG(promo_discount), 1)          AS avg_promo_discount
FROM orders_with_segment
GROUP BY order_month, user_segment
ORDER BY order_month, user_segment;

WITH daily_rider_stats AS (
    SELECT
        d.rider_id,
        r.city,
        DATE(d.pickup_time)                                       AS work_date,
        COUNT(d.delivery_id)                                      AS total_deliveries,
        TIMESTAMPDIFF(MINUTE, MIN(d.pickup_time), MAX(d.delivery_time))
            / 60.0                                                AS hours_worked,
        AVG(CASE
            WHEN TIMESTAMPDIFF(MINUTE, d.pickup_time, d.delivery_time) < 30 THEN 1.0
            ELSE 0.0
        END)                                                      AS on_time_rate,
        AVG(d.km_distance)                                        AS avg_km_per_delivery
    FROM deliveries d
    JOIN riders r ON d.rider_id = r.rider_id
    GROUP BY d.rider_id, r.city, DATE(d.pickup_time)
),
rider_scores AS (
    SELECT
        rider_id,
        city,
        work_date,
        total_deliveries,
        ROUND(total_deliveries / NULLIF(hours_worked, 0), 2)     AS deliveries_per_hour,
        ROUND(on_time_rate * 100, 1)                             AS on_time_pct,
        ROUND(avg_km_per_delivery, 2)                            AS avg_km,
        -- Composite score: 40% deliveries/hr + 40% on-time + 20% distance efficiency
        ROUND(
            0.40 * LEAST(total_deliveries / NULLIF(hours_worked, 0) / 5.0, 1.0)
          + 0.40 * on_time_rate
          + 0.20 * (1.0 - LEAST(avg_km_per_delivery / 10.0, 1.0)),
        3) AS efficiency_score
    FROM daily_rider_stats
)
SELECT
    rider_id,
    city,
    work_date,
    deliveries_per_hour,
    on_time_pct,
    avg_km,
    efficiency_score,
    RANK() OVER (PARTITION BY city, work_date ORDER BY efficiency_score DESC) AS city_rank
FROM rider_scores
ORDER BY city, work_date, city_rank;