-- Active Trials as of your [targeted_date]

SELECT
  COUNT(*)
FROM
  [revenuecat_data_table]
WHERE date(effective_end_time) > [targeted_date]
  AND date(start_time) <= [targeted_date]
  AND is_trial_period = 'true'
  AND (effective_end_time IS NULL OR DATE_DIFF('s', start_time, effective_end_time)::float > 0)
  AND ownership_type != 'FAMILY_SHARED'
  AND store != 'promotional'
  AND is_sandbox != 'true'

-- The RevenueCat Active Trials chart excludes
-- promotional transactions and transactions resulting from family sharing
-- since they do not reflect auto-renewing future payments.

-- Active Subscriptions as of your [targeted_date]

SELECT
  COUNT(*)
FROM
  [revenuecat_data_table]
WHERE date(effective_end_time) > [targeted_date]
  AND date(start_time) <= [targeted_date]
  AND is_trial_period = 'false'
  AND (effective_end_time IS NULL OR DATE_DIFF('s', start_time, effective_end_time)::float > 0)
  AND ownership_type != 'FAMILY_SHARED'
  AND store != 'promotional'
  AND is_sandbox != 'true'

-- The RevenueCat Active Subscriptions chart excludes trials,
-- promotional transactions, and transactions resulting from family sharing
-- since they do not reflect auto-renewing future payments.

-- Revenue generated on [targeted_date]

SELECT
  SUM(purchase_price_in_usd) as total_revenue,
  SUM(price_in_usd) as total_revenue_net_of_refunds,
  SUM(price_in_usd * (1 - tax_percentage - commission_percentage)) as proceeds
FROM
  [revenuecat_data_table]
WHERE date(start_time) = [targeted_date]
  AND is_trial_period = 'false'
  AND ownership_type != 'FAMILY_SHARED'
  AND store != 'promotional'
  AND is_sandbox != 'true'

-- Transactions which have been refunded can be identified through the refunded_at field.

-- How many Active Subscriptions do I have with a given custom attribute value?
  
SELECT
  COUNT(*)
FROM
  [revenuecat_data_table] rc
  
WHERE date(effective_end_time) > [targeted_date]
  AND date(start_time) <= [targeted_date]
  AND is_trial_period = 'false'
  AND (effective_end_time IS NULL OR DATE_DIFF('s', start_time, effective_end_time)::float > 0)
  AND ownership_type != 'FAMILY_SHARED'
  AND store != 'promotional'
  AND is_sandbox != 'true'
  AND json_extract_path_text(custom_subscriber_attributes, '[custom_attribute_key].value') = [custom_attribute_value]

-- What is my split of Active Subs by auto renew status?
  
SELECT
  CASE 
    WHEN unsubscribe_detected_at IS NOT NULL THEN 'Set to cancel' 
    ELSE 'Set to renew' 
    END) as auto_renew_status,
  COUNT(*) as active_subscriptions
FROM
  [revenuecat_data_table] rc
  
WHERE date(effective_end_time) > [targeted_date]
  AND date(start_time) <= [targeted_date]
  AND is_trial_period = 'false'
  AND (effective_end_time IS NULL OR DATE_DIFF('s', start_time, effective_end_time)::float > 0)
  AND ownership_type != 'FAMILY_SHARED'
  AND store != 'promotional'
  AND is_sandbox != 'true'
  GROUP BY 1

-- What is my weekly revenue, where Monday is set as the start day of the week?

SELECT
  date_trunc('week', start_time) as week,
  SUM(price_in_usd) as total_revenue
FROM
  [revenuecat_data_table]
WHERE date(start_time) BETWEEN [targeted_period_start_date] AND [targeted_period_end_date]
  AND is_trial_period = 'false'
  AND ownership_type != 'FAMILY_SHARED'
  AND store != 'promotional'
  AND is_sandbox != 'true'
GROUP BY week

-- What is my Realized LTV of each monthly subscription cohort, segmented by whether they were offered a trial?
  
WITH 
(SELECT
  MIN(start_time) as subscription_start_time,
  original_store_transaction_id,
  MAX(is_trial_period) as had_a_trial,
  SUM(price_in_usd) as realized_ltv
FROM
  [revenuecat_data_table]
WHERE date(start_time) > [targeted_period_start_date]
  AND ownership_type != 'FAMILY_SHARED'
  AND store != 'promotional'
  AND is_sandbox != 'true'
  GROUP BY original_store_transaction_id) as subscriptions
  
