How to Answer Any “Show Me the Last 3 Months” Question With SQL

-- Don't do this
WHERE booking_date >= '2026-01-01' AND booking_date < '2026-04-01'

-- Bookings from the last 90 days
SELECT 
    b.booking_id,
    c.first_name || ' ' || c.last_name AS customer_name,
    e.expedition_name,
    b.booking_date::date AS booked_on,
    b.total_amount
FROM bookings b
    INNER JOIN customers c ON b.customer_id = c.customer_id
    INNER JOIN expedition_instances ei ON b.instance_id = ei.instance_id
    INNER JOIN expeditions e ON ei.expedition_id = e.expedition_id
WHERE b.booking_date >= CURRENT_DATE - INTERVAL '90 days'
    AND b.status IN ('completed', 'confirmed')
ORDER BY b.booking_date DESC;

WHERE b.booking_date >= CURRENT_DATE - INTERVAL '90 days'

-- Weekly booking volume for the last 3 months
SELECT 
    DATE_TRUNC('week', b.booking_date)::date AS week_starting,
    COUNT(*) AS bookings_this_week,
    SUM(b.total_amount) AS weekly_revenue,
    ROUND(AVG(b.total_amount), 2) AS avg_booking_value
FROM bookings b
WHERE b.booking_date >= CURRENT_DATE - INTERVAL '3 months'
    AND b.status IN ('completed', 'confirmed')
GROUP BY DATE_TRUNC('week', b.booking_date)
ORDER BY week_starting DESC;

-- This month vs last month: booking volume and revenue
WITH this_month AS (
    SELECT 
        COUNT(*) AS bookings,
        COALESCE(SUM(total_amount), 0) AS revenue
    FROM bookings
    WHERE booking_date >= DATE_TRUNC('month', CURRENT_DATE)
        AND status IN ('completed', 'confirmed')
),
last_month AS (
    SELECT 
        COUNT(*) AS bookings,
        COALESCE(SUM(total_amount), 0) AS revenue
    FROM bookings
    WHERE booking_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
        AND booking_date < DATE_TRUNC('month', CURRENT_DATE)
        AND status IN ('completed', 'confirmed')
)
SELECT 
    tm.bookings AS this_month_bookings,
    lm.bookings AS last_month_bookings,
    tm.bookings - lm.bookings AS booking_change,
    tm.revenue AS this_month_revenue,
    lm.revenue AS last_month_revenue,
    CASE 
        WHEN lm.revenue > 0 
        THEN ROUND((tm.revenue - lm.revenue) * 100.0 / lm.revenue, 1)
        ELSE NULL 
    END AS revenue_change_pct
FROM this_month tm, last_month lm;

-- Executive summary: current performance with trend context
WITH current_quarter AS (
    SELECT 
        COUNT(DISTINCT b.booking_id) AS bookings,
        COUNT(DISTINCT b.customer_id) AS unique_customers,
        SUM(p.amount) AS revenue
    FROM bookings b
        INNER JOIN payments p ON b.booking_id = p.booking_id
    WHERE b.booking_date >= DATE_TRUNC('quarter', CURRENT_DATE)
        AND p.payment_status = 'completed'
        AND b.status IN ('completed', 'confirmed')
),
previous_quarter AS (
    SELECT 
        COUNT(DISTINCT b.booking_id) AS bookings,
        COUNT(DISTINCT b.customer_id) AS unique_customers,
        SUM(p.amount) AS revenue
    FROM bookings b
        INNER JOIN payments p ON b.booking_id = p.booking_id
    WHERE b.booking_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
        AND b.booking_date < DATE_TRUNC('quarter', CURRENT_DATE)
        AND p.payment_status = 'completed'
        AND b.status IN ('completed', 'confirmed')
)
SELECT 
    cq.bookings AS current_qtr_bookings,
    pq.bookings AS prev_qtr_bookings,
    cq.revenue AS current_qtr_revenue,
    pq.revenue AS prev_qtr_revenue,
    cq.unique_customers AS current_qtr_customers,
    CASE 
        WHEN pq.revenue > 0 
        THEN ROUND((cq.revenue - pq.revenue) * 100.0 / pq.revenue, 1) 
        ELSE NULL 
    END AS revenue_change_pct
FROM current_quarter cq, previous_quarter pq;