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,

      c.quick_drops,
      c.micro_interactions,
      c.real_calls_brief,
      c.quality_calls,
      c.deep_calls,
      c.all_calls,
      c.total_talk_minutes

  FROM included_dates id
  LEFT OUTER JOIN call_classification c
    ON id.dt = c.call_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(quick_drops, 0)) AS avg_daily_quick_drops,
    AVG(COALESCE(micro_interactions, 0)) as avg_daily_micro_interactions,
    AVG(COALESCE(real_calls_brief, 0)) as avg_daily_real_calls_brief,
    AVG(COALESCE(quality_calls, 0)) as avg_daily_quality_calls,
    AVG(COALESCE(deep_calls, 0)) AS avg_daily_deep_calls,
    AVG(COALESCE(all_calls, 0)) AS avg_daily_all_calls,
    AVG(COALESCE(total_talk_minutes, 0)) AS avg_daily_total_talk_minutes

  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_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
    (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(quick_drops, 0)) AS quick_drops_actual,
    SUM(COALESCE(micro_interactions, 0)) AS micro_interactions_actual,
    SUM(COALESCE(real_calls_brief, 0)) AS real_calls_brief_actual,
    SUM(COALESCE(quality_calls, 0)) AS quality_calls_actual,
    SUM(COALESCE(deep_calls, 0)) AS deep_calls_actual,
    SUM(COALESCE(all_calls, 0)) AS all_calls_actual,
    SUM(COALESCE(total_talk_minutes, 0)) AS total_talk_minutes_actual,

    --More logic for snapshot metrics
    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.quick_drops_actual + (s.avg_daily_quick_drops * p.days_remaining_in_period)
  ELSE p.quick_drops_actual
END AS quick_drops,

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

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

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

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

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

--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 p.active_network_actual + (snap.avg_daily_diff_active_network * p.days_remaining_in_period)
      ELSE p.active_network_actual
    END AS active_network,

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


  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 call lengths report. Joins call_classification and network_size 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), call_classification, network_size

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	Description
`period_start`	Start date of the period
`period`	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`	`'Current Period'` if the row represents the current in-progress period; otherwise `NULL`
`quick_drops`	Calls under 20 seconds
`micro_interactions`	Calls between 20 and 44 seconds
`real_calls_brief`	Calls between 45 seconds and 2 minutes
`quality_calls`	Calls between 2 and 5 minutes
`deep_calls`	Calls 5 minutes or longer
`all_calls`	Total completed calls (all tiers combined)
`total_talk_minutes`	Total talk time in minutes
`active_network`	Active subscriptions with a device at period end (snapshot metric)

CTEs

`base_all_completed_days`

Joins included_dates, call_classification, and network_size into a single daily-grain dataset. All joins are left outer, so dates with no call activity or network data 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 active_network at period end.

`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 (call tier counts and total talk minutes) 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.

`period_rollup`

Aggregates the daily ranked data to the period level. Call tier counts and talk minutes are SUMmed; active_network uses MAX(CASE WHEN period_day_rank = 1 ...) to capture the period-end value. Also computes current_period_flag and days_remaining_in_period.

`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. active_network uses the daily delta method. 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.

`final_filtered`

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

Notes

Call tier counts use the canonical duration boundaries defined in call_classifications.sql. If those boundaries change, this query's output will automatically reflect the updated classifications.
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