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

📌 Question 41: Find Customers Whose Spending Decreased for 3 Consecutive Months 

Table: orders (customer_id, amount, order_date)

WITH monthly_spend AS (

    SELECT

        customer_id,

        DATE_TRUNC('month', order_date) AS month,

        SUM(amount) AS revenue

    FROM orders

    GROUP BY customer_id, DATE_TRUNC('month', order_date)

),

spend_trend AS (

    SELECT *,

           LAG(revenue,1) OVER(PARTITION BY customer_id ORDER BY month) AS prev1,

           LAG(revenue,2) OVER(PARTITION BY customer_id ORDER BY month) AS prev2

    FROM monthly_spend

)

SELECT customer_id, month

FROM spend_trend

WHERE revenue < prev1

  AND prev1 < prev2;

📌 Question 42: Find the Median Order Amount for Each Month 

Table: orders (order_id, amount, order_date)

SELECT

    DATE_TRUNC('month', order_date) AS month,

    PERCENTILE_CONT(0.5)

        WITHIN GROUP (ORDER BY amount) AS median_order

FROM orders

GROUP BY DATE_TRUNC('month', order_date);

📌 Question 43: Find Customers Who Ordered on Every Weekend 

Table: orders (customer_id, order_date)

SELECT

    customer_id

FROM orders

WHERE EXTRACT(DOW FROM order_date) IN (0,6)

GROUP BY customer_id

HAVING COUNT(DISTINCT order_date) >= 8;

📌 Question 44: Find the Top 5% Highest Revenue Customers 

Table: orders (customer_id, amount)

WITH revenue AS (

    SELECT

        customer_id,

        SUM(amount) AS total_revenue

    FROM orders

    GROUP BY customer_id

)

SELECT *

FROM (

    SELECT *,

           NTILE(20) OVER (ORDER BY total_revenue DESC) AS bucket

    FROM revenue

) t

WHERE bucket = 1;

📌 Question 45: Find the Most Frequently Returned Product 

Tables:

sales (order_id, product_id)

returns (order_id)

SELECT

    s.product_id,

    COUNT(*) AS return_count

FROM sales s

JOIN returns r

ON s.order_id = r.order_id

GROUP BY s.product_id

ORDER BY return_count DESC

LIMIT 1;

📌 Question 46: Calculate Average Delivery Time 

Table: deliveries (order_id, order_date, delivery_date)

SELECT

    ROUND(

        AVG(delivery_date - order_date),

        2

    ) AS avg_delivery_days

FROM deliveries;

📌 Question 47: Find Users Who Logged In Every Day Last Week 

Table: logins (user_id, login_date)

SELECT

    user_id

FROM logins

WHERE login_date >= CURRENT_DATE - INTERVAL '6 day'

GROUP BY user_id

HAVING COUNT(DISTINCT login_date) = 7;

📌 Question 48: Find Products with Revenue Above Category Average 

Tables:

products (product_id, category)

sales (product_id, amount)

WITH product_revenue AS (

    SELECT

        p.product_id,

        p.category,

        SUM(s.amount) AS revenue

    FROM products p

    JOIN sales s

      ON p.product_id = s.product_id

    GROUP BY p.product_id, p.category

)

SELECT *

FROM (

    SELECT *,

           AVG(revenue) OVER (

               PARTITION BY category

           ) AS category_avg

    FROM product_revenue

) t

WHERE revenue > category_avg;

📌 Question 49: Find the Busiest Day of the Week 

Table: orders (order_date)

SELECT

    TO_CHAR(order_date, 'Day') AS weekday,

    COUNT(*) AS total_orders

FROM orders

GROUP BY weekday

ORDER BY total_orders DESC

LIMIT 1;

📌 Question 50: Calculate Customer Retention After First Purchase

Table: orders (customer_id, order_date)

WITH customer_orders AS (

    SELECT

        customer_id,

        COUNT() AS total_orders

    FROM orders

    GROUP BY customer_id

)

SELECT

    ROUND(

        100.0 *

        COUNT(CASE WHEN total_orders > 1 THEN 1 END)

        / COUNT(),

        2

    ) AS retention_rate

FROM customer_orders;

🎯 Concepts Covered:

✅ Window Functions

✅ Percentiles

✅ NTILE()

✅ Retention Analysis

✅ Revenue Analytics

✅ Delivery KPIs

✅ Customer Behavior Analysis

✅ Advanced Business SQL 

❤️ Double Tap For More