I am going to share code with you. If i run this code today, December 6, 2024, can you tell me what the last complete quarter would be and the last complete month?  Can you verify that I will only get percentage info and YTD info for the last complete month and quarter but will still get all historical monthly and quarterly data? Code follows:  WITH date_reference AS (
    SELECT
        '2024-01-01'::DATE AS start_date,
        DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')::DATE AS first_day_of_last_complete_month,
        DATE_TRUNC('quarter', CURRENT_DATE - INTERVAL '3 months')::DATE AS first_day_of_last_complete_quarter,
        LAST_DAY(DATE_ADD(month, -1, DATE_TRUNC('month', CURRENT_DATE))) as last_day_of_last_complete_month,
        LAST_DAY(DATE_ADD(month, -1, DATE_TRUNC('quarter', CURRENT_DATE))) as last_day_of_last_complete_quarter,
        DATE_TRUNC('month', CURRENT_DATE)::DATE AS current_month_start,
        DATE_TRUNC('quarter', CURRENT_DATE)::DATE AS current_quarter_start,
        DATE_TRUNC('year', CURRENT_DATE)::DATE AS start_of_current_year
),
all_monthly_metrics AS (
    SELECT 
        ticket_group,
        ticket_type,
        DATE_TRUNC('month', date)::DATE AS period,
        'Monthly' AS report_type,
        COUNT(DISTINCT case_id) AS metric1_value,
        COUNT(DISTINCT CASE WHEN column_for_distinct_count2 IN ('A', 'B') THEN case_id END) AS metric2_value,
        AVG(column_for_avg) AS metric3_value,
        CAST(
            COUNT(DISTINCT CASE WHEN column_metric1 IS NOT NULL THEN case_id END)
            / NULLIF(COUNT(DISTINCT case_id), 0)
            AS Decimal(5,4)
        ) AS metric4_value,
        CAST(
            COUNT(DISTINCT CASE WHEN column_metric2 IS NOT NULL THEN case_id END)
            / NULLIF(COUNT(DISTINCT case_id), 0)
            AS Decimal(5,4)
        ) AS metric5_value,
        MIN(column_for_min) AS metric6_value
    FROM base_metrics_table
    WHERE date >= (SELECT start_date FROM date_reference)
      AND date <= (SELECT last_day_of_last_complete_month FROM date_reference)
    GROUP BY ticket_group, ticket_type, DATE_TRUNC('month', date)
),
all_quarterly_metrics AS (
    SELECT 
        ticket_group,
        ticket_type,
        DATE_TRUNC('quarter', date)::DATE AS period,
        'Quarterly' AS report_type,
        COUNT(DISTINCT case_id) AS metric1_value,
        COUNT(DISTINCT CASE WHEN column_for_distinct_count2 IN ('A', 'B') THEN case_id END) AS metric2_value,
        AVG(column_for_avg) AS metric3_value,
        CAST(
            COUNT(DISTINCT CASE WHEN column_metric1 IS NOT NULL THEN case_id END)
            / NULLIF(COUNT(DISTINCT case_id), 0)
            AS Decimal(5,4)
        ) AS metric4_value,
        CAST(
            COUNT(DISTINCT CASE WHEN column_metric2 IS NOT NULL THEN case_id END)
            / NULLIF(COUNT(DISTINCT case_id), 0)
            AS Decimal(5,4)
        ) AS metric5_value,
        MIN(column_for_min) AS metric6_value
    FROM base_metrics_table
    WHERE date >= (SELECT start_date FROM date_reference)
      AND date <= (SELECT last_day_of_last_complete_quarter FROM date_reference)
    GROUP BY ticket_group, ticket_type, DATE_TRUNC('quarter', date)
),
monthly_YTD AS (
    SELECT
        ticket_group,
        ticket_type,
        (SELECT first_day_of_last_complete_month FROM date_reference) AS period,
        'Monthly' AS report_type,
        COUNT(DISTINCT case_id) AS metric1_ytd,
        COUNT(DISTINCT CASE WHEN column_for_distinct_count2 IN ('A', 'B') THEN case_id END) AS metric2_ytd,
        AVG(column_for_avg) AS metric3_ytd,
        CAST(
            COUNT(DISTINCT CASE WHEN column_metric1 IS NOT NULL THEN case_id END)
            / NULLIF(COUNT(DISTINCT case_id), 0)
            AS Decimal(5,4)
        ) AS metric4_ytd,
        CAST(
            COUNT(DISTINCT CASE WHEN column_metric2 IS NOT NULL THEN case_id END)
            / NULLIF(COUNT(DISTINCT case_id), 0)
            AS Decimal(5,4)
        ) AS metric5_ytd,
        MIN(column_for_min) AS metric6_ytd
    FROM base_metrics_table
    WHERE date >= (SELECT start_date FROM date_reference)
      AND date <= (SELECT last_day_of_last_complete_month FROM date_reference)
    GROUP BY ticket_group, ticket_type
),
quarterly_YTD AS (
    SELECT
        ticket_group,
        ticket_type,
        (SELECT first_day_of_last_complete_quarter FROM date_reference) AS period,
        'Quarterly' AS report_type,
        COUNT(DISTINCT case_id) AS metric1_ytd,
        COUNT(DISTINCT CASE WHEN column_for_distinct_count2 IN ('A', 'B') THEN case_id END) AS metric2_ytd,
        AVG(column_for_avg) AS metric3_ytd,
        CAST(
            COUNT(DISTINCT CASE WHEN column_metric1 IS NOT NULL THEN case_id END)
            / NULLIF(COUNT(DISTINCT case_id), 0)
            AS Decimal(5,4)
        ) AS metric4_ytd,
        CAST(
            COUNT(DISTINCT CASE WHEN column_metric2 IS NOT NULL THEN case_id END)
            / NULLIF(COUNT(DISTINCT case_id), 0)
            AS Decimal(5,4)
        ) AS metric5_ytd,
        MIN(column_for_min) AS metric6_ytd
    FROM base_metrics_table
    WHERE date >= (SELECT start_date FROM date_reference)
      AND date <= (SELECT last_day_of_last_complete_quarter FROM date_reference)
    GROUP BY ticket_group, ticket_type
),
last_month_lag AS (
    SELECT
        ticket_group,
        ticket_type,
        period,
        metric1_value,
        LAG(metric1_value) OVER (PARTITION BY ticket_group, ticket_type ORDER BY period) AS prev_metric1_value,
        metric2_value,
        LAG(metric2_value) OVER (PARTITION BY ticket_group, ticket_type ORDER BY period) AS prev_metric2_value,
        metric3_value,
        LAG(metric3_value) OVER (PARTITION BY ticket_group, ticket_type ORDER BY period) AS prev_metric3_value,
        metric4_value,
        LAG(metric4_value) OVER (PARTITION BY ticket_group, ticket_type ORDER BY period) AS prev_metric4_value,
        metric5_value,
        LAG(metric5_value) OVER (PARTITION BY ticket_group, ticket_type ORDER BY period) AS prev_metric5_value,
        metric6_value,
        LAG(metric6_value) OVER (PARTITION BY ticket_group, ticket_type ORDER BY period) AS prev_metric6_value
    FROM all_monthly_metrics
),
last_month_percent_change AS (
    SELECT
        ticket_group,
        ticket_type,
        period,
        'Monthly' AS report_type,
        CAST(
            CASE
                WHEN prev_metric1_value IS NULL OR prev_metric1_value = 0 THEN NULL
                ELSE (metric1_value - prev_metric1_value) / prev_metric1_value
            END
            AS Decimal(7,4)
        ) AS metric1_percent_change,
        CAST(
            CASE
                WHEN prev_metric2_value IS NULL OR prev_metric2_value = 0 THEN NULL
                ELSE (metric2_value - prev_metric2_value) / prev_metric2_value
            END
            AS Decimal(7,4)
        ) AS metric2_percent_change,
        CAST(
            CASE
                WHEN prev_metric3_value IS NULL OR prev_metric3_value = 0 THEN NULL
                ELSE (metric3_value - prev_metric3_value) / prev_metric3_value
            END
            AS Decimal(7,4)
        ) AS metric3_percent_change,
        CAST(
            CASE
                WHEN prev_metric4_value IS NULL OR prev_metric4_value = 0 THEN NULL
                ELSE (metric4_value - prev_metric4_value) / prev_metric4_value
            END
            AS Decimal(7,4)
        ) AS metric4_percent_change,
        CAST(
            CASE
                WHEN prev_metric5_value IS NULL OR prev_metric5_value = 0 THEN NULL
                ELSE (metric5_value - prev_metric5_value) / prev_metric5_value
            END
            AS Decimal(7,4)
        ) AS metric5_percent_change,
        CAST(
            CASE
                WHEN prev_metric6_value IS NULL OR prev_metric6_value = 0 THEN NULL
                ELSE (metric6_value - prev_metric6_value) / prev_metric6_value
            END
            AS Decimal(7,4)
        ) AS metric6_percent_change,
        mytd.metric1_ytd,
        mytd.metric2_ytd,
        mytd.metric3_ytd,
        mytd.metric4_ytd,
        mytd.metric5_ytd,
        mytd.metric6_ytd
    FROM last_month_lag
    LEFT JOIN monthly_YTD mytd
        ON last_month_lag.ticket_group = mytd.ticket_group
        AND last_month_lag.ticket_type = mytd.ticket_type
        AND last_month_lag.period = mytd.period
    WHERE period = (SELECT first_day_of_last_complete_month FROM date_reference)
),
last_quarter_lag AS (
    SELECT
        ticket_group,
        ticket_type,
        period,
        metric1_value,
        LAG(metric1_value) OVER (PARTITION BY ticket_group, ticket_type ORDER BY period) AS prev_metric1_value,
        metric2_value,
        LAG(metric2_value) OVER (PARTITION BY ticket_group, ticket_type ORDER BY period) AS prev_metric2_value,
        metric3_value,
        LAG(metric3_value) OVER (PARTITION BY ticket_group, ticket_type ORDER BY period) AS prev_metric3_value,
        metric4_value,
        LAG(metric4_value) OVER (PARTITION BY ticket_group, ticket_type ORDER BY period) AS prev_metric4_value,
        metric5_value,
        LAG(metric5_value) OVER (PARTITION BY ticket_group, ticket_type ORDER BY period) AS prev_metric5_value,
        metric6_value,
        LAG(metric6_value) OVER (PARTITION BY ticket_group, ticket_type ORDER BY period) AS prev_metric6_value
    FROM all_quarterly_metrics
),
last_quarter_percent_change AS (
    SELECT
        ticket_group,
        ticket_type,
        period,
        'Quarterly' AS report_type,
        CAST(
            CASE
                WHEN prev_metric1_value IS NULL OR prev_metric1_value = 0 THEN NULL
                ELSE (metric1_value - prev_metric1_value) / prev_metric1_value
            END
            AS Decimal(7,4)
        ) AS metric1_percent_change,
        CAST(
            CASE
                WHEN prev_metric2_value IS NULL OR prev_metric2_value = 0 THEN NULL
                ELSE (metric2_value - prev_metric2_value) / prev_metric2_value
            END
            AS Decimal(7,4)
        ) AS metric2_percent_change,
        CAST(
            CASE
                WHEN prev_metric3_value IS NULL OR prev_metric3_value = 0 THEN NULL
                ELSE (metric3_value - prev_metric3_value) / prev_metric3_value
            END
            AS Decimal(7,4)
        ) AS metric3_percent_change,
        CAST(
            CASE
                WHEN prev_metric4_value IS NULL OR prev_metric4_value = 0 THEN NULL
                ELSE (metric4_value - prev_metric4_value) / prev_metric4_value
            END
            AS Decimal(7,4)
        ) AS metric4_percent_change,
        CAST(
            CASE
                WHEN prev_metric5_value IS NULL OR prev_metric5_value = 0 THEN NULL
                ELSE (metric5_value - prev_metric5_value) / prev_metric5_value
            END
            AS Decimal(7,4)
        ) AS metric5_percent_change,
        CAST(
            CASE
                WHEN prev_metric6_value IS NULL OR prev_metric6_value = 0 THEN NULL
                ELSE (metric6_value - prev_metric6_value) / prev_metric6_value
            END
            AS Decimal(7,4)
        ) AS metric6_percent_change,
        qytd.metric1_ytd,
        qytd.metric2_ytd,
        qytd.metric3_ytd,
        qytd.metric4_ytd,
        qytd.metric5_ytd,
        qytd.metric6_ytd
    FROM last_quarter_lag
    LEFT JOIN quarterly_YTD qytd
        ON last_quarter_lag.ticket_group = qytd.ticket_group
        AND last_quarter_lag.ticket_type = qytd.ticket_type
        AND last_quarter_lag.period = qytd.period
    WHERE period = (SELECT first_day_of_last_complete_quarter FROM date_reference)
)
SELECT
    m.ticket_group,
    m.ticket_type,
    m.period,
    m.report_type,
    m.metric1_value,
    m.metric2_value,
    m.metric3_value,
    m.metric4_value,
    m.metric5_value,
    m.metric6_value,
    p.metric1_percent_change,
    p.metric2_percent_change,
    p.metric3_percent_change,
    p.metric4_percent_change,
    p.metric5_percent_change,
    p.metric6_percent_change,
    p.metric1_ytd,
    p.metric2_ytd,
    p.metric3_ytd,
    p.metric4_ytd,
    p.metric5_ytd,
    p.metric6_ytd
FROM all_quarterly_metrics m
LEFT JOIN last_quarter_percent_change p
    ON m.ticket_group = p.ticket_group
    AND m.ticket_type = p.ticket_type
    AND m.period = p.period
UNION ALL
SELECT
    mm.ticket_group,
    mm.ticket_type,
    mm.period,
    mm.report_type,
    mm.metric1_value,
    mm.metric2_value,
    mm.metric3_value,
    mm.metric4_value,
    mm.metric5_value,
    mm.metric6_value,
    pm.metric1_percent_change,
    pm.metric2_percent_change,
    pm.metric3_percent_change,
    pm.metric4_percent_change,
    pm.metric5_percent_change,
    pm.metric6_percent_change,
    pm.metric1_ytd,
    pm.metric2_ytd,
    pm.metric3_ytd,
    pm.metric4_ytd,
    pm.metric5_ytd,
    pm.metric6_ytd
FROM all_monthly_metrics mm
LEFT JOIN last_month_percent_change pm
    ON mm.ticket_group = pm.ticket_group
    AND mm.ticket_type = pm.ticket_type
    AND mm.period = pm.period
ORDER BY ticket_group, ticket_type, period, report_type;