Flipkart Business Analyst Interview Questions and Answers 2026

WITH curr_month AS (
    SELECT
        seller_id,
        SUM(gmv) AS gmv_curr
    FROM orders
    WHERE DATE_TRUNC('month', order_date) = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
      AND status = 'delivered'
    GROUP BY seller_id
),
prev_month AS (
    SELECT
        seller_id,
        SUM(gmv) AS gmv_prev
    FROM orders
    WHERE DATE_TRUNC('month', order_date) = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '2 months')
      AND status = 'delivered'
    GROUP BY seller_id
),
bridge AS (
    SELECT
        COALESCE(c.seller_id, p.seller_id) AS seller_id,
        COALESCE(c.gmv_curr, 0)            AS gmv_curr,
        COALESCE(p.gmv_prev, 0)            AS gmv_prev,
        CASE
            WHEN p.seller_id IS NULL THEN 'new_seller'
            WHEN c.seller_id IS NULL THEN 'lost_seller'
            ELSE 'existing_seller'
        END AS seller_type
    FROM curr_month c
    FULL OUTER JOIN prev_month p USING (seller_id)
)
SELECT
    seller_type,
    COUNT(DISTINCT seller_id)          AS seller_count,
    SUM(gmv_curr - gmv_prev)           AS gmv_contribution
FROM bridge
GROUP BY seller_type
ORDER BY seller_type;

WITH quarterly_stats AS (
    SELECT
        o.seller_id,
        s.category,
        DATE_TRUNC('quarter', o.date) AS quarter_start,
        SUM(o.gmv)                    AS total_gmv,
        ROUND(100.0 * SUM(o.returns) / NULLIF(SUM(o.gmv), 0), 2) AS return_rate_pct
    FROM orders o
    JOIN sellers s ON o.seller_id = s.seller_id
    WHERE o.date >= DATE_TRUNC('quarter', CURRENT_DATE - INTERVAL '6 months')
    GROUP BY o.seller_id, s.category, DATE_TRUNC('quarter', o.date)
),
ranked AS (
    SELECT
        *,
        PERCENT_RANK() OVER (
            PARTITION BY category, quarter_start
            ORDER BY total_gmv DESC
        ) AS gmv_pct_rank
    FROM quarterly_stats
),
curr_q AS (
    SELECT * FROM ranked
    WHERE quarter_start = DATE_TRUNC('quarter', CURRENT_DATE - INTERVAL '3 months')
),
prev_q AS (
    SELECT seller_id, return_rate_pct AS prev_return_rate FROM ranked
    WHERE quarter_start = DATE_TRUNC('quarter', CURRENT_DATE - INTERVAL '6 months')
)
SELECT
    c.seller_id,
    c.category,
    c.total_gmv,
    c.return_rate_pct    AS curr_return_rate,
    p.prev_return_rate,
    c.return_rate_pct - p.prev_return_rate AS return_rate_delta,
    'HIGH RISK'          AS flag
FROM curr_q c
JOIN prev_q p ON c.seller_id = p.seller_id
WHERE c.gmv_pct_rank <= 0.10
  AND (c.return_rate_pct - p.prev_return_rate) > 15
ORDER BY return_rate_delta DESC;

