-- =============================================================
-- 04_company_response_analysis.sql
-- AWS Consumer Complaint Intelligence Dashboard
-- North Carolina Credit Card Fees & Interest Analysis
--
-- PURPOSE: Analyze company response behavior — response outcomes
--          by company, per-company timely response rates, and
--          monetary relief patterns.
-- =============================================================

-- -------------------------------------------------------
-- QUERY 1: Response outcome breakdown by company (top 10 companies)
-- -------------------------------------------------------
SELECT
    company,
    company_response,
    COUNT(*) AS 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, company_response
ORDER BY company, count DESC;

-- -------------------------------------------------------
-- QUERY 2: Per-company timely response rate
-- -------------------------------------------------------
SELECT
    company,
    COUNT(*) AS total_complaints,
    SUM(CASE WHEN LOWER(timely_response) = 'yes' THEN 1 ELSE 0 END) AS timely_count,
    ROUND(
        SUM(CASE WHEN LOWER(timely_response) = 'yes' THEN 1 ELSE 0 END) * 100.0 / COUNT(*),
        1
    ) AS timely_rate_pct
FROM consumer_complaints.nc_credit_card_complaints
GROUP BY company
HAVING COUNT(*) >= 5
ORDER BY timely_rate_pct ASC, total_complaints DESC;

-- -------------------------------------------------------
-- QUERY 3: Monetary relief rate by company
-- Companies that closed with monetary relief vs. explanation
-- -------------------------------------------------------
SELECT
    company,
    COUNT(*) AS total_complaints,
    SUM(CASE WHEN company_response = 'Closed with monetary relief' THEN 1 ELSE 0 END)
        AS monetary_relief_count,
    ROUND(
        SUM(CASE WHEN company_response = 'Closed with monetary relief' THEN 1 ELSE 0 END)
        * 100.0 / COUNT(*),
        1
    ) AS monetary_relief_pct
FROM consumer_complaints.nc_credit_card_complaints
GROUP BY company
HAVING COUNT(*) >= 5
ORDER BY monetary_relief_pct DESC;

-- -------------------------------------------------------
-- QUERY 4: In-progress complaints (unresolved as of dataset date)
-- -------------------------------------------------------
SELECT
    company,
    COUNT(*) AS in_progress_count
FROM consumer_complaints.nc_credit_card_complaints
WHERE company_response = 'In progress'
GROUP BY company
ORDER BY in_progress_count DESC;

-- -------------------------------------------------------
-- QUERY 5: Response outcome by sub-issue
-- -------------------------------------------------------
SELECT
    sub_issue,
    company_response,
    COUNT(*) AS count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY sub_issue), 1) AS pct_within_subissue
FROM consumer_complaints.nc_credit_card_complaints
GROUP BY sub_issue, company_response
ORDER BY sub_issue, count DESC;
