-- 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(price_in_usd) as revenue
FROM
  [revenuecat_data_table]
WHERE 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'

-- Transactions which are refunded can be identified through the refunded_at field.
-- Once refunded, price_in_usd will be set to $0, so revenue will always be net of refunds.

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

-- 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 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 (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 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