Improve SQL Query Readability with Common Table Expressions (CTEs)

WITH MonthlySales AS (
    SELECT
        DATE_TRUNC('month', order_date) AS sales_month,
        SUM(total_amount) AS monthly_total
    FROM orders
    WHERE order_date >= '2023-01-01'
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    sales_month,
    monthly_total,
    LAG(monthly_total, 1, 0) OVER (ORDER BY sales_month) AS previous_month_sales
FROM MonthlySales
WHERE monthly_total > 10000
ORDER BY sales_month DESC;

How it works: This snippet uses a Common Table Expression (CTE) named `MonthlySales` to first calculate the total sales for each month in 2023. This intermediate result set is then used in the main query to select the sales month and its total, and also to calculate the previous month's sales using the `LAG` window function for comparison. CTEs, introduced by the `WITH` clause, improve query readability, modularity, and can be referenced multiple times within the same query.