-- =============================================================
-- 03_kpi_queries.sql
-- AWS Consumer Complaint Intelligence Dashboard
-- North Carolina Credit Card Fees & Interest Analysis
--
-- PURPOSE: Core KPI queries replicating the Python analysis.
--          All results validated against complaints.csv (542 rows).
-- =============================================================

-- -------------------------------------------------------
-- KPI 1: Total complaint count
-- Expected: 542
-- -------------------------------------------------------
SELECT COUNT(*) AS total_complaints
FROM consumer_complaints.nc_credit_card_complaints;

-- -------------------------------------------------------
-- KPI 2: Complaint count by company (top 10)
-- -------------------------------------------------------
SELECT
    company,
    COUNT(*) AS complaint_count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM consumer_complaints.nc_credit_card_complaints), 1) AS pct_of_total
FROM consumer_complaints.nc_credit_card_complaints
GROUP BY company
ORDER BY complaint_count DESC
LIMIT 10;

-- -------------------------------------------------------
-- KPI 3: Complaint count by sub-issue
-- -------------------------------------------------------
SELECT
    sub_issue,
    COUNT(*) AS complaint_count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM consumer_complaints.nc_credit_card_complaints), 1) AS pct_of_total
FROM consumer_complaints.nc_credit_card_complaints
GROUP BY sub_issue
ORDER BY complaint_count DESC;

-- -------------------------------------------------------
-- KPI 4: Monthly complaint volume trend
-- -------------------------------------------------------
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')
ORDER BY year_month;

-- -------------------------------------------------------
-- KPI 5: Company response outcome distribution
-- -------------------------------------------------------
SELECT
    company_response,
    COUNT(*) AS count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM consumer_complaints.nc_credit_card_complaints), 1) AS pct
FROM consumer_complaints.nc_credit_card_complaints
GROUP BY company_response
ORDER BY count DESC;

-- -------------------------------------------------------
-- KPI 6: Timely response rate
-- Expected: 99.6% (540 Yes, 2 No)
-- -------------------------------------------------------
SELECT
    timely_response,
    COUNT(*) AS count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM consumer_complaints.nc_credit_card_complaints), 1) AS pct
FROM consumer_complaints.nc_credit_card_complaints
GROUP BY timely_response
ORDER BY count DESC;

-- -------------------------------------------------------
-- KPI 7: Narrative availability rate
-- Expected: 62.2% (337 with narratives, 205 without)
-- -------------------------------------------------------
SELECT
    CASE
        WHEN consumer_narrative IS NULL OR consumer_narrative = '' THEN 'No Narrative'
        ELSE 'Has Narrative'
    END AS narrative_status,
    COUNT(*) AS count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM consumer_complaints.nc_credit_card_complaints), 1) AS pct
FROM consumer_complaints.nc_credit_card_complaints
GROUP BY
    CASE
        WHEN consumer_narrative IS NULL OR consumer_narrative = '' THEN 'No Narrative'
        ELSE 'Has Narrative'
    END;

-- -------------------------------------------------------
-- KPI 8: ZIP code complaint concentration (top 15, excluding fully masked)
-- -------------------------------------------------------
SELECT
    zip_code,
    COUNT(*) AS complaint_count
FROM consumer_complaints.nc_credit_card_complaints
WHERE zip_code IS NOT NULL
  AND zip_code != 'XXXXX'
  AND zip_code != ''
GROUP BY zip_code
ORDER BY complaint_count DESC
LIMIT 15;