SELECT
  to_char(first_start_time, 'YYYY-MM') as subscription_start_month,
  had_a_trial,
  COUNT() as subscriptions,
  SUM(realized_ltv) as realized_ltv,
  SUM(realized_ltv) / COUNT() as realized_ltv_per_subscription
FROM
  subscriptions

-- What portion of my Active Trials are in a grace period?
  
SELECT
  CASE
    WHEN grace_period_end_time IS NOT NULL THEN 'in_grace_period'
    ELSE 'in_trial_period'
    END as period_type,
  COUNT(*) as active_trials
FROM
  [revenuecat_data_table]
WHERE date(effective_end_time) > [targeted_date]
  AND date(start_time) <= [targeted_date]
  AND is_trial_period = 'true'
  AND (effective_end_time IS NULL OR DATE_DIFF('s', start_time, effective_end_time)::float > 0)
  AND ownership_type != 'FAMILY_SHARED'
  AND store != 'promotional'
  AND is_sandbox != 'true'
GROUP BY period_type

-- What is my Realized LTV per Paying Customer cohorted by First Purchase Date?
  
WITH filtered_transactions AS
  (SELECT *
  FROM [revenuecat_data_table] rc
  WHERE is_trial_period = 'false'
    AND was_refunded = 'false'
    AND ownership_type = 'PURCHASED'
    AND is_sandbox != 'true'
    AND store != 'promotional'
    AND price > 0),

first_purchase_dates AS
  (SELECT
    rc_original_app_user_id,
    MIN(start_time) as first_purchase_date
  FROM filtered_transactions ft
  GROUP BY 1)

SELECT
  DATE(fpd.first_purchase_date) AS first_purchase_date,
  COUNT(DISTINCT rc_original_app_user_id) AS paying_customers,
  SUM(CASE WHEN DATEADD(day, 7, first_purchase_date) > start_time THEN price_in_usd ELSE 0 END)::DECIMAL(18,2) AS total_ltv_7_days,
  SUM(CASE WHEN DATEADD(day, 30, first_purchase_date) > start_time THEN price_in_usd ELSE 0 END)::DECIMAL(18,2) AS total_ltv_30_days,
  SUM(CASE WHEN DATEADD(month, 6, first_purchase_date) > start_time THEN price_in_usd ELSE 0 END)::DECIMAL(18,2) AS total_ltv_6_months,
  SUM(CASE WHEN DATEADD(month, 12, first_purchase_date) > start_time THEN price_in_usd ELSE 0 END)::DECIMAL(18,2) AS total_ltv_12_months,
  SUM(CASE WHEN DATEADD(month, 24, first_purchase_date) > start_time THEN price_in_usd ELSE 0 END)::DECIMAL(18,2) AS total_ltv_24_months,
  SUM(price_in_usd)::DECIMAL(18,2) AS total_ltv_unbounded,
  (SUM(CASE WHEN DATEADD(day, 7, first_purchase_date) > start_time THEN price_in_usd ELSE 0 END)/COUNT(DISTINCT rc_original_app_user_id))::DECIMAL(18,2) AS avg_ltv_7_days,
  (SUM(CASE WHEN DATEADD(day, 30, first_purchase_date) > start_time THEN price_in_usd ELSE 0 END)/COUNT(DISTINCT rc_original_app_user_id))::DECIMAL(18,2) AS avg_ltv_30_days,
  (SUM(CASE WHEN DATEADD(month, 6, first_purchase_date) > start_time THEN price_in_usd ELSE 0 END)/COUNT(DISTINCT rc_original_app_user_id))::DECIMAL(18,2) AS avg_ltv_6_months,
  (SUM(CASE WHEN DATEADD(month, 12, first_purchase_date) > start_time THEN price_in_usd ELSE 0 END)/COUNT(DISTINCT rc_original_app_user_id))::DECIMAL(18,2) AS avg_ltv_12_months,
  (SUM(CASE WHEN DATEADD(month, 23, first_purchase_date) > start_time THEN price_in_usd ELSE 0 END)/COUNT(DISTINCT rc_original_app_user_id))::DECIMAL(18,2) AS avg_ltv_24_months,
  (SUM(price_in_usd)/COUNT(DISTINCT rc_original_app_user_id))::DECIMAL(18,2) AS avg_ltv_unbounded
FROM filtered_transactions ft
LEFT JOIN first_purchase_dates fpd 
  ON fpd.rc_original_app_user_id = ft.rc_original_app_user_id
GROUP BY 1