-- Monthly Active Users by Year and Month
SELECT
  year,
  month,
  FORMAT_DATE('%Y-%m', DATE(year, month, 1))    AS year_month,
  COUNT(DISTINCT user_id)                         AS monthly_active_users,
  LAG(COUNT(DISTINCT user_id)) OVER (
    ORDER BY year, month
  )                                               AS prev_month_mau,
  ROUND(
    (COUNT(DISTINCT user_id) - LAG(COUNT(DISTINCT user_id))
       OVER (ORDER BY year, month))
    / NULLIF(LAG(COUNT(DISTINCT user_id))
       OVER (ORDER BY year, month), 0) * 100, 2
  )                                               AS mom_growth_pct
FROM `socialflow.analytics.fact_user_sessions`
WHERE year BETWEEN 2020 AND 2025
GROUP BY year, month
ORDER BY year, month;

-- Annual Revenue Breakdown by Stream
SELECT
  year,
  ROUND(SUM(ad_revenue_usd) / 1e9, 3)           AS ad_revenue_billion,
  ROUND(SUM(subscription_rev_usd) / 1e9, 3)     AS subscription_revenue_billion,
  ROUND(SUM(total_revenue_usd) / 1e9, 3)        AS total_revenue_billion,
  ROUND(
    SUM(ad_revenue_usd) / NULLIF(SUM(total_revenue_usd), 0) * 100, 1
  )                                               AS ad_revenue_share_pct,
  ROUND(
    SUM(subscription_rev_usd) / NULLIF(SUM(total_revenue_usd), 0) * 100, 1
  )                                               AS subscription_share_pct
FROM `socialflow.analytics.fact_user_sessions`
WHERE year BETWEEN 2020 AND 2025
GROUP BY year
ORDER BY year;

-- Top 10 Countries: Sessions, Revenue, ARPU
SELECT
  country,
  region,
  COUNT(*)                                        AS total_sessions,
  COUNT(DISTINCT user_id)                         AS unique_users,
  ROUND(SUM(total_revenue_usd), 2)               AS total_revenue_usd,
  ROUND(
    SUM(total_revenue_usd) / NULLIF(COUNT(DISTINCT user_id), 0), 2
  )                                               AS arpu_usd,
  ROUND(AVG(session_duration_min), 1)            AS avg_session_min
FROM `socialflow.analytics.fact_user_sessions`
WHERE year = 2024
GROUP BY country, region
ORDER BY total_revenue_usd DESC
LIMIT 10;

-- Platform Session Share by Year
SELECT
  year,
  platform,
  COUNT(*)                                        AS sessions,
  ROUND(
    COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY year), 2
  )                                               AS session_share_pct,
  ROUND(AVG(session_duration_min), 2)            AS avg_duration_min,
  ROUND(AVG(ad_revenue_usd), 4)                  AS avg_ad_rev_per_session
FROM `socialflow.analytics.fact_user_sessions`
GROUP BY year, platform
ORDER BY year, sessions DESC;

-- Content Type: Session Volume, Duration, Revenue
SELECT
  content_type,
  COUNT(*)                                        AS total_sessions,
  ROUND(AVG(session_duration_min), 2)            AS avg_session_min,
  ROUND(AVG(pages_viewed), 1)                    AS avg_pages_viewed,
  ROUND(AVG(ad_impressions), 1)                  AS avg_ad_impressions,
  ROUND(SUM(ad_revenue_usd), 2)                  AS total_ad_revenue,
  ROUND(
    SUM(ad_revenue_usd) * 100.0 / SUM(SUM(ad_revenue_usd)) OVER (), 2
  )                                               AS revenue_share_pct
FROM `socialflow.analytics.fact_user_sessions`
WHERE year = 2024
GROUP BY content_type
ORDER BY total_ad_revenue DESC;

