All Data Join

Query

WITH base_all_completed_days AS (
  SELECT
      id.dt as date,
      id.period_start_date as period_start,

      n.active_subs_with_device as active_network,
      n.activations,
      n.new_contacts_created,

      c.active_devices,
      c.users_who_both_made_and_received_calls,
      c.answered_calls,
      c.outbound_calls,
      c.answered_outbound,
      c.inbound_calls,
      c.answered_inbound,
      c.external_calls,
      c.answered_external,
      c.voicemails_left,
      c.voicemails_received,
      c.total_talk_hours

  FROM included_dates id
  LEFT OUTER JOIN daily_calls c
    ON id.dt = c.date
  left outer join network_size n
    ON id.dt = n.dt
),

base AS (
  SELECT *
  FROM base_all_completed_days
  WHERE
    {{ include_current_period }} <> 'No'
    OR period_start <
      CASE
        WHEN {{ time_grain }} = 'Day' THEN CURRENT_DATE
        WHEN {{ time_grain }} = 'Week' THEN DATE_TRUNC('week', CURRENT_DATE)
        WHEN {{ time_grain }} = 'Month' THEN DATE_TRUNC('month', CURRENT_DATE)
        WHEN {{ time_grain }} = 'Quarter' THEN DATE_TRUNC('quarter', CURRENT_DATE)
      END
),

ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY period_start
      ORDER BY date DESC
    ) AS period_day_rank
  FROM base
),

forecast_window AS (
  SELECT
    CASE
      WHEN {{ forecast_basis }} = 'Trailing 7 days' THEN 7
      WHEN {{ forecast_basis }} = 'Trailing 14 days' THEN 14
      WHEN {{ forecast_basis }} = 'Trailing 28 days' THEN 28
      WHEN {{ forecast_basis }} = 'Trailing 56 days' THEN 56
      WHEN {{ forecast_basis }} = 'Current period to date' THEN
        CASE
          WHEN {{ time_grain }} = 'Week' THEN GREATEST(DATEDIFF('day', DATE_TRUNC('week', CURRENT_DATE), CURRENT_DATE), 1)
          WHEN {{ time_grain }} = 'Month' THEN GREATEST(DATEDIFF('day', DATE_TRUNC('month', CURRENT_DATE), CURRENT_DATE), 1)
          WHEN {{ time_grain }} = 'Quarter' THEN GREATEST(DATEDIFF('day', DATE_TRUNC('quarter', CURRENT_DATE), CURRENT_DATE), 1)
          ELSE 1
        END
    END AS lookback_days,

    CASE
      WHEN {{ forecast_basis }} = 'Trailing 7 days' THEN CURRENT_DATE - INTERVAL 7 DAY
      WHEN {{ forecast_basis }} = 'Trailing 14 days' THEN CURRENT_DATE - INTERVAL 14 DAY
      WHEN {{ forecast_basis }} = 'Trailing 28 days' THEN CURRENT_DATE - INTERVAL 28 DAY
      WHEN {{ forecast_basis }} = 'Trailing 56 days' THEN CURRENT_DATE - INTERVAL 56 DAY
      WHEN {{ forecast_basis }} = 'Current period to date' THEN
        CASE
          WHEN {{ time_grain }} = 'Week' THEN DATE_TRUNC('week', CURRENT_DATE)
          WHEN {{ time_grain }} = 'Month' THEN DATE_TRUNC('month', CURRENT_DATE)
          WHEN {{ time_grain }} = 'Quarter' THEN DATE_TRUNC('quarter', CURRENT_DATE)
          ELSE CURRENT_DATE - INTERVAL 1 DAY
        END
    END AS window_start_date,

    CASE
      WHEN {{ forecast_basis }} = 'Trailing 7 days' THEN CURRENT_DATE - INTERVAL 8 DAY
      WHEN {{ forecast_basis }} = 'Trailing 14 days' THEN CURRENT_DATE - INTERVAL 15 DAY
      WHEN {{ forecast_basis }} = 'Trailing 28 days' THEN CURRENT_DATE - INTERVAL 29 DAY
      WHEN {{ forecast_basis }} = 'Trailing 56 days' THEN CURRENT_DATE - INTERVAL 57 DAY
      WHEN {{ forecast_basis }} = 'Current period to date' THEN
        CASE
          WHEN {{ time_grain }} = 'Week' THEN DATE_TRUNC('week', CURRENT_DATE) - INTERVAL 1 DAY
          WHEN {{ time_grain }} = 'Month' THEN DATE_TRUNC('month', CURRENT_DATE) - INTERVAL 1 DAY
          WHEN {{ time_grain }} = 'Quarter' THEN DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL 1 DAY
          ELSE CURRENT_DATE
        END
    END AS snapshot_anchor_date
),

