Date Spine

Query

--8<-- "queries/user_retention/user_retention_date_spine.sql" 

Overview

Generates a continuous sequence of calendar dates starting from 2025-01-01, grouped and labeled by a configurable time grain. All dates strictly before CURRENT_DATE are included — today is always excluded.

Day grain is intentionally omitted from this date spine. The user retention report generates cohort retention grids, for which daily bucketing is not meaningful.

---

Parameters

Parameter	Accepted Values	Description
{{ time_grain }}	'Week', 'Month', 'Quarter'	Controls the aggregation period for display and grouping columns

---

Output Columns

Column	Description
period_start_display_text	Human-readable label for the period (e.g. "2025-01-06", "Jan 2025", "2025-Q1")
period_start_date	Truncated date value for the start of the period — suitable for sorting and joining
days_in_period	Count of calendar days falling within the period. Note: currently unnamed in the source query — should be aliased as days_in_period.

---

Notes

• Unlike other date spines in this repo, dt (the individual calendar date) is not included as an output column — only the period-level grouping columns are returned.
• period_start_display_text is formatted for display only — use period_start_date for joins and sorting.
• The 6000-row generator covers dates through approximately 2041.