-- 7-Day Rolling DAU with Anomaly Detection
WITH daily_active AS (
  SELECT
    session_date,
    COUNT(DISTINCT user_id)   AS dau
  FROM `socialflow.analytics.fact_user_sessions`
  GROUP BY session_date
),
rolling AS (
  SELECT
    session_date,
    dau,
    AVG(dau) OVER (
      ORDER BY session_date
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    )                         AS dau_7d_avg,
    STDDEV(dau) OVER (
      ORDER BY session_date
      ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    )                         AS dau_30d_stddev,
    AVG(dau) OVER (
      ORDER BY session_date
      ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    )                         AS dau_30d_avg
  FROM daily_active
)
SELECT
  session_date,
  dau,
  ROUND(dau_7d_avg, 0)        AS dau_7d_rolling_avg,
  CASE
    WHEN ABS(dau - dau_30d_avg) > 2 * dau_30d_stddev THEN 'ANOMALY'
    WHEN ABS(dau - dau_30d_avg) > 1.5 * dau_30d_stddev THEN 'WARNING'
    ELSE 'NORMAL'
  END                         AS anomaly_flag
FROM rolling
ORDER BY session_date DESC;

-- RFM Segmentation
WITH user_metrics AS (
  SELECT
    user_id,
    DATE_DIFF(
      DATE '2025-01-01',
      MAX(session_date), DAY
    )                         AS recency_days,
    COUNT(DISTINCT session_date) AS frequency,
    SUM(total_revenue_usd)    AS monetary
  FROM `socialflow.analytics.fact_user_sessions`
  WHERE year BETWEEN 2024 AND 2025
  GROUP BY user_id
),
rfm_scores AS (
  SELECT
    user_id,
    recency_days,
    frequency,
    ROUND(monetary, 2)        AS monetary,
    NTILE(5) OVER (ORDER BY recency_days DESC) AS r_score,
    NTILE(5) OVER (ORDER BY frequency)         AS f_score,
    NTILE(5) OVER (ORDER BY monetary)          AS m_score
  FROM user_metrics
)
SELECT
  user_id,
  r_score,
  f_score,
  m_score,
  (r_score + f_score + m_score)                 AS rfm_total,
  CASE
    WHEN r_score >= 4 AND f_score >= 4           THEN 'Champion'
    WHEN r_score >= 3 AND f_score >= 3           THEN 'Loyal User'
    WHEN r_score >= 4 AND f_score <= 2           THEN 'New User'
    WHEN r_score <= 2 AND f_score >= 4           THEN 'At Risk'
    WHEN r_score <= 2 AND f_score <= 2           THEN 'Lost User'
    ELSE 'Needs Attention'
  END                                             AS user_segment
FROM rfm_scores
ORDER BY rfm_total DESC;

-- ARPU by Subscription Tier and Region
SELECT
  f.year,
  f.region,
  u.subscription_type,
  COUNT(DISTINCT f.user_id)                       AS user_count,
  ROUND(SUM(f.total_revenue_usd), 2)             AS total_revenue,
  ROUND(
    SUM(f.total_revenue_usd) / NULLIF(COUNT(DISTINCT f.user_id), 0), 2
  )                                               AS arpu_usd,
  ROUND(
    SUM(f.total_revenue_usd) / NULLIF(COUNT(DISTINCT f.user_id), 0)
    - LAG(
        SUM(f.total_revenue_usd) / NULLIF(COUNT(DISTINCT f.user_id), 0)
      ) OVER (
        PARTITION BY f.region, u.subscription_type
        ORDER BY f.year
      ), 2
  )                                               AS arpu_yoy_change
FROM `socialflow.analytics.fact_user_sessions` f
JOIN `socialflow.analytics.dim_users` u
  ON f.user_id = u.user_id
WHERE f.year BETWEEN 2022 AND 2024
GROUP BY f.year, f.region, u.subscription_type
ORDER BY f.year, f.region, arpu_usd DESC;

