SQL Interview Questions for E-Commerce and Marketplace Data Science Roles

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

WITH customer_spend AS (
    SELECT
        customer_id,
        SUM(total_amount) AS total_revenue
    FROM orders
    WHERE order_date >= '2024-01-01'
      AND order_date < '2025-01-01'
    GROUP BY customer_id
),
ranked AS (
    SELECT
        customer_id,
        total_revenue,
        DENSE_RANK() OVER (ORDER BY total_revenue DESC) AS rnk
    FROM customer_spend
)
SELECT customer_id, total_revenue, rnk
FROM ranked
WHERE rnk <= 5
ORDER BY rnk;

CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY,
    customer_id    INT,
    purchase_date  DATE,
    amount         DECIMAL(10,2)
);

WITH customer_counts AS (
    SELECT
        customer_id,
        COUNT(DISTINCT purchase_date) AS purchase_days
    FROM transactions
    WHERE purchase_date >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY customer_id
)
SELECT
    ROUND(
        100.0 * COUNT(CASE WHEN purchase_days > 1 THEN 1 END)
        / COUNT(*),
        2
    ) AS repeat_purchase_rate_pct
FROM customer_counts;

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

SELECT
    customer_id,
    order_id,
    order_date,
    order_total,
    SUM(order_total) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_spend
FROM orders
ORDER BY customer_id, order_date;

CREATE TABLE product_spend (
    category         VARCHAR(50),
    product          VARCHAR(100),
    user_id          INT,
    spend            DECIMAL(10,2),
    transaction_date DATE
);

WITH category_product_totals AS (
    SELECT
        category,
        product,
        SUM(spend) AS total_spend
    FROM product_spend
    WHERE transaction_date >= '2024-01-01'
      AND transaction_date < '2025-01-01'
    GROUP BY category, product
),
ranked AS (
    SELECT
        category,
        product,
        total_spend,
        RANK() OVER (
            PARTITION BY category
            ORDER BY total_spend DESC
        ) AS product_rank
    FROM category_product_totals
)
SELECT category, product, total_spend
FROM ranked
WHERE product_rank <= 2
ORDER BY category, product_rank;

CREATE TABLE daily_inventory (
    product_id    INT,
    snapshot_date DATE,
    units_on_hand INT
);

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

WITH trailing_sales AS (
    SELECT
        product_id,
        SUM(quantity) AS units_sold_90d
    FROM order_items
    WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY product_id
),
avg_inventory AS (
    SELECT
        product_id,
        AVG(units_on_hand) AS avg_units_on_hand
    FROM daily_inventory
    WHERE snapshot_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY product_id
)
SELECT
    s.product_id,
    s.units_sold_90d,
    i.avg_units_on_hand,
    ROUND(s.units_sold_90d::DECIMAL / NULLIF(i.avg_units_on_hand, 0), 2)
        AS turnover_ratio
FROM trailing_sales s
JOIN avg_inventory i ON s.product_id = i.product_id
ORDER BY turnover_ratio DESC;

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

SELECT
    a.product_id AS product_a,
    b.product_id AS product_b,
    COUNT(*) AS times_bought_together
FROM order_items a
JOIN order_items b
    ON a.order_id = b.order_id
    AND a.product_id < b.product_id
GROUP BY a.product_id, b.product_id
ORDER BY times_bought_together DESC
LIMIT 10;

CREATE TABLE user_events (
    event_id   INT PRIMARY KEY,
    user_id    INT,
    event_type VARCHAR(20),
    event_date DATE
);
-- event_type values: 'page_view', 'add_to_cart', 'begin_checkout', 'purchase'

WITH funnel_counts AS (
    SELECT
        COUNT(DISTINCT CASE WHEN event_type = 'page_view'
            THEN user_id END) AS page_views,
        COUNT(DISTINCT CASE WHEN event_type = 'add_to_cart'
            THEN user_id END) AS add_to_carts,
        COUNT(DISTINCT CASE WHEN event_type = 'begin_checkout'
            THEN user_id END) AS checkouts,
        COUNT(DISTINCT CASE WHEN event_type = 'purchase'
            THEN user_id END) AS purchases
    FROM user_events
)
SELECT
    page_views,
    add_to_carts,
    ROUND(100.0 * add_to_carts / NULLIF(page_views, 0), 2)
        AS view_to_cart_pct,
    checkouts,
    ROUND(100.0 * checkouts / NULLIF(add_to_carts, 0), 2)
        AS cart_to_checkout_pct,
    purchases,
    ROUND(100.0 * purchases / NULLIF(checkouts, 0), 2)
        AS checkout_to_purchase_pct