forecast_window_single AS (
  SELECT
    MAX(lookback_days) AS lookback_days,
    MAX(window_start_date) AS window_start_date,
    MAX(snapshot_anchor_date) AS snapshot_anchor_date
  FROM forecast_window
),

forecast_constants AS (
  SELECT
    lookback_days,
    window_start_date,
    snapshot_anchor_date
  FROM forecast_window_single
),

forecast_run_rate_summed AS (
  SELECT
    AVG(COALESCE(answered_calls, 0)) AS avg_daily_answered_calls,
    AVG(COALESCE(outbound_calls, 0)) as avg_daily_outbound_calls,
    AVG(COALESCE(answered_outbound, 0)) as avg_daily_answered_outbound,
    AVG(COALESCE(inbound_calls, 0)) as avg_daily_inbound_calls,
    AVG(COALESCE(answered_inbound, 0)) AS avg_daily_answered_inbound,
    AVG(COALESCE(external_calls, 0)) AS avg_daily_external_calls,
    AVG(COALESCE(answered_external, 0)) AS avg_daily_answered_external,
    AVG(COALESCE(voicemails_left, 0)) AS avg_daily_voicemails_left,
    AVG(COALESCE(voicemails_received, 0)) AS avg_daily_voicemails_received,
    AVG(COALESCE(total_talk_hours, 0)) AS avg_daily_total_talk_hours,
    AVG(COALESCE(activations, 0)) as avg_daily_activations,
    AVG(COALESCE(new_contacts_created, 0)) as avg_daily_new_contacts_created

  FROM ranked
  CROSS JOIN forecast_constants fc
  WHERE period_start >= fc.window_start_date
),


