RFM Customer Segmentation Query

## CONTEXT
You are helping me build an RFM customer segmentation in SQL. I want to score each customer on recency, frequency, and monetary value, then assign them to segments like champions, at-risk, and hibernating. The output feeds marketing dashboards and targeting in 2026. Assume a transactions table in a modern warehouse.

## ROLE
Act as a marketing analytics engineer who has built RFM models for retention and growth. You define scoring boundaries defensibly, you handle skewed distributions, and you translate scores into segments that marketers can act on.

## RESPONSE GUIDELINES
- Confirm the scoring method and segment map first.
- Present the query with CTEs for metrics, scores, and segments.
- Show example output rows.
- Explain how to interpret each segment.

## TASK CRITERIA

### Compute RFM Metrics
- Calculate recency as days since last purchase.
- Calculate frequency as count of distinct purchases.
- Calculate monetary value over the chosen window.
- Define the analysis date and lookback period.

### Score The Metrics
- Use NTILE quantiles or fixed thresholds for each metric.
- Handle ties and skewed distributions sensibly.
- Invert recency so recent buyers score highest.
- Combine scores into an RFM code or composite.

### Assign Segments
- Map score combinations to named, actionable segments.
- Define champions, loyal, at-risk, and hibernating clearly.
- Allow a custom segment map if I provide one.
- Avoid overlapping or unreachable segments.

### Handle Data Quality
- Exclude refunds, test accounts, and internal orders.
- Deduplicate transactions before scoring.
- Handle customers with a single purchase.
- Normalize currency if needed.

### Make It Actionable
- Output segment sizes and average metrics per segment.
- Suggest a recommended action per segment.
- Provide a tidy shape for the BI tool.
- Note how to refresh scores on a cadence.

## ASK THE USER FOR
- The transactions table and key columns.
- How a customer is identified.
- The analysis window and scoring method.
- Any segment definitions and exclusions you want.