FROM funnel_counts;

CREATE TABLE user_events (
    event_id   INT PRIMARY KEY,
    user_id    INT,
    event_type VARCHAR(20),
    event_date DATE
);

WITH daily_carts AS (
    SELECT DISTINCT user_id, event_date
    FROM user_events
    WHERE event_type = 'add_to_cart'
),
daily_purchases AS (
    SELECT DISTINCT user_id, event_date
    FROM user_events
    WHERE event_type = 'purchase'
),
cart_status AS (
    SELECT
        c.event_date,
        c.user_id,
        CASE WHEN p.user_id IS NULL THEN 1 ELSE 0 END AS abandoned
    FROM daily_carts c
    LEFT JOIN daily_purchases p
        ON c.user_id = p.user_id
        AND c.event_date = p.event_date
)
SELECT
    TO_CHAR(event_date, 'Day') AS day_of_week,
    EXTRACT(DOW FROM event_date) AS day_num,
    COUNT(*) AS total_carts,
    SUM(abandoned) AS abandoned_carts,
    ROUND(100.0 * SUM(abandoned) / COUNT(*), 2) AS abandonment_rate_pct
FROM cart_status
GROUP BY TO_CHAR(event_date, 'Day'), EXTRACT(DOW FROM event_date)
ORDER BY day_num;

CREATE TABLE session_events (
    event_id    INT PRIMARY KEY,
    session_id  VARCHAR(36),
    user_id     INT,
    event_type  VARCHAR(20),
    event_ts    TIMESTAMP
);

WITH session_starts AS (
    SELECT
        session_id,
        user_id,
        MIN(event_ts) AS session_start
    FROM session_events
    WHERE event_type = 'page_view'
    GROUP BY session_id, user_id
),
session_purchases AS (
    SELECT
        session_id,
        MIN(event_ts) AS purchase_ts
    FROM session_events
    WHERE event_type = 'purchase'
    GROUP BY session_id
)
SELECT
    COUNT(DISTINCT s.session_id) AS total_sessions,
    COUNT(DISTINCT CASE
        WHEN p.purchase_ts IS NOT NULL
         AND p.purchase_ts <= s.session_start + INTERVAL '30 minutes'
        THEN s.session_id
    END) AS converted_sessions,
    ROUND(100.0 *
        COUNT(DISTINCT CASE
            WHEN p.purchase_ts IS NOT NULL
             AND p.purchase_ts <= s.session_start + INTERVAL '30 minutes'
            THEN s.session_id
        END)
        / COUNT(DISTINCT s.session_id),
    2) AS conversion_rate_pct
FROM session_starts s
LEFT JOIN session_purchases p
    ON s.session_id = p.session_id;

CREATE TABLE marketplace_orders (
    order_id    INT PRIMARY KEY,
    seller_id   INT,
    buyer_id    INT,
    order_date  DATE,
    ship_date   DATE,
    order_total DECIMAL(10,2)
);

SELECT
    seller_id,
    COUNT(*) AS total_orders,
    ROUND(AVG(ship_date - order_date), 2) AS avg_fulfillment_days,
    ROUND(
        PERCENTILE_CONT(0.5) WITHIN GROUP (
            ORDER BY ship_date - order_date
        )::NUMERIC, 2
    ) AS median_fulfillment_days
FROM marketplace_orders
WHERE ship_date IS NOT NULL
GROUP BY seller_id
HAVING COUNT(*) >= 10
ORDER BY avg_fulfillment_days ASC
LIMIT 10;

CREATE TABLE seller_reviews (
    review_id  INT PRIMARY KEY,
    seller_id  INT,
    buyer_id   INT,
    rating     DECIMAL(2,1),
    review_date DATE
);

WITH seller_avg AS (
    SELECT
        seller_id,
        COUNT(*) AS review_count,
        ROUND(AVG(rating), 2) AS avg_rating
    FROM seller_reviews
    GROUP BY seller_id
    HAVING COUNT(*) >= 5
)
SELECT
    CASE
        WHEN avg_rating >= 4.5 THEN 'Excellent'
        WHEN avg_rating >= 3.5 THEN 'Good'
        ELSE 'Needs Improvement'
    END AS tier,
    COUNT(*) AS seller_count,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct_of_sellers
