analysis.py

import pandas as pd
import sqlite3
import matplotlib.pyplot as plt

# ===========================
# DATA LOADING
# ===========================

ACCOUNTS_PATH = "data/accounts_anonymized.json"
CASES_PATH = "data/support_cases_anonymized.json"

# Desserialização JSON files para python
accounts_df = pd.read_json(ACCOUNTS_PATH)
cases_df = pd.read_json(CASES_PATH)

#QC
print("Accounts shape:", accounts_df.shape)
print("Cases shape:", cases_df.shape)

print("\nAccounts columns:")
print(accounts_df.columns)

print("\nCases columns:")
print(cases_df.columns)

print("\nAccounts sample:")
print(accounts_df.head())

print("\nCases sample:")
print(cases_df.head())

print("\nAccounts info:")
accounts_df.info()

print("\nCases info:")
cases_df.info()

# ===========================
# CREATE SQLITE MEMORY
# ===========================

conn = sqlite3.connect(":memory:")

accounts_df.to_sql("accounts", conn, index=False, if_exists="replace")
cases_df.to_sql("cases", conn, index=False, if_exists="replace")

# ===========================
# SQL SETUP AND METRICS
# ===========================

query_cases_per_account = """
SELECT
    a.account_name,
    COUNT(c.case_sfid) AS total_cases
FROM accounts a
LEFT JOIN cases c
    ON a.account_sfid = c.account_sfid
GROUP BY a.account_name
ORDER BY total_cases DESC
LIMIT 10;
"""

top_accounts_cases = pd.read_sql(query_cases_per_account, conn)

print("\nTop 10 accounts with most support cases:")
print(top_accounts_cases)

query_cases_by_status = """
SELECT
    case_status,
    COUNT(*) AS total_cases
FROM cases
GROUP BY case_status
ORDER BY total_cases DESC;
"""

cases_by_status_df = pd.read_sql(query_cases_by_status, conn)

print("\nCases by status:")
print(cases_by_status_df)

query_avg_resolution_time = """
SELECT
    AVG(
        JULIANDAY(case_closed_date) - JULIANDAY(case_created_date)
    ) AS avg_resolution_days
FROM cases
WHERE case_closed_date IS NOT NULL;
"""

avg_resolution_df = pd.read_sql(query_avg_resolution_time, conn)

print("\nAverage resolution time (days):")
print(avg_resolution_df.round(2))

query_cases_by_priority = """
SELECT
    case_priority,
    COUNT(*) AS total_cases
FROM cases
GROUP BY case_priority
ORDER BY total_cases DESC;
"""

cases_by_priority_df = pd.read_sql(query_cases_by_priority, conn)

print("\nCases by priority:")
print(cases_by_priority_df)

# ===========================
# DATA VISUALIZATION
# ===========================

plt.figure()

colors = ["#f28c28" if i == 0 else "#4f4f4f"
          for i in range(len(top_accounts_cases))]

plt.bar(
    top_accounts_cases["account_name"],
    top_accounts_cases["total_cases"],
    color=colors
)

plt.xticks(rotation=45, ha="right")
plt.title("Top 10 Accounts by Number of Support Cases")
plt.xlabel("Account")
plt.ylabel("Number of Cases")
plt.tight_layout()
plt.show()

#--------------------
plt.figure()

plt.bar(
    cases_by_status_df["case_status"],
    cases_by_status_df["total_cases"],
    color="#4f4f4f"
)

plt.xticks(rotation=45, ha="right")
plt.title("Support Cases by Status")
plt.xlabel("Status")
plt.ylabel("Number of Cases")
plt.tight_layout()
plt.show()

#--------------------
plt.figure()

colors = [
    "#f28c28" if priority in ["Urgent", "High"] else "#9e9e9e"
    for priority in cases_by_priority_df["case_priority"]
]

plt.bar(
    cases_by_priority_df["case_priority"],
    cases_by_priority_df["total_cases"],
    color=colors
)

plt.title("Support Cases by Priority")
plt.xlabel("Priority")
plt.ylabel("Number of Cases")
plt.tight_layout()
plt.show()

conn.close()