Problem
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.
Schema
| 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 |
What This Tests
- Joining dimension and fact tables at a monthly grain
- Using a CTE for reusable aggregated logic
- Applying conditional aggregation with CASE
- Handling zero-activity months correctly
- Comparing time periods within each segment
Practicing as: Data Visualisation Specialist interview at EquifaxHi, I'll play your Equifax interviewer for the Data Visualisation Specialist role. Candidates describe these interviews as mostly positive and moderately difficult, so expect me to be friendly and conversational. Take your time with the question above and answer like we're in the room.
You are practicing as a guest. Sign up free to run your code against the sample data. Your draft stays right here.
