-- =============================================================
-- 05_subissue_trend_analysis.sql
-- AWS Consumer Complaint Intelligence Dashboard
-- North Carolina Credit Card Fees & Interest Analysis
--
-- PURPOSE: Cross-tabulate sub-issue counts by month and by
--          company to identify trend patterns.
-- =============================================================

-- -------------------------------------------------------
-- QUERY 1: Sub-issue counts by month (trend over time)
-- -------------------------------------------------------
SELECT
    DATE_FORMAT(date_parse(date_received, '%m/%d/%y'), '%Y-%m') AS year_month,
    sub_issue,
    COUNT(*) AS complaint_count
FROM consumer_complaints.nc_credit_card_complaints
WHERE date_received IS NOT NULL AND date_received != ''
GROUP BY
    DATE_FORMAT(date_parse(date_received, '%m/%d/%y'), '%Y-%m'),
    sub_issue
ORDER BY year_month, complaint_count DESC;

-- -------------------------------------------------------
-- QUERY 2: Sub-issue counts by company (top 10 companies)
-- -------------------------------------------------------
SELECT
    company,
    sub_issue,
    COUNT(*) AS complaint_count
FROM consumer_complaints.nc_credit_card_complaints
WHERE company IN (
    SELECT company
    FROM consumer_complaints.nc_credit_card_complaints
    GROUP BY company
    ORDER BY COUNT(*) DESC
    LIMIT 10
)
GROUP BY company, sub_issue
ORDER BY company, complaint_count DESC;

-- -------------------------------------------------------
-- QUERY 3: Year-over-year sub-issue comparison
-- 2024 vs 2025 complaint volumes by sub-issue
-- -------------------------------------------------------
SELECT
    sub_issue,
    SUM(CASE WHEN YEAR(date_parse(date_received, '%m/%d/%y')) = 2024 THEN 1 ELSE 0 END) AS count_2024,
    SUM(CASE WHEN YEAR(date_parse(date_received, '%m/%d/%y')) = 2025 THEN 1 ELSE 0 END) AS count_2025,
    SUM(CASE WHEN YEAR(date_parse(date_received, '%m/%d/%y')) = 2026 THEN 1 ELSE 0 END) AS count_2026_partial
FROM consumer_complaints.nc_credit_card_complaints
WHERE date_received IS NOT NULL AND date_received != ''
GROUP BY sub_issue
ORDER BY sub_issue;

-- -------------------------------------------------------
-- QUERY 4: Monthly complaint volume with 3-month rolling average
-- (Athena window function approach)
-- -------------------------------------------------------
WITH monthly_counts AS (
    SELECT
        DATE_FORMAT(date_parse(date_received, '%m/%d/%y'), '%Y-%m') AS year_month,
        COUNT(*) AS complaint_count
    FROM consumer_complaints.nc_credit_card_complaints
    WHERE date_received IS NOT NULL AND date_received != ''
    GROUP BY DATE_FORMAT(date_parse(date_received, '%m/%d/%y'), '%Y-%m')
)
SELECT
    year_month,
    complaint_count,
    ROUND(
        AVG(complaint_count) OVER (
            ORDER BY year_month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ), 1
    ) AS rolling_3mo_avg
FROM monthly_counts
ORDER BY year_month;

-- -------------------------------------------------------
-- QUERY 5: Complaint spike detection
-- Months where volume exceeded the overall monthly average
-- -------------------------------------------------------
WITH monthly_counts AS (
    SELECT
        DATE_FORMAT(date_parse(date_received, '%m/%d/%y'), '%Y-%m') AS year_month,
        COUNT(*) AS complaint_count
    FROM consumer_complaints.nc_credit_card_complaints
    WHERE date_received IS NOT NULL AND date_received != ''
    GROUP BY DATE_FORMAT(date_parse(date_received, '%m/%d/%y'), '%Y-%m')
),
avg_monthly AS (
    SELECT AVG(complaint_count) AS avg_count FROM monthly_counts
)
SELECT
    m.year_month,
    m.complaint_count,
    ROUND(a.avg_count, 1) AS avg_monthly_count,
    ROUND(m.complaint_count - a.avg_count, 1) AS deviation_from_avg
FROM monthly_counts m
CROSS JOIN avg_monthly a
WHERE m.complaint_count > a.avg_count
ORDER BY deviation_from_avg DESC;