--Separate logic needed to include snapshot and count_distinct metrics in the forecasts 
--(actually, this logic is busted for some count_distinct metrics, so I'm temporarily killing forecasts for this type of metric & will come back t it in the future)

snapshot_points AS (
  SELECT
    fc.lookback_days,

    --MAX(CASE WHEN b.date = CURRENT_DATE - INTERVAL 1 DAY THEN b.active_devices END) AS active_devices_end,
    --MAX(CASE WHEN b.date = fc.snapshot_anchor_date THEN b.active_devices END) AS active_devices_start,
    --MAX(CASE WHEN b.date = CURRENT_DATE - INTERVAL 1 DAY THEN b.users_who_both_made_and_received_calls END) AS users_who_both_made_and_received_calls_end,
    --MAX(CASE WHEN b.date = fc.snapshot_anchor_date THEN b.users_who_both_made_and_received_calls END) AS users_who_both_made_and_received_calls_start,
    MAX(CASE WHEN b.date = CURRENT_DATE - INTERVAL 1 DAY THEN b.active_network END) AS active_network_end,
    MAX(CASE WHEN b.date = fc.snapshot_anchor_date THEN b.active_network END) AS active_network_start

  FROM base_all_completed_days b
  CROSS JOIN forecast_constants fc
  GROUP BY fc.lookback_days
),

forecast_run_rate_snapshots AS (
  SELECT
    --Commented out metrics are the ones that don't work with the current logic, may get forecasted via another means later
    --(active_devices_end - active_devices_start) / NULLIF(lookback_days, 0) AS avg_daily_diff_active_devices,
    --(users_who_both_made_and_received_calls_end - users_who_both_made_and_received_calls_start) / NULLIF(lookback_days, 0) AS avg_daily_diff_users_who_both_made_and_received_calls,
    (active_network_end - active_network_start) / NULLIF(lookback_days, 0) AS avg_daily_diff_active_network
  FROM snapshot_points
),

period_rollup AS (
  SELECT
    period_start,

    CASE
      WHEN {{ time_grain }} = 'Day' THEN
        CAST(EXTRACT(YEAR FROM period_start) AS VARCHAR) || '-' ||
        LPAD(CAST(EXTRACT(MONTH FROM period_start) AS VARCHAR), 2, '0') || '-' ||
        LPAD(CAST(EXTRACT(DAY FROM period_start) AS VARCHAR), 2, '0')
      WHEN {{ time_grain }} = 'Week' THEN
        CAST(EXTRACT(YEAR FROM period_start) AS VARCHAR) || '-' ||
        LPAD(CAST(EXTRACT(MONTH FROM period_start) AS VARCHAR), 2, '0') || '-' ||
        LPAD(CAST(EXTRACT(DAY FROM period_start) AS VARCHAR), 2, '0')
      WHEN {{ time_grain }} = 'Month' THEN
        CAST(EXTRACT(YEAR FROM period_start) AS VARCHAR) || '-' ||
        LPAD(CAST(EXTRACT(MONTH FROM period_start) AS VARCHAR), 2, '0')
      WHEN {{ time_grain }} = 'Quarter' THEN
        CAST(EXTRACT(YEAR FROM period_start) AS VARCHAR) || '-Q' ||
        CAST(EXTRACT(QUARTER FROM period_start) AS VARCHAR)
    END AS period,


    SUM(COALESCE(answered_calls, 0)) AS answered_calls_actual,
    SUM(COALESCE(outbound_calls, 0)) AS outbound_calls_actual,
    SUM(COALESCE(answered_outbound, 0)) AS answered_outbound_actual,
    SUM(COALESCE(inbound_calls, 0)) AS inbound_calls_actual,
    SUM(COALESCE(answered_inbound, 0)) AS answered_inbound_actual,
    SUM(COALESCE(external_calls, 0)) AS external_calls_actual, 
    SUM(COALESCE(answered_external, 0)) AS answered_external_actual,
    SUM(COALESCE(voicemails_left, 0)) AS voicemails_left_actual,
    SUM(COALESCE(voicemails_received, 0)) AS voicemails_received_actual,
    SUM(COALESCE(total_talk_hours, 0)) AS total_talk_hours_actual,
    SUM(COALESCE(activations, 0)) AS activations_actual,
    SUM(COALESCE(new_contacts_created, 0)) AS new_contacts_created_actual,

    --More logic for snapshot metrics
    MAX(CASE WHEN period_day_rank = 1 THEN active_devices END) AS active_devices_actual,
    MAX(CASE WHEN period_day_rank = 1 THEN users_who_both_made_and_received_calls END) AS users_who_both_made_and_received_calls_actual,
    MAX(CASE WHEN period_day_rank = 1 THEN active_network END) AS active_network_actual,

    CASE
      WHEN {{ include_current_period }} <> 'No'
       AND {{ time_grain }} <> 'Day'
       AND period_start =
         CASE
           WHEN {{ time_grain }} = 'Week' THEN DATE_TRUNC('week', CURRENT_DATE)
           WHEN {{ time_grain }} = 'Month' THEN DATE_TRUNC('month', CURRENT_DATE)
           WHEN {{ time_grain }} = 'Quarter' THEN DATE_TRUNC('quarter', CURRENT_DATE)
         END
      THEN 'Current Period'
      ELSE NULL
    END AS current_period_flag,

    CASE
      WHEN {{ time_grain }} = 'Week'
        THEN DATEDIFF('day', CURRENT_DATE, DATE_TRUNC('week', CURRENT_DATE) + INTERVAL 6 DAY) + 1
      WHEN {{ time_grain }} = 'Month'
        THEN DATEDIFF('day', CURRENT_DATE, DATE_TRUNC('month', CURRENT_DATE) + INTERVAL 1 MONTH - INTERVAL 1 DAY) + 1
      WHEN {{ time_grain }} = 'Quarter'
        THEN DATEDIFF('day', CURRENT_DATE, DATE_TRUNC('quarter', CURRENT_DATE) + INTERVAL 3 MONTH - INTERVAL 1 DAY) + 1
      ELSE 0
    END AS days_remaining_in_period

  FROM ranked
  GROUP BY 1
),

final AS (
  SELECT
    p.period_start,
    case 
        when {{ include_current_period }} = 'Yes (with rest-of-period forecasts)' AND p.current_period_flag = 'Current Period' THEN p.period || ' (Forecast)'
        WHEN {{ include_current_period }} = 'Yes (incomplete data)' AND p.current_period_flag = 'Current Period' THEN p.period || ' (Incomplete)'
        else p.period end as period,
    p.current_period_flag,

CASE
  WHEN p.current_period_flag = 'Current Period'
   AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
  THEN p.answered_calls_actual + (s.avg_daily_answered_calls * p.days_remaining_in_period)
  ELSE p.answered_calls_actual
END AS answered_calls,

CASE
  WHEN p.current_period_flag = 'Current Period'
   AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
  THEN p.outbound_calls_actual + (s.avg_daily_outbound_calls * p.days_remaining_in_period)
  ELSE p.outbound_calls_actual
END AS outbound_calls,

CASE
  WHEN p.current_period_flag = 'Current Period'
   AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
  THEN p.answered_outbound_actual + (s.avg_daily_answered_outbound * p.days_remaining_in_period)
  ELSE p.answered_outbound_actual
END AS answered_outbound,

CASE
  WHEN p.current_period_flag = 'Current Period'
   AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
  THEN p.inbound_calls_actual + (s.avg_daily_inbound_calls * p.days_remaining_in_period)
  ELSE p.inbound_calls_actual
END AS inbound_calls,

CASE
  WHEN p.current_period_flag = 'Current Period'
   AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
  THEN p.answered_inbound_actual + (s.avg_daily_answered_inbound * p.days_remaining_in_period)
  ELSE p.answered_inbound_actual
END AS answered_inbound,

CASE
  WHEN p.current_period_flag = 'Current Period'
   AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
  THEN p.external_calls_actual + (s.avg_daily_external_calls * p.days_remaining_in_period)
  ELSE p.external_calls_actual
END AS external_calls,

CASE
  WHEN p.current_period_flag = 'Current Period'
   AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
  THEN p.answered_external_actual + (s.avg_daily_answered_external * p.days_remaining_in_period)
  ELSE p.answered_external_actual
END AS answered_external,

CASE
  WHEN p.current_period_flag = 'Current Period'
   AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
  THEN p.voicemails_left_actual + (s.avg_daily_voicemails_left * p.days_remaining_in_period)
  ELSE p.voicemails_left_actual
END AS voicemails_left,

CASE
  WHEN p.current_period_flag = 'Current Period'
   AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
  THEN p.voicemails_received_actual + (s.avg_daily_voicemails_received * p.days_remaining_in_period)
  ELSE p.voicemails_received_actual
END AS voicemails_received,

CASE
  WHEN p.current_period_flag = 'Current Period'
   AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
  THEN p.total_talk_hours_actual + (s.avg_daily_total_talk_hours * p.days_remaining_in_period)
  ELSE p.total_talk_hours_actual
END AS total_talk_hours,

CASE
  WHEN p.current_period_flag = 'Current Period'
   AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
  THEN p.activations_actual + (s.avg_daily_activations * p.days_remaining_in_period)
  ELSE p.activations_actual
END AS activations,

CASE
  WHEN p.current_period_flag = 'Current Period'
   AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
  THEN p.new_contacts_created_actual + (s.avg_daily_new_contacts_created * p.days_remaining_in_period)
  ELSE p.new_contacts_created_actual
END AS new_contacts_created,

--Logic for snapshop and / or count_distinct metrics
    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN 0
      ELSE p.active_devices_actual
    END AS active_devices,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN 0
      ELSE p.users_who_both_made_and_received_calls_actual
    END AS users_who_both_made_and_received_calls,

    CASE
      WHEN p.current_period_flag = 'Current Period'
       AND {{ include_current_period }} = 'Yes (with rest-of-period forecasts)'
      THEN p.active_network_actual + (snap.avg_daily_diff_active_network * p.days_remaining_in_period)
      ELSE p.active_network_actual
    END AS active_network


  FROM period_rollup p
  CROSS JOIN forecast_run_rate_summed s
  CROSS JOIN forecast_run_rate_snapshots snap
),

chart_window AS (
  SELECT
    CASE
      WHEN {{ custom_date_start }} IS NOT NULL
        THEN CAST({{ custom_date_start }} AS DATE)

      WHEN {{ time_grain }} = 'Day'
        THEN CURRENT_DATE - INTERVAL 180 DAY

      WHEN {{ time_grain }} = 'Week'
        THEN DATE_TRUNC('week', CURRENT_DATE) - INTERVAL 21 WEEK

      WHEN {{ time_grain }} = 'Month'
        THEN DATE_TRUNC('month', CURRENT_DATE) - INTERVAL 12 MONTH

      WHEN {{ time_grain }} = 'Quarter'
        THEN DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL 8 QUARTER
    END AS start_date,

    CASE
      WHEN {{ custom_date_end }} IS NOT NULL
        THEN CAST({{ custom_date_end }} AS DATE)

      ELSE CURRENT_DATE
    END AS end_date
),

final_filtered AS (
  SELECT f.*
  FROM final f
  CROSS JOIN chart_window w
  WHERE f.period_start >= w.start_date
    AND f.period_start <= w.end_date
)

SELECT *
FROM final_filtered f
ORDER BY period_start;

Overview

The central reporting query for the network growth and health dashboard. Joins network_size and daily_calls onto the date spine, rolls daily data up to the selected time grain, and optionally projects the current incomplete period forward using a run-rate forecast.

Depends on: included_dates (date_spine), network_size, daily_calls

Parameters

Parameter	Accepted Values	Description
`{{ time_grain }}`	`'Day'`, `'Week'`, `'Month'`, `'Quarter'`	Aggregation period for all output metrics
`{{ include_current_period }}`	`'No'`, `'Yes (incomplete data)'`, `'Yes (with rest-of-period forecasts)'`	Controls whether the current in-progress period is included, and if so, whether remaining days are forecast
`{{ forecast_basis }}`	`'Trailing 7 days'`, `'Trailing 14 days'`, `'Trailing 28 days'`, `'Trailing 56 days'`, `'Current period to date'`	Lookback window used to compute the daily run-rate for forecasting
`{{ custom_date_start }}`	Date or `NULL`	Optional override for the chart window start date. If `NULL`, defaults to a grain-appropriate lookback
`{{ custom_date_end }}`	Date or `NULL`	Optional override for the chart window end date. If `NULL`, defaults to `CURRENT_DATE`

Output Columns

Column	Type	Description
`period_start`	Date	Start date of the period
`period`	String	Human-readable period label (e.g. `"2025-01"`, `"Q1 2025"`). Appended with `" (Forecast)"` or `" (Incomplete)"` for the current period depending on `include_current_period`
`current_period_flag`	String	`'Current Period'` if the row represents the current in-progress period; otherwise `NULL`
`answered_calls`	Number	Successful calls participated in
`outbound_calls`	Number	Total outbound call attempts
`answered_outbound`	Number	Outbound calls that were answered
`inbound_calls`	Number	Total inbound call attempts
`answered_inbound`	Number	Inbound calls that were answered
`external_calls`	Number	Calls involving an external (non-Can) number
`answered_external`	Number	External calls that were answered
`voicemails_left`	Number	Voicemails left by devices
`voicemails_received`	Number	Voicemails received by devices
`total_talk_hours`	Number	Total talk time in hours
`activations`	Number	Device activations
`new_contacts_created`	Number	Newly approved contacts
`active_devices`	Number	Distinct devices active in the period (snapshot metric — see notes)
`users_who_both_made_and_received_calls`	Number	Distinct devices that both made and received a successful call in the period (snapshot metric — see notes)
`active_network`	Number	Active subscriptions with a device at period end

CTEs

`base_all_completed_days`

Joins included_dates, daily_calls, and network_size into a single daily-grain dataset. All joins are left outer, so dates with no call or network activity are retained.

`base`

Filters base_all_completed_days based on {{ include_current_period }}. If set to 'No', rows belonging to the current in-progress period are excluded.

`ranked`

Adds period_day_rank — a row number within each period ordered by date descending. Used to snapshot the most recent value of period-end metrics (e.g. active_devices, active_network).

`forecast_window` / `forecast_window_single` / `forecast_constants`

Computes three scalar values from {{ forecast_basis }} and {{ time_grain }}: - lookback_days — number of days in the run-rate window - window_start_date — start of the run-rate window - snapshot_anchor_date — the start-of-window date used for snapshot metric deltas

These are collapsed to a single row in forecast_constants for cross-joining downstream.

`forecast_run_rate_summed`

Computes average daily values for all summable metrics over the forecast window. These averages are multiplied by days_remaining_in_period to project the current period forward.

`snapshot_points` / `forecast_run_rate_snapshots`

Computes the run-rate for active_network by taking the difference between its value at CURRENT_DATE - 1 and at snapshot_anchor_date, divided by lookback_days. Used for forecasting snapshot-style metrics that cannot be summed.

Note: active_devices and users_who_both_made_and_received_calls forecasting is currently disabled (commented out). These metrics are set to 0 for the current period when forecasts are enabled. This is a known limitation to be revisited.

`period_rollup`

Aggregates the daily ranked data to the period level. Summable metrics are SUMmed; snapshot metrics use MAX(CASE WHEN period_day_rank = 1 ...) to capture the most recent value within the period. Also computes: - current_period_flag — marks the current in-progress period - days_remaining_in_period — calendar days from today through end of period, used as the forecast multiplier

`final`

Applies forecast logic. For each metric, if the row is the current period and {{ include_current_period }} = 'Yes (with rest-of-period forecasts)', the actual-to-date value is augmented with avg_daily_rate × days_remaining_in_period. Otherwise the actual value is passed through unchanged. Also applies the " (Forecast)" / " (Incomplete)" suffix to the period label.

`chart_window`

Computes the date range for the final output filter. Uses {{ custom_date_start }} / {{ custom_date_end }} if provided; otherwise falls back to grain-appropriate defaults (e.g. trailing 12 months for Month grain, trailing 8 quarters for Quarter grain).

`final_filtered`

Applies the chart_window filter to final and produces the final output ordered by period_start.

Notes

Snapshot vs. summable metrics: Most metrics (calls, talk time, activations, contacts) are summed across days in a period. active_devices, users_who_both_made_and_received_calls, and active_network are snapshot metrics — they reflect the state at the end of the period rather than a cumulative total, and require separate forecasting logic.
Forecast limitations: Current-period forecasts for active_devices and users_who_both_made_and_received_calls are not yet implemented and will return 0 when forecasting is enabled. active_network forecasting is live using the daily delta method.
Default chart windows by grain: Day → trailing 180 days; Week → trailing 21 weeks; Month → trailing 12 months; Quarter → trailing 8 quarters.
days_remaining_in_period at Day grain is always 0, so forecasting has no effect when time_grain = 'Day'.

All Data Join

Query

Overview

Parameters

Output Columns

CTEs

base_all_completed_days

base

ranked

forecast_window / forecast_window_single / forecast_constants

forecast_run_rate_summed

snapshot_points / forecast_run_rate_snapshots

period_rollup

final

chart_window

final_filtered