Network Size

Query

With 

subs AS (
  SELECT
    s.ID,
    convert_timezone('UTC','America/Los_Angeles',TO_TIMESTAMP(CREATED))::DATE AS created_date,
    CASE WHEN s.ENDED_AT IS NOT NULL THEN convert_timezone('UTC','America/Los_Angeles',TO_TIMESTAMP(s.ENDED_AT::NUMBER))::DATE END AS ended_date,
    CASE WHEN s.TRIAL_END IS NOT NULL THEN convert_timezone('UTC','America/Los_Angeles',TO_TIMESTAMP(s.TRIAL_END::NUMBER))::DATE END AS trial_end_date,
    s.PLAN:amount::NUMBER AS plan_amount
  FROM RAW_DB.STRIPE.SUBSCRIPTIONS s
  WHERE s.LIVEMODE = true
    AND s.STATUS != 'incomplete_expired'
),

devices AS (
  SELECT STRIPE_SUBSCRIPTION_ID, MIN(convert_timezone('UTC','America/Los_Angeles',CREATED_AT))::DATE AS device_created_date
  FROM RAW_DB.TINCAN.LEGACY_DEVICES
  WHERE STRIPE_SUBSCRIPTION_ID IS NOT NULL
  GROUP BY 1
)

SELECT
  id.dt,
  COUNT(*) AS active_subs_with_device,
  COUNT(CASE 
    WHEN s.plan_amount > 0 
      AND (s.trial_end_date IS NULL OR s.trial_end_date <= id.dt)
    THEN 1 END) AS paid_subscriptions,
  COUNT(CASE 
    WHEN s.plan_amount > 0 
      AND s.trial_end_date IS NOT NULL 
      AND s.trial_end_date > id.dt
    THEN 1 END) AS trial_subscriptions,
  COUNT(CASE WHEN s.plan_amount = 0 THEN 1 END) AS free_subscriptions
FROM included_dates id
INNER JOIN subs s
  ON s.created_date <= id.dt
  AND (s.ended_date IS NULL OR s.ended_date > id.dt)
INNER JOIN devices d
  ON s.ID = d.STRIPE_SUBSCRIPTION_ID
  AND d.device_created_date <= id.dt
GROUP BY ALL
ORDER BY 1

Overview

Produces a daily count of active subscriptions that have at least one associated device activated on or before each date. Used in the call lengths report to normalize call activity metrics against the size of the active network.

A simpler variant of the network growth and health network_size query — activations and new contacts columns are omitted here since they are not needed for call length analysis.

Depends on: included_dates (date_spine)

---

Output Columns

Column	Description
dt	Calendar date
active_subs_with_device	Total active subscriptions with at least one device activated on or before dt
paid_subscriptions	Active paid subscriptions (plan_amount > 0) whose trial has ended or never existed
trial_subscriptions	Active paid subscriptions (plan_amount > 0) still within a trial on dt
free_subscriptions	Active subscriptions on a free plan (plan_amount = 0)

---

CTEs

subs

Source: RAW_DB.STRIPE.SUBSCRIPTIONS

Filters to live-mode subscriptions, excluding incomplete_expired status. Extracts created_date, ended_date, trial_end_date, and plan_amount (in cents) from the PLAN JSON field. All timestamps converted from UTC to America/Los_Angeles.

devices

Source: RAW_DB.TINCAN.LEGACY_DEVICES

For each Stripe subscription ID, finds the earliest device activation date. Used to ensure a subscription is only counted once a device has been physically activated.

---

Notes

• Both joins to subs and devices are inner joins — a subscription must have an associated activated device to appear in results.
• plan_amount is in cents. A value of 0 indicates a free plan; any positive value indicates a paid plan.
• Trial logic: a subscription is in trial if trial_end_date is set and falls after dt; otherwise counted as paid (assuming plan_amount > 0).