FROM seller_avg
GROUP BY
    CASE
        WHEN avg_rating >= 4.5 THEN 'Excellent'
        WHEN avg_rating >= 3.5 THEN 'Good'
        ELSE 'Needs Improvement'
    END
ORDER BY
    CASE tier
        WHEN 'Excellent' THEN 1
        WHEN 'Good' THEN 2
        ELSE 3
    END;

CREATE TABLE daily_seller_revenue (
    seller_id INT,
    rev_date  DATE,
    revenue   DECIMAL(10,2)
);

SELECT
    seller_id,
    rev_date,
    revenue,
    ROUND(
        AVG(revenue) OVER (
            PARTITION BY seller_id
            ORDER BY rev_date
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ), 2
    ) AS rolling_7d_avg
FROM daily_seller_revenue
ORDER BY seller_id, rev_date;

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

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', order_date) AS revenue_month,
        SUM(total_amount) AS monthly_total
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    revenue_month,
    monthly_total,
    LAG(monthly_total) OVER (ORDER BY revenue_month) AS prev_month_total,
    ROUND(
        100.0 * (monthly_total - LAG(monthly_total) OVER (ORDER BY revenue_month))
        / NULLIF(LAG(monthly_total) OVER (ORDER BY revenue_month), 0),
        2
    ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY revenue_month;

CREATE TABLE user_transactions (
    transaction_id   INT PRIMARY KEY,
    product_id       INT,
    spend            DECIMAL(10,2),
    transaction_date DATE
);

WITH yearly_spend AS (
    SELECT
        EXTRACT(YEAR FROM transaction_date) AS txn_year,
        product_id,
        SUM(spend) AS annual_spend
    FROM user_transactions
    GROUP BY EXTRACT(YEAR FROM transaction_date), product_id
)
SELECT
    txn_year,
    product_id,
    annual_spend AS curr_year_spend,
    LAG(annual_spend) OVER (
        PARTITION BY product_id ORDER BY txn_year
    ) AS prev_year_spend,
    ROUND(
        100.0 * (annual_spend - LAG(annual_spend) OVER (
            PARTITION BY product_id ORDER BY txn_year
        ))
        / NULLIF(LAG(annual_spend) OVER (
            PARTITION BY product_id ORDER BY txn_year
        ), 0),
        2
    ) AS yoy_growth_pct
FROM yearly_spend
ORDER BY product_id, txn_year;

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT,
    order_date  DATE
);

WITH first_purchase AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', MIN(order_date)) AS cohort_month
    FROM orders
    GROUP BY customer_id
),
subsequent_orders AS (
    SELECT
        fp.cohort_month,
        fp.customer_id,
        DATE_TRUNC('month', o.order_date) AS order_month,
        (EXTRACT(YEAR FROM DATE_TRUNC('month', o.order_date))
         - EXTRACT(YEAR FROM fp.cohort_month)) * 12
        + (EXTRACT(MONTH FROM DATE_TRUNC('month', o.order_date))
         - EXTRACT(MONTH FROM fp.cohort_month)) AS months_since_first
    FROM first_purchase fp
    JOIN orders o ON fp.customer_id = o.customer_id
),
cohort_sizes AS (
    SELECT cohort_month, COUNT(DISTINCT customer_id) AS cohort_size
    FROM first_purchase
    GROUP BY cohort_month
)
SELECT
    cs.cohort_month,
    cs.cohort_size,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN so.months_since_first = 1
        THEN so.customer_id END) / cs.cohort_size, 2) AS month_1_retention,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN so.months_since_first = 2
        THEN so.customer_id END) / cs.cohort_size, 2) AS month_2_retention,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN so.months_since_first = 3
        THEN so.customer_id END) / cs.cohort_size, 2) AS month_3_retention
FROM cohort_sizes cs
LEFT JOIN subsequent_orders so
    ON cs.cohort_month = so.cohort_month
    AND so.months_since_first BETWEEN 1 AND 3
GROUP BY cs.cohort_month, cs.cohort_size
ORDER BY cs.cohort_month;