-- D1 / D7 / D30 Retention by Acquisition Channel
WITH user_first_session AS (
  SELECT
    f.user_id,
    u.acquisition_channel,
    MIN(f.session_date)       AS first_session_date
  FROM `socialflow.analytics.fact_user_sessions` f
  JOIN `socialflow.analytics.dim_users` u
    ON f.user_id = u.user_id
  WHERE f.year = 2024
  GROUP BY f.user_id, u.acquisition_channel
),
retention_base AS (
  SELECT
    ufs.user_id,
    ufs.acquisition_channel,
    ufs.first_session_date,
    MAX(CASE
      WHEN DATE_DIFF(f.session_date, ufs.first_session_date, DAY) BETWEEN 1 AND 1
      THEN 1 ELSE 0 END)      AS retained_d1,
    MAX(CASE
      WHEN DATE_DIFF(f.session_date, ufs.first_session_date, DAY) BETWEEN 6 AND 8
      THEN 1 ELSE 0 END)      AS retained_d7,
    MAX(CASE
      WHEN DATE_DIFF(f.session_date, ufs.first_session_date, DAY) BETWEEN 28 AND 32
      THEN 1 ELSE 0 END)      AS retained_d30
  FROM user_first_session ufs
  JOIN `socialflow.analytics.fact_user_sessions` f
    ON ufs.user_id = f.user_id
  GROUP BY ufs.user_id, ufs.acquisition_channel, ufs.first_session_date
)
SELECT
  acquisition_channel,
  COUNT(*)                                        AS new_users,
  ROUND(AVG(retained_d1) * 100, 1)              AS d1_retention_pct,
  ROUND(AVG(retained_d7) * 100, 1)              AS d7_retention_pct,
  ROUND(AVG(retained_d30) * 100, 1)             AS d30_retention_pct
FROM retention_base
GROUP BY acquisition_channel
ORDER BY d30_retention_pct DESC;

-- Quarterly Seasonality Index
WITH quarterly_rev AS (
  SELECT
    year,
    quarter,
    SUM(total_revenue_usd)    AS quarterly_revenue
  FROM `socialflow.analytics.fact_user_sessions`
  WHERE year BETWEEN 2021 AND 2024
  GROUP BY year, quarter
),
annual_avg AS (
  SELECT
    year,
    AVG(quarterly_revenue)    AS avg_quarterly_revenue
  FROM quarterly_rev
  GROUP BY year
)
SELECT
  q.year,
  q.quarter,
  ROUND(q.quarterly_revenue / 1e6, 2)            AS revenue_million,
  ROUND(
    q.quarterly_revenue / a.avg_quarterly_revenue, 3
  )                                               AS seasonality_index,
  CASE
    WHEN q.quarterly_revenue / a.avg_quarterly_revenue > 1.1 THEN 'Peak'
    WHEN q.quarterly_revenue / a.avg_quarterly_revenue < 0.9 THEN 'Trough'
    ELSE 'Normal'
  END                                             AS season_label
FROM quarterly_rev q
JOIN annual_avg a ON q.year = a.year
ORDER BY q.year, q.quarter;

-- 12-Month Cohort LTV Analysis
WITH user_cohorts AS (
  SELECT
    u.user_id,
    DATE_TRUNC(u.signup_date, YEAR)               AS cohort_year,
    u.signup_date
  FROM `socialflow.analytics.dim_users` u
  WHERE u.signup_date IS NOT NULL
),
monthly_revenue AS (
  SELECT
    f.user_id,
    DATE_DIFF(
      DATE_TRUNC(f.session_date, MONTH),
      DATE_TRUNC(uc.signup_date, MONTH),
      MONTH
    )                                             AS months_since_signup,
    SUM(f.total_revenue_usd)                      AS monthly_rev
  FROM `socialflow.analytics.fact_user_sessions` f
  JOIN user_cohorts uc ON f.user_id = uc.user_id
  WHERE DATE_DIFF(f.session_date, uc.signup_date, MONTH) BETWEEN 0 AND 11
  GROUP BY f.user_id, months_since_signup
),
cumulative_ltv AS (
  SELECT
    uc.cohort_year,
    mr.months_since_signup,
    COUNT(DISTINCT mr.user_id)                    AS active_users,
    ROUND(SUM(mr.monthly_rev), 2)                 AS total_rev_at_month,
    ROUND(
      SUM(SUM(mr.monthly_rev)) OVER (
        PARTITION BY uc.cohort_year
        ORDER BY mr.months_since_signup
      ) / COUNT(DISTINCT uc.user_id), 2
    )                                             AS cumulative_ltv_per_user
  FROM user_cohorts uc
  JOIN monthly_revenue mr ON uc.user_id = mr.user_id
  GROUP BY uc.cohort_year, mr.months_since_signup
)
SELECT
  cohort_year,
  months_since_signup,
  active_users,
  cumulative_ltv_per_user
FROM cumulative_ltv
ORDER BY cohort_year, months_since_signup;

-- Pareto: Top Creators by Engagement (80/20 Rule)
WITH creator_engagement AS (
  SELECT
    c.creator_id,
    u.creator_tier,
    SUM(c.total_views)                            AS total_views,
    SUM(c.total_likes + c.total_shares + c.total_comments)
                                                  AS total_interactions,
    COUNT(c.content_id)                           AS content_pieces
  FROM `socialflow.analytics.dim_content` c
  JOIN `socialflow.analytics.dim_users` u
    ON c.creator_id = u.user_id
  WHERE u.is_creator = TRUE
    AND EXTRACT(YEAR FROM c.created_date) = 2024
  GROUP BY c.creator_id, u.creator_tier
),
ranked AS (
  SELECT
    creator_id,
    creator_tier,
    total_interactions,
    content_pieces,
    ROUND(total_interactions * 1.0 /
      SUM(total_interactions) OVER () * 100, 4)   AS interaction_share_pct,
    RANK() OVER (ORDER BY total_interactions DESC) AS engagement_rank,
    COUNT(*) OVER ()                               AS total_creators
  FROM creator_engagement
),
cumulative AS (
  SELECT
    *,
    SUM(interaction_share_pct) OVER (
      ORDER BY engagement_rank
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )                                             AS cumulative_share_pct,
    ROUND(engagement_rank * 100.0 / total_creators, 1) AS creator_percentile
  FROM ranked
)
SELECT
  creator_id,
  creator_tier,
  engagement_rank,
  creator_percentile,
  ROUND(total_interactions, 0)                    AS total_interactions,
  ROUND(interaction_share_pct, 4)                AS share_pct,
  ROUND(cumulative_share_pct, 2)                 AS cumulative_share_pct,
  CASE WHEN cumulative_share_pct <= 80 THEN 'Top 20%' ELSE 'Long Tail' END
                                                  AS pareto_group
FROM cumulative
ORDER BY engagement_rank
LIMIT 200;

-- Engagement Funnel Analysis
WITH action_counts AS (
  SELECT
    year,
    quarter,
    COUNTIF(action_type IN ('View','Like','Share','Comment','Post')) AS total_sessions,
    COUNTIF(action_type IN ('Like','Share','Comment','Post'))        AS liked_or_more,
    COUNTIF(action_type IN ('Share','Comment','Post'))               AS shared_or_more,
    COUNTIF(action_type IN ('Comment','Post'))                      AS commented_or_more,
    COUNTIF(action_type = 'Post')                                   AS posted
  FROM `socialflow.analytics.fact_user_sessions`
  WHERE year BETWEEN 2022 AND 2024
  GROUP BY year, quarter
)
SELECT
  year,
  quarter,
  total_sessions                                  AS step1_views,
  liked_or_more                                   AS step2_likes,
  shared_or_more                                  AS step3_shares,
  commented_or_more                               AS step4_comments,
  posted                                          AS step5_posts,
  ROUND(liked_or_more     * 100.0 / NULLIF(total_sessions, 0), 1)   AS like_rate_pct,
  ROUND(shared_or_more    * 100.0 / NULLIF(total_sessions, 0), 1)   AS share_rate_pct,
  ROUND(commented_or_more * 100.0 / NULLIF(total_sessions, 0), 1)   AS comment_rate_pct,
  ROUND(posted            * 100.0 / NULLIF(total_sessions, 0), 1)   AS post_rate_pct
FROM action_counts
ORDER BY year, quarter;

-- Churn Prediction Feature Engineering
WITH user_activity AS (
  SELECT
    user_id,
    -- Recency signals
    DATE_DIFF(DATE '2025-01-01', MAX(session_date), DAY)  AS days_since_last_session,
    -- Frequency signals
    COUNT(DISTINCT session_date)                           AS active_days_l30,
    COUNT(*)                                               AS total_sessions_l30,
    -- Engagement depth
    ROUND(AVG(session_duration_min), 2)                   AS avg_session_duration,
    ROUND(AVG(pages_viewed), 2)                           AS avg_pages_per_session,
    -- Content affinity
    COUNTIF(content_type = 'Video') * 1.0 / COUNT(*)     AS video_session_ratio,
    COUNTIF(action_type = 'Post') * 1.0 / COUNT(*)       AS posting_ratio,
    -- Revenue
    ROUND(SUM(total_revenue_usd), 4)                      AS revenue_l30,
    -- Trend: compare last 7 vs prior 23 days
    COUNTIF(session_date >= DATE_SUB(DATE '2025-01-01', INTERVAL 7 DAY))
                                                           AS sessions_last7d,
    COUNTIF(session_date < DATE_SUB(DATE '2025-01-01', INTERVAL 7 DAY))
                                                           AS sessions_prior23d
  FROM `socialflow.analytics.fact_user_sessions`
  WHERE session_date BETWEEN
    DATE_SUB(DATE '2025-01-01', INTERVAL 30 DAY)
    AND DATE '2025-01-01'
  GROUP BY user_id
)
SELECT
  ua.*,
  u.subscription_type,
  u.acquisition_channel,
  u.creator_tier,
  u.is_creator,
  -- Derived churn signal
  ROUND(sessions_last7d * 23.0 /
    NULLIF(sessions_prior23d * 7.0, 0), 3)                AS activity_trend_ratio,
  CASE
    WHEN days_since_last_session > 14 AND sessions_last7d = 0 THEN 1
    ELSE 0
  END                                                       AS churn_label
FROM user_activity ua
JOIN `socialflow.analytics.dim_users` u
  ON ua.user_id = u.user_id;

-- CPM Optimization Matrix: User Segment × Content Type
WITH session_cpm AS (
  SELECT
    f.user_id,
    f.content_type,
    f.platform,
    u.age_group,
    u.subscription_type,
    u.is_creator,
    f.ad_impressions,
    f.ad_revenue_usd,
    SAFE_DIVIDE(f.ad_revenue_usd, f.ad_impressions) * 1000 AS cpm
  FROM `socialflow.analytics.fact_user_sessions` f
  JOIN `socialflow.analytics.dim_users` u
    ON f.user_id = u.user_id
  WHERE f.year = 2024
    AND f.ad_impressions > 0
),
cpm_matrix AS (
  SELECT
    age_group,
    subscription_type,
    content_type,
    platform,
    COUNT(*)                                      AS session_count,
    SUM(ad_impressions)                           AS total_impressions,
    ROUND(SUM(ad_revenue_usd), 2)                AS total_ad_revenue,
    ROUND(AVG(cpm), 4)                           AS avg_cpm,
    ROUND(PERCENTILE_CONT(cpm, 0.5) OVER (
      PARTITION BY age_group, subscription_type, content_type, platform
    ), 4)                                        AS median_cpm,
    ROUND(STDDEV(cpm), 4)                        AS cpm_stddev
  FROM session_cpm
  GROUP BY age_group, subscription_type, content_type, platform
)
SELECT
  *,
  RANK() OVER (ORDER BY avg_cpm DESC)            AS cpm_rank
FROM cpm_matrix
WHERE session_count >= 100          -- statistical significance filter
ORDER BY avg_cpm DESC
LIMIT 50;