🚀 SQL Scenario Based Interview Questions with Answers Part 4

📌 Question 31: Find Customers Who Increased Their Monthly Spending

Table: orders customer_id, amount, order_date

Requirement: Return customers whose spending increased compared to the previous month.

WITH monthly_spend AS (
    SELECT 
        customer_id,
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS total_spend
    FROM orders
    GROUP BY customer_id, DATE_TRUNC('month', order_date)
)
SELECT 
    customer_id,
    month,
    total_spend
FROM (
    SELECT *,
           LAG(total_spend) OVER (
               PARTITION BY customer_id 
               ORDER BY month
           ) AS prev_spend
    FROM monthly_spend
) t
WHERE total_spend > prev_spend;

📌 Question 32: Find Products Purchased Together Most Often

Table: order_items order_id, product_id

SELECT 
    a.product_id AS product_1,
    b.product_id AS product_2,
    COUNT(*) AS frequency
FROM order_items a
JOIN order_items b 
    ON a.order_id = b.order_id 
    AND a.product_id < b.product_id
GROUP BY 1,2
ORDER BY frequency DESC
LIMIT 10;

📌 Question 33: Find Users Active for 7 Consecutive Days

Table: user_activity user_id, activity_date

WITH activity AS (
    SELECT 
        user_id,
        activity_date,
        activity_date - 
        ROW_NUMBER() OVER (
            PARTITION BY user_id 
            ORDER BY activity_date
        ) * INTERVAL '1 day' AS grp
    FROM user_activity
)
SELECT 
    user_id
FROM activity
GROUP BY user_id, grp
HAVING COUNT(*) >= 7;

📌 Question 34: Calculate Repeat Purchase Rate

Table: orders customer_id, order_id

SELECT 
    ROUND(
        100.0 * 
        COUNT(CASE WHEN order_count > 1 THEN 1 END) / 
        COUNT(*), 
        2
    ) AS repeat_purchase_rate
FROM (
    SELECT 
        customer_id,
        COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
) t;

📌 Question 35: Find the Longest Gap Between Two Orders

Table: orders customer_id, order_date

WITH gaps AS (
    SELECT 
        customer_id,
        order_date,
        order_date - 
        LAG(order_date) OVER (
            PARTITION BY customer_id 
            ORDER BY order_date
        ) AS gap_days
    FROM orders
)
SELECT 
    customer_id,
    MAX(gap_days) AS longest_gap
FROM gaps
GROUP BY customer_id;

📌 Question 36: Calculate Revenue Lost Due to Churn

Tables: customers customer_id, status, orders customer_id, amount

SELECT 
    SUM(amount) AS churned_revenue
FROM customers c
JOIN orders o 
    ON c.customer_id = o.customer_id
WHERE c.status = 'Churned';

📌 Question 37: Find the Fastest Growing Product

Table: sales product_id, amount, sale_date

WITH monthly_sales AS (
    SELECT 
        product_id,
        DATE_TRUNC('month', sale_date) AS month,
        SUM(amount) AS revenue
    FROM sales
    GROUP BY 1,2
)
SELECT * 
FROM (
    SELECT *,
        revenue - 
        LAG(revenue) OVER (
            PARTITION BY product_id 
            ORDER BY month
        ) AS growth
    FROM monthly_sales
) t
ORDER BY growth DESC
LIMIT 1;

📌 Question 38: Find Customers Who Purchased Every Product Category

Tables: products product_id, category, orders customer_id, product_id

SELECT 
    customer_id
FROM orders o
JOIN products p 
    ON o.product_id = p.product_id
GROUP BY customer_id
HAVING COUNT(DISTINCT category) = (
    SELECT COUNT(DISTINCT category) 
    FROM products
);

📌 Question 39: Find Peak Ordering Hour

Table: orders order_id, order_timestamp