Monthly Active Users (MAU)

-- Calculate Monthly Active Users with engagement breakdown
WITH user_activity AS (
  SELECT 
    user_id,
    DATE_TRUNC('month', event_date) as activity_month,
    COUNT(DISTINCT DATE(event_date)) as active_days,
    COUNT(*) as total_events,
    COUNT(DISTINCT event_type) as unique_event_types,
    MAX(event_date) as last_activity_date,
    MIN(event_date) as first_activity_month
  FROM user_events
  WHERE event_date >= CURRENT_DATE - INTERVAL '90 days'
    AND event_type IN ('login', 'create', 'edit', 'share', 'collaborate')  -- Define meaningful actions
  GROUP BY 1, 2
),
monthly_mau AS (
  SELECT 
    activity_month,
    COUNT(DISTINCT user_id) as total_mau,
    COUNT(DISTINCT CASE WHEN active_days >= 20 THEN user_id END) as power_users,
    COUNT(DISTINCT CASE WHEN active_days BETWEEN 10 AND 19 THEN user_id END) as regular_users,
    COUNT(DISTINCT CASE WHEN active_days BETWEEN 5 AND 9 THEN user_id END) as casual_users,
    COUNT(DISTINCT CASE WHEN active_days < 5 THEN user_id END) as light_users,
    ROUND(AVG(active_days), 1) as avg_active_days,
    ROUND(AVG(total_events), 1) as avg_events_per_user,
    ROUND(AVG(unique_event_types), 1) as avg_feature_usage
  FROM user_activity
  GROUP BY 1
)
SELECT 
  activity_month,
  total_mau,
  power_users,
  regular_users,
  casual_users,
  light_users,
  ROUND(100.0 * power_users / total_mau, 1) as power_user_percentage,
  ROUND(100.0 * (power_users + regular_users) / total_mau, 1) as engaged_user_percentage,
  avg_active_days,
  avg_events_per_user,
  LAG(total_mau) OVER (ORDER BY activity_month) as prev_month_mau,
  ROUND(100.0 * (total_mau - LAG(total_mau) OVER (ORDER BY activity_month)) / 
    NULLIF(LAG(total_mau) OVER (ORDER BY activity_month), 0), 1) as month_over_month_growth
FROM monthly_mau
ORDER BY activity_month DESC;

-- Analyze MAU by user cohorts and retention
WITH user_cohorts AS (
  SELECT 
    user_id,
    DATE_TRUNC('month', registration_date) as cohort_month,
    registration_date
  FROM users
  WHERE registration_date >= '2023-01-01'
),
cohort_activity AS (
  SELECT 
    uc.cohort_month,
    uc.user_id,
    DATE_TRUNC('month', ue.event_date) as activity_month,
    EXTRACT(month FROM AGE(ue.event_date, uc.registration_date)) as months_since_registration
  FROM user_cohorts uc
  JOIN user_events ue ON uc.user_id = ue.user_id
  WHERE ue.event_date >= uc.registration_date
    AND ue.event_type IN ('login', 'create', 'edit', 'share', 'collaborate')
),
cohort_mau AS (
  SELECT 
    cohort_month,
    activity_month,
    months_since_registration,
    COUNT(DISTINCT user_id) as active_users
  FROM cohort_activity
  GROUP BY 1, 2, 3
),
cohort_sizes AS (
  SELECT 
    cohort_month,
    COUNT(DISTINCT user_id) as cohort_size
  FROM user_cohorts
  GROUP BY 1
)
SELECT 
  cm.cohort_month,
  cs.cohort_size,
  cm.activity_month,
  cm.months_since_registration,
  cm.active_users,
  ROUND(100.0 * cm.active_users / cs.cohort_size, 1) as retention_rate,
  SUM(cm.active_users) OVER (
    PARTITION BY cm.activity_month 
    ORDER BY cm.cohort_month
  ) as cumulative_mau_from_cohorts
FROM cohort_mau cm
JOIN cohort_sizes cs ON cm.cohort_month = cs.cohort_month
WHERE cm.months_since_registration <= 12  -- First year retention
ORDER BY cm.activity_month DESC, cm.cohort_month DESC;

-- Analyze MAU by feature usage and engagement depth
WITH feature_usage AS (
  SELECT 
    user_id,
    DATE_TRUNC('month', event_date) as activity_month,
    event_type as feature,
    COUNT(*) as usage_count,
    COUNT(DISTINCT DATE(event_date)) as days_used_feature
  FROM user_events
  WHERE event_date >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY 1, 2, 3
),
user_feature_profiles AS (
  SELECT 
    activity_month,
    user_id,
    COUNT(DISTINCT feature) as features_used,
    SUM(usage_count) as total_events,
    MAX(days_used_feature) as max_days_used_any_feature,
    STRING_AGG(DISTINCT feature, ', ' ORDER BY usage_count DESC) as top_features
  FROM feature_usage
  GROUP BY 1, 2
),
feature_mau_analysis AS (
  SELECT 
    activity_month,
    COUNT(DISTINCT user_id) as total_mau,
    COUNT(DISTINCT CASE WHEN features_used >= 5 THEN user_id END) as multi_feature_users,
    COUNT(DISTINCT CASE WHEN features_used = 1 THEN user_id END) as single_feature_users,
    ROUND(AVG(features_used), 1) as avg_features_per_user,
    ROUND(AVG(total_events), 1) as avg_events_per_user,
    ROUND(AVG(max_days_used_any_feature), 1) as avg_engagement_days
  FROM user_feature_profiles
  GROUP BY 1
),
feature_popularity AS (
  SELECT 
    fu.activity_month,
    fu.feature,
    COUNT(DISTINCT fu.user_id) as feature_mau,
    ROUND(AVG(fu.usage_count), 1) as avg_usage_per_user,
    ROUND(AVG(fu.days_used_feature), 1) as avg_days_used
  FROM feature_usage fu
  GROUP BY 1, 2
)
SELECT 
  fma.activity_month,
  fma.total_mau,
  fma.multi_feature_users,
  fma.single_feature_users,
  ROUND(100.0 * fma.multi_feature_users / fma.total_mau, 1) as multi_feature_percentage,
  fma.avg_features_per_user,
  fma.avg_events_per_user,
  fma.avg_engagement_days
FROM feature_mau_analysis fma
ORDER BY fma.activity_month DESC;