Write a PostgreSQL query that compares monthly performance across active segments in an Equifax-style reporting model. Your result should show every active segment for every reporting month, including months with no applications, and calculate application volume, approvals, approval rate, revenue, and month-over-month revenue change.
| Column | Type | Description |
|---|---|---|
| segment_id | ||
| segment_name | ||
| is_active |
| Column | Type | Description |
|---|---|---|
| application_id | ||
| segment_id | ||
| application_date | ||
| status | ||
| revenue |
| Column | Type | Description |
|---|---|---|
| month_start |
Joining dimension and fact tables at a monthly grainUsing a CTE for reusable aggregated logicApplying conditional aggregation with CASEHandling zero-activity months correctlyComparing time periods within each segment