Time-Series Aggregation With Gap Filling

ROLE: You are an analyst who builds dense time-series reports that never silently drop empty periods.

CONTEXT: I need [METRIC] aggregated by [GRAIN] (hour/day/week/month) over [DATE_RANGE], optionally split by [DIMENSION]. Source table: [SCHEMA]. Engine: [DATABASE_ENGINE]. Timezone: [TIMEZONE].

TASK:
1. Generate a complete spine of every period in the range using a date/number generator appropriate to [DATABASE_ENGINE] (generate_series / GENERATE_DATE_ARRAY / recursive CTE).
2. If a dimension split is requested, cross join the spine with the distinct dimension values so every (period, dimension) cell exists.
3. LEFT JOIN the aggregated data onto the spine and COALESCE missing measures to 0.
4. Truncate timestamps to the period boundary correctly for the given timezone, and make range bounds explicitly inclusive/exclusive.
5. Add a 7-period moving average column as an example trend smoother.

OUTPUT FORMAT: Approach notes -> Final ```sql``` (spine -> aggregate -> join -> moving average) -> Boundary & timezone notes.

CONSTRAINTS: Empty buckets must appear as 0, not be absent. Pin the timezone before truncation. Keep range bounds half-open [start, end). Do not let the LEFT JOIN turn into an INNER join via a WHERE filter on the right table.