WITH funnel AS (
    SELECT
        category,
        COUNT(DISTINCT CASE WHEN event_type = 'product_view'    THEN session_id END) AS step1_view,
        COUNT(DISTINCT CASE WHEN event_type = 'add_to_cart'     THEN session_id END) AS step2_cart,
        COUNT(DISTINCT CASE WHEN event_type = 'checkout_start'  THEN session_id END) AS step3_checkout,
        COUNT(DISTINCT CASE WHEN event_type = 'payment_done'    THEN session_id END) AS step4_payment
    FROM events
    WHERE ts >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY category
),
conversion_rates AS (
    SELECT
        category,
        step1_view,
        step2_cart,
        step3_checkout,
        step4_payment,
        ROUND(100.0 * step2_cart     / NULLIF(step1_view,    0), 1) AS view_to_cart_pct,
        ROUND(100.0 * step3_checkout / NULLIF(step2_cart,    0), 1) AS cart_to_checkout_pct,
        ROUND(100.0 * step4_payment  / NULLIF(step3_checkout,0), 1) AS checkout_to_payment_pct
    FROM funnel
),
drop_offs AS (
    SELECT
        category,
        'view_to_cart'          AS step,
        (100 - view_to_cart_pct)       AS drop_off_pct
    FROM conversion_rates
    UNION ALL
    SELECT category, 'cart_to_checkout',
        (100 - cart_to_checkout_pct)   AS drop_off_pct
    FROM conversion_rates
    UNION ALL
    SELECT category, 'checkout_to_payment',
        (100 - checkout_to_payment_pct) AS drop_off_pct
    FROM conversion_rates
),
ranked_drops AS (
    SELECT
        *,
        RANK() OVER (PARTITION BY category ORDER BY drop_off_pct DESC) AS drop_rank
    FROM drop_offs
)
SELECT category, step AS biggest_dropoff_step, drop_off_pct
FROM ranked_drops
WHERE drop_rank = 1
ORDER BY drop_off_pct DESC;

WITH first_purchase AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(order_date)) AS cohort_month
    FROM orders
    GROUP BY user_id
),
user_activity AS (
    SELECT
        fp.user_id,
        fp.cohort_month,
        DATE_TRUNC('month', o.order_date)   AS activity_month,
        DATEDIFF('month', fp.cohort_month,
            DATE_TRUNC('month', o.order_date)) AS months_since_first
    FROM first_purchase fp
    JOIN orders o ON fp.user_id = o.user_id
    WHERE DATE_TRUNC('month', o.order_date) >= fp.cohort_month
),
cohort_size AS (
    SELECT cohort_month, COUNT(DISTINCT user_id) AS total_users
    FROM first_purchase
    GROUP BY cohort_month
)
SELECT
    ua.cohort_month,
    cs.total_users,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN months_since_first = 0 THEN ua.user_id END) / cs.total_users, 1) AS month_0,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN months_since_first = 1 THEN ua.user_id END) / cs.total_users, 1) AS month_1,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN months_since_first = 2 THEN ua.user_id END) / cs.total_users, 1) AS month_2,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN months_since_first = 3 THEN ua.user_id END) / cs.total_users, 1) AS month_3,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN months_since_first = 4 THEN ua.user_id END) / cs.total_users, 1) AS month_4,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN months_since_first = 5 THEN ua.user_id END) / cs.total_users, 1) AS month_5,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN months_since_first = 6 THEN ua.user_id END) / cs.total_users, 1) AS month_6
FROM user_activity ua
JOIN cohort_size cs ON ua.cohort_month = cs.cohort_month
GROUP BY ua.cohort_month, cs.total_users
ORDER BY ua.cohort_month;

WITH sla_calc AS (
    SELECT
        warehouse_city,
        dest_city,
        COUNT(*)                                            AS total_shipments,
        COUNT(CASE WHEN actual_delivery_date > promised_delivery_date THEN 1 END)
                                                           AS breached_count,
        ROUND(
            100.0 * COUNT(CASE WHEN actual_delivery_date > promised_delivery_date THEN 1 END)
            / NULLIF(COUNT(*), 0), 1
        )                                                  AS breach_rate_pct,
        ROUND(
            AVG(CASE
                WHEN actual_delivery_date > promised_delivery_date
                THEN DATEDIFF('day', promised_delivery_date, actual_delivery_date)
            END), 1
        )                                                  AS avg_delay_days
    FROM shipments
    WHERE actual_delivery_date IS NOT NULL
    GROUP BY warehouse_city, dest_city
),
filtered AS (
    SELECT
        *,
        RANK() OVER (ORDER BY breach_rate_pct DESC, avg_delay_days DESC) AS breach_rank
    FROM sla_calc
    WHERE breach_rate_pct > 20
      AND avg_delay_days  > 2
)
SELECT
    breach_rank,
    warehouse_city,
    dest_city,
    total_shipments,
    breached_count,
    breach_rate_pct,
    avg_delay_days
FROM filtered
WHERE breach_rank <= 10
ORDER BY breach_rank;