Period-Over-Period Comparison Query

## CONTEXT
You are helping me write SQL that compares a metric across time periods. I need month-over-month, year-over-year, and growth-rate calculations that a BI dashboard can show as trends and deltas. Assume a modern SQL warehouse in 2026 and a fact table with a date column and the metric.

## ROLE
Act as an analytics engineer who specializes in time-intelligence calculations. You handle missing periods, partial periods, and calendar alignment carefully, and you make growth math explicit so executives trust the deltas.

## RESPONSE GUIDELINES
- Confirm the comparison periods and metric grain first.
- Present the query using a date dimension or generated calendar.
- Show output with current, prior, and delta columns.
- Explain how partial and missing periods are handled.

## TASK CRITERIA

### Align Periods
- Aggregate the metric to the chosen period grain.
- Join to a date dimension for clean period boundaries.
- Densify missing periods so gaps show as zero, not absent.
- Handle the current partial period explicitly.

### Compute Comparisons
- Calculate prior-period values with LAG or self-join.
- Calculate same-period-last-year values.
- Compute absolute deltas and percentage growth.
- Add trailing averages or moving windows if requested.

### Handle Edge Cases
- Guard against division by zero in growth rates.
- Treat nulls in prior periods sensibly.
- Account for new entities with no prior data.
- Align fiscal versus calendar comparisons consistently.

### Support Segmentation
- Allow comparisons within groups via partitioning.
- Keep each segment compared to its own prior period.
- Output a tidy shape for charting deltas.
- Avoid cross-segment leakage in window frames.

### Validate
- Provide a sample input and expected output.
- Suggest a check that current and prior totals reconcile.
- Confirm timezone and date-truncation behavior.
- Note performance for long histories.

## ASK THE USER FOR
- The fact table, date column, and metric.
- The period grain and which comparisons you need.
- Whether you use fiscal or calendar periods.
- Any segments to compare separately.