/*Group by user_id, game_name, payment_month*/

WITH payment_month AS (
  SELECT
     user_id
    ,game_name
    ,DATE_TRUNC('month', payment_date)::date AS payment_month
    ,SUM(revenue_amount_usd) AS total_revenue
  FROM games_payments
  GROUP BY 
     user_id
    ,game_name
    ,DATE_TRUNC('month', payment_date)
),

/*Calculate prior and next payment months, is new user*/

payments AS (
  SELECT
     user_id
    ,game_name
    ,payment_month
    ,LEAD(payment_month) OVER(PARTITION BY user_id, game_name ORDER BY payment_month) AS next_payment_month
    ,LAG(payment_month) OVER(PARTITION BY user_id, game_name ORDER BY payment_month) AS prior_payment_month
    ,CASE 
      WHEN payment_month = MIN(payment_month) OVER (PARTITION BY user_id, game_name) THEN 1 
      ELSE 0 
    END AS is_new_user
  FROM payment_month
),

/*Calculate revenue per current and prior months*/

revenue_comparison AS (
  SELECT
     r1.user_id
    ,r1.game_name
    ,r1.payment_month
    ,r1.total_revenue
    ,r2.total_revenue AS prior_month_revenue
  FROM payment_month r1
  LEFT JOIN payment_month r2 
    ON r1.user_id = r2.user_id 
      AND r1.game_name = r2.game_name 
      AND r1.payment_month = r2.payment_month + INTERVAL '1 month'
)

/*Calculation churned and reactivated users, expansion and contraction MRR*/

SELECT 
   pm.user_id
  ,pm.game_name
  ,pm.payment_month AS payment_month
  ,pm.total_revenue AS revenue_amount_usd
  ,age
  ,language
  ,p.is_new_user
  ,CASE 
    WHEN p.next_payment_month IS NULL 
      OR (DATE_PART('month', p.next_payment_month) - DATE_PART('month', pm.payment_month) > 1) THEN 1 
    ELSE 0 
  END AS is_churned
  ,CASE 
    WHEN DATE_PART('month', pm.payment_month) - DATE_PART('month', p.prior_payment_month) > 1 THEN 1 
    ELSE 0 
  END AS is_reactivated
  ,CASE 
    WHEN rc.prior_month_revenue IS NULL THEN NULL
    WHEN rc.total_revenue > rc.prior_month_revenue THEN rc.total_revenue - rc.prior_month_revenue
    ELSE NULL
  END AS expansion_mrr
  ,CASE 
    WHEN rc.prior_month_revenue IS NULL THEN NULL
    WHEN rc.total_revenue < rc.prior_month_revenue THEN rc.prior_month_revenue - rc.total_revenue
    ELSE NULL
  END AS contraction_mrr
FROM payment_month AS pm
LEFT JOIN payments AS p 
  ON pm.user_id = p.user_id 
    AND pm.game_name = p.game_name 
    AND pm.payment_month = p.payment_month
LEFT JOIN revenue_comparison AS rc 
  ON pm.user_id = rc.user_id
    AND pm.game_name = rc.game_name
    AND pm.payment_month = rc.payment_month
LEFT JOIN games_paid_users AS pu 
  ON pm.user_id = pu.user_id 
    AND pm.game_name = pu.game_name
ORDER BY 
   pm.payment_month
  ,pm.user_id
  ,pm.game_name

SELECT 
   PARSE_DATE('%Y%m%d', event_date) AS event_date
  ,FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_MICROS(event_timestamp)) AS event_datetime
  ,user_pseudo_id
  ,
    (
      SELECT
      value.int_value
      FROM
      UNNEST(event_params)
      WHERE
        KEY='ga_session_id'
  ) AS session_id
  ,
    (
      SELECT
      value.string_value
      FROM
      UNNEST(event_params)
      WHERE
        KEY='page_title'
    ) AS page_title
  ,event_name
  ,geo.country
  ,device.category AS device_category
  ,traffic_source.source
  ,traffic_source.medium
  ,traffic_source.name AS campaign
  ,device.language
  ,device.operating_system
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name IN 
    (
    'session_start',
    'view_item',
    'add_to_cart',
    'begin_checkout',
    'add_shipping_info',
    'add_payment_info',
    'purchase')