Portfolio Project Case Studies Questions

WITH months AS (
  -- generate last 12 months (month_start)
  SELECT date_trunc('month', current_date) - (n * interval '1 month') AS month_start
  FROM generate_series(0,11) AS t(n)
),
users_cohort AS (
  SELECT
    user_id,
    date_trunc('month', created_at)::date AS cohort_month
  FROM users
),
user_events AS (
  -- reduce events to month granularity and mark purchase flag
  SELECT
    user_id,
    date_trunc('month', occurred_at)::date AS event_month,
    MAX((event_type = 'purchase')::int) AS has_purchase -- 1 if any purchase in that month
  FROM events
  WHERE occurred_at >= date_trunc('month', current_date) - interval '12 months'
  GROUP BY user_id, date_trunc('month', occurred_at)
)
SELECT
  m.month_start AS month,
  uc.cohort_month,
  COUNT(DISTINCT CASE WHEN ue.user_id IS NOT NULL THEN uc.user_id END) AS mau,
  COALESCE(
    100.0 * SUM(CASE WHEN ue.has_purchase = 1 THEN 1 ELSE 0 END) / NULLIF(COUNT(DISTINCT CASE WHEN ue.user_id IS NOT NULL THEN uc.user_id END),0),
    0
  ) AS conversion_pct
FROM months m
CROSS JOIN (
  -- restrict cohorts to users who signed up no later than the month (optional: only cohorts within last 12 months)
  SELECT DISTINCT cohort_month FROM users_cohort
) cohorts(cohort_month)
LEFT JOIN users_cohort uc
  ON uc.cohort_month = cohorts.cohort_month
-- join to events that occurred in the target month
LEFT JOIN user_events ue
  ON ue.user_id = uc.user_id
  AND ue.event_month = m.month_start
WHERE m.month_start >= date_trunc('month', current_date) - interval '11 months'
  -- optional: limit cohorts to those signing up in last 12 months
  AND cohorts.cohort_month >= date_trunc('month', current_date) - interval '11 months'
GROUP BY m.month_start, uc.cohort_month
ORDER BY uc.cohort_month, m.month_start;

# python pseudocode
import shap
import matplotlib.pyplot as plt
import json
from pathlib import Path

# 1. Prepare
model = trained_sklearn_model
X = feature_dataframe          # Pandas DataFrame
background = X.sample(100)     # for KernelExplainer; smaller for speed

# 2. Compute SHAP values
if model_is_tree_based:
    explainer = shap.TreeExplainer(model, data=background)
else:
    explainer = shap.KernelExplainer(model.predict_proba, background)

shap_values = explainer.shap_values(X)  # list for multiclass

# 3. Visualize (example: summary plot and single prediction force plot)
out_dir = Path("outputs/shap_viz"); out_dir.mkdir(parents=True, exist_ok=True)

# Summary plot - simplified, high-contrast
plt.figure(figsize=(8,6))
shap.summary_plot(shap_values, X, show=False, plot_type="dot", color_bar=False)
plt.title("SHAP summary: feature impact on model output", fontsize=14)
plt.tight_layout()
svg_path = out_dir / "shap_summary.svg"
png_path = out_dir / "shap_summary.png"
plt.savefig(svg_path, format="svg")    # vector: scalable, editable
plt.savefig(png_path, dpi=300)         # raster: fallback
plt.close()

# Single prediction explanation (force or bar)
i = 0  # index to explain
plt.figure(figsize=(6,4))
shap.plots.bar(shap_values[i], feature_names=X.columns, show=False)
plt.title(f"Top feature contributions for sample {i}", fontsize=12)
plt.tight_layout()
plt.savefig(out_dir / f"shap_bar_{i}.svg", format="svg")
plt.savefig(out_dir / f"shap_bar_{i}.png", dpi=300)
plt.close()

# 4. Accessibility artifacts
metadata = {
    "files": [
        {
            "filename": svg_path.name,
            "alt_text": "Bar chart showing top features contributing to prediction: feature A positive, feature B negative. Colors high contrast. See caption for details.",
            "caption": "This summary shows the magnitude and direction of each feature's effect on predictions. Positive values increase predicted probability; negative decrease.",
            "labels": list(X.columns)
        }
    ],
    "model": {"type": model.__class__.__name__, "explainer": explainer.__class__.__name__}
}
with open(out_dir / "shap_viz_metadata.json", "w") as f:
    json.dump(metadata, f, indent=2)

# 5. Save plain-text caption for screen readers
with open(out_dir / "shap_summary_caption.txt", "w") as f:
    f.write(metadata["files"][0]["caption"])

import pandas as pd
from ctgan import CTGANSynthesizer
# pseudocode; assumes a DP-enabled optimizer is available

def generate_synthetic(df, pii_cols, sensitive_cols, categorical_cols, numeric_cols,
                       n_samples=10000, dp_epsilon=None, seed=0):
    """
    1) Pseudonymize/ark PII
    2) Feature encode
    3) Train CTGAN with optional DP-SGD
    4) Post-process to enforce marginal totals
    """
    # 1) Remove/transform PII
    df = df.copy()
    for c in pii_cols:
        df[c] = df[c].apply(lambda v: hash_with_salt(v, seed))  # non-reversible pseudonym
    
    # 2) mask rare categories (k-anonymity prep)
    df = mask_rare_categories(df, categorical_cols, min_count=10)
    
    # 3) fit generator (use DP-SGD if dp_epsilon provided)
    gen = CTGANSynthesizer(epochs=300, seed=seed)
    if dp_epsilon:
        gen.enable_dp(dp_epsilon, delta=1e-5)  # conceptual API
    gen.fit(df, categorical_cols)
    
    # 4) sample and post-process to match key aggregates
    synth = gen.sample(n_samples)
    synth = calibrate_marginals(synth, df, cols=['country','product','month'])
    synth = remove_residual_pii(synth, pii_cols)
    return synth