Business Intelligence Background Questions

-- users table: user_id, signup_at
-- events table: user_id, event_at (date) -- e.g., "app_open"
WITH signup AS (
  SELECT user_id, DATE_TRUNC('week', signup_at) AS cohort_week
  FROM users
),
activity AS (
  SELECT e.user_id,
         DATE_TRUNC('week', e.event_at) AS activity_week
  FROM events e
  WHERE e.event_at >= (SELECT MIN(signup_at) FROM users)
),
cohort_activity AS (
  SELECT s.cohort_week,
         a.activity_week,
         COUNT(DISTINCT a.user_id) AS active_users
  FROM signup s
  JOIN activity a ON s.user_id = a.user_id
  GROUP BY 1,2
),
cohort_sizes AS (
  SELECT cohort_week, COUNT(DISTINCT user_id) AS cohort_size
  FROM signup
  GROUP BY 1
)
SELECT
  ca.cohort_week,
  DATE_PART('week', ca.activity_week - ca.cohort_week) AS week_number,
  ca.active_users,
  cs.cohort_size,
  ROUND(100.0 * ca.active_users / cs.cohort_size, 2) AS retention_pct
FROM cohort_activity ca
JOIN cohort_sizes cs USING (cohort_week)
ORDER BY cohort_week, week_number;

-- tests/null_threshold_opportunity_amount.sql
select count(*) as failures
from {{ ref('fct_opportunities') }}
where amount is null
and {{ percent_of_table }} > {{ threshold }}

-- 1. Create normalized view (ANSI SQL)
WITH users_norm AS (
  SELECT
    user_id,
    email,
    LOWER(TRIM(email)) AS norm_email,
    CASE WHEN LOWER(TRIM(email)) <> email THEN TRUE ELSE FALSE END AS email_changed
  FROM users
  WHERE email IS NOT NULL
),

-- 2. Find groups with >1 distinct user mapped to same normalized email
dup_groups AS (
  SELECT
    norm_email,
    COUNT(*) AS account_count,
    BOOL_OR(email_changed) AS any_changed
  FROM users_norm
  GROUP BY norm_email
  HAVING COUNT(*) > 1
)

-- 3. Flag probable duplicates for manual review
SELECT
  u.user_id,
  u.email AS original_email,
  u.norm_email,
  g.account_count,
  u.email_changed,
  CASE
    WHEN g.any_changed THEN 'probable_duplicate_normalization'
    ELSE 'probable_duplicate_exact'
  END AS flag_reason
FROM users_norm u
JOIN dup_groups g USING (norm_email)
ORDER BY norm_email, account_count DESC, u.user_id;