29. Find Monthly Revenue Growth

WITH monthly_sales AS (

    SELECT

        DATE_TRUNC('month', o.order_date) AS month,

        SUM(oi.quantity * oi.unit_price) AS revenue

    FROM orders o

    JOIN order_items oi ON o.order_id = oi.order_id

    GROUP BY DATE_TRUNC('month', o.order_date)

)

SELECT

    month,

    revenue,

    LAG(revenue) OVER (ORDER BY month) AS previous_month_revenue,

    ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month), 2) AS growth_percentage

FROM monthly_sales;

30. Find the Highest Value Order for Each Customer

WITH order_values AS (

    SELECT

        o.customer_id,

        o.order_id,

        SUM(oi.quantity * oi.unit_price) AS order_value

    FROM orders o

    JOIN order_items oi ON o.order_id = oi.order_id

    GROUP BY o.customer_id, o.order_id

)

SELECT *

FROM (

    SELECT *,

           ROW_NUMBER() OVER (

               PARTITION BY customer_id ORDER BY order_value DESC

           ) AS rn

    FROM order_values

) t

WHERE rn = 1;

Window Functions Covered 

• Ranking: ROW_NUMBER(), RANK(), DENSE_RANK() 

• Navigation: LAG(), LEAD() 

• Aggregates: SUM() OVER() 

• Analytics: Running Totals, Revenue Contribution, Month-over-Month Growth 

💡 Double Tap ❤️ For More