🚀 SQL Scenario Based Interview Questions with Answers: Part-3

📌 Question 21: Find the First Purchase Date for Every Customer

Table: orders order_id, customer_id, order_date

SELECT 
    customer_id,
    MIN(order_date) AS first_purchase_date
FROM orders
GROUP BY customer_id;

📌 Question 22: Calculate Customer Lifetime Value (CLV)

Table: orders customer_id, amount

Requirement: Total revenue generated by each customer.

SELECT 
    customer_id,
    SUM(amount) AS lifetime_value
FROM orders
GROUP BY customer_id
ORDER BY lifetime_value DESC;

📌 Question 23: Find the Top 5 Products Contributing 80% of Revenue

Table: sales product_id, amount

WITH product_revenue AS (
    SELECT 
        product_id,
        SUM(amount) AS revenue
    FROM sales
    GROUP BY product_id
),
revenue_rank AS (
    SELECT 
        product_id,
        revenue,
        SUM(revenue) OVER (ORDER BY revenue DESC) AS running_revenue,
        SUM(revenue) OVER () AS total_revenue
    FROM product_revenue
)
SELECT 
    product_id,
    revenue,
    ROUND(100.0 * running_revenue / total_revenue, 2) AS cumulative_pct
FROM revenue_rank
WHERE running_revenue <= total_revenue * 0.80;

📌 Question 24: Find Customers Who Purchased More Than 3 Different Products

Table: orders customer_id, product_id

SELECT 
    customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(DISTINCT product_id) > 3;

📌 Question 25: Find the Highest Revenue Order for Each Customer

Table: orders order_id, customer_id, amount

WITH ranked_orders AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id 
               ORDER BY amount DESC
           ) AS rn
    FROM orders
)
SELECT 
    customer_id,
    order_id,
    amount
FROM ranked_orders
WHERE rn = 1;

📌 Question 26: Calculate Average Time Between Orders
Table: orders customer_id, order_date

WITH order_gap AS (
    SELECT 
        customer_id,
        order_date,
        LAG(order_date) OVER (
            PARTITION BY customer_id 
            ORDER BY order_date
        ) AS previous_order
    FROM orders
)
SELECT 
    customer_id,
    AVG(order_date - previous_order) AS avg_days_between_orders
FROM order_gap
WHERE previous_order IS NOT NULL
GROUP BY customer_id;

📌 Question 27: Find Users Who Abandoned Their Cart
Tables: cart user_id, product_id, orders user_id

Requirement: Users who added items to their cart but never completed a purchase.

SELECT DISTINCT 
    c.user_id
FROM cart c
LEFT JOIN orders o
    ON c.user_id = o.user_id
WHERE o.user_id IS NULL;

📌 Question 28: Find Revenue Generated by New vs Returning Customers

Tables: users user_id, signup_date, orders user_id, amount, order_date

SELECT 
    CASE 
        WHEN DATE_TRUNC('month', signup_date) = 
             DATE_TRUNC('month', order_date) 
        THEN 'New'
        ELSE 'Returning'
    END AS customer_type,
    SUM(amount) AS revenue
FROM users u
JOIN orders o 
    ON u.user_id = o.user_id
GROUP BY customer_type;

📌 Question 29: Find the Most Frequently Purchased Product Pair

Table: order_items order_id, product_id

SELECT 
    a.product_id AS product_1,
    b.product_id AS product_2,
    COUNT(*) AS pair_count
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 pair_count DESC
LIMIT 1;

📌 Question 30: Calculate Revenue Contribution by Region

Tables: customers customer_id, region, orders customer_id, amount

SELECT 
    c.region,
    SUM(o.amount) AS revenue,
    ROUND(
        100.0 * SUM(o.amount) / 
        SUM(SUM(o.amount)) OVER (), 
        2
    ) AS revenue_share
FROM customers c
JOIN orders o 
    ON c.customer_id = o.customer_id
GROUP BY c.region
ORDER BY revenue DESC;

Double Tap ❤️ For More