📌 Question 69: Find Orders Above Monthly Average

Table: orders (order_id, amount, order_date)

WITH monthly_avg AS (

    SELECT

        DATE_TRUNC('month', order_date) AS month,

        AVG(amount) AS avg_amount

    FROM orders

    GROUP BY DATE_TRUNC('month', order_date)

)

SELECT

    o.order_id,

    o.amount,

    o.order_date

FROM orders o

JOIN monthly_avg m

ON DATE_TRUNC('month', o.order_date) = m.month

WHERE o.amount > m.avg_amount;

📌 Question 70: Calculate Customer Repeat Rate by Month

Table: orders (customer_id, order_date)

WITH customer_orders AS (

    SELECT

        DATE_TRUNC('month', order_date) AS month,

        customer_id,

        COUNT() AS order_count

    FROM orders

    GROUP BY month, customer_id

)

SELECT

    month,

    ROUND(

        100.0 *

        COUNT(CASE WHEN order_count > 1 THEN 1 END)

        / COUNT(),

        2

    ) AS repeat_rate

FROM customer_orders

GROUP BY month

ORDER BY month;

🎯 Concepts Covered:

✅ Window Functions

✅ Streak Analysis

✅ Customer Segmentation

✅ Weekly & Monthly KPIs

✅ Revenue Analytics

✅ Business Intelligence

✅ Advanced Aggregations

✅ Real Interview Scenarios 

❤️ Double Tap For More