Monthly net revenue after refunds

Finance reports revenue per month after subtracting refunds. Sales and refunds live in separate tables, and some months have no refunds at all.

Using CTEs, return one row per month that has sales: month (formatted YYYY-MM), gross_revenue, refunds (0 when there are none) and net_revenue (gross minus refunds), ordered by month.

1. Step 1: Aggregate each table to month level in its own CTE; joining the raw tables directly would multiply rows and inflate the sums.
2. Step 2: `DATE_TRUNC('month', ...)` gives a clean join key for both CTEs.
3. Step 3: `LEFT JOIN` from sales keeps November, which has no refunds; `COALESCE(..., 0)` stops the net revenue from becoming NULL.

WITH monthly_sales AS (
    SELECT DATE_TRUNC('month', txn_date) AS month_start,
           SUM(amount)                   AS gross_revenue
    FROM sales_txns
    GROUP BY 1
),
monthly_refunds AS (
    SELECT DATE_TRUNC('month', refund_date) AS month_start,
           SUM(amount)                      AS refunds
    FROM refund_txns
    GROUP BY 1
)
SELECT TO_CHAR(s.month_start, 'YYYY-MM')      AS month,
       s.gross_revenue,
       COALESCE(r.refunds, 0)                 AS refunds,
       s.gross_revenue - COALESCE(r.refunds, 0) AS net_revenue
FROM monthly_sales s
LEFT JOIN monthly_refunds r ON r.month_start = s.month_start
ORDER BY s.month_start;