-- DATA DEMOGRAPHICS

-- Analyzing gender distribution
SELECT 
  CASE
    WHEN sex = '1' THEN 'Male'
    WHEN sex = '2' THEN 'Female'
    ELSE 'Unknown'
  END AS gender,
  COUNT(*) AS client_count
FROM `bigquery-public-data.ml_datasets.credit_card_default`
GROUP BY gender;

-- Examining marital status distribution
SELECT
  CASE
    WHEN marital_status = '1' THEN 'Married'
    WHEN marital_status = '2' THEN 'Single'
    WHEN marital_status = '3' THEN 'Other'
    ELSE 'Unknown'
  END AS marital_status,
  COUNT(*) AS client_count
FROM `bigquery-public-data.ml_datasets.credit_card_default`
GROUP BY marital_status;

-- Exploring education level distribution
SELECT
  CASE
    WHEN education_level = '1' THEN 'Graduate School'
    WHEN education_level = '2' THEN 'University'
    WHEN education_level = '3' THEN 'High School'
    WHEN education_level = '4' THEN 'Other'
    WHEN education_level = '5' THEN 'Unknown'
    ELSE 'Unspecified'
  END AS education_level,
  COUNT(*) AS client_count
FROM `bigquery-public-data.ml_datasets.credit_card_default`
GROUP BY education_level;

-- Analyzing credit limit statistics
SELECT
  AVG(limit_balance) AS avg_credit_limit,
  MIN(limit_balance) AS min_credit_limit,
  MAX(limit_balance) AS max_credit_limit
FROM `bigquery-public-data.ml_datasets.credit_card_default`;

-- Examining average credit limit by education level
SELECT
  CASE
    WHEN education_level = '1' THEN 'Graduate School'
    WHEN education_level = '2' THEN 'University'
    WHEN education_level = '3' THEN 'High School'
    WHEN education_level = '4' THEN 'Other'
    WHEN education_level = '5' THEN 'Unknown'
    ELSE 'Unspecified'
  END AS education_level,
  AVG(limit_balance) AS avg_credit_limit
FROM `bigquery-public-data.ml_datasets.credit_card_default`
GROUP BY education_level
ORDER BY education_level;