Your question is SQL for Journey Friction. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
JPMorganChase wants to monitor customer journeys across its mobile app, website, branches, and contact center. Write a PostgreSQL query that aggregates January 2025 interactions for active customers and identifies journey stages with meaningful friction.
An interaction is considered a failure when its status is anything other than success. A friction point must have at least two interactions and a failure rate of at least 40%.
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer display name |
| segment | VARCHAR(30) | Customer segment |
| is_active | BOOLEAN | Whether the customer is currently active |
| Column | Type | Description |
|---|---|---|
| interaction_idPK | INT | Unique interaction identifier |
| customer_id | INT | Customer associated with the interaction |
| channel | VARCHAR(30) | Interaction channel |
| stage_code | VARCHAR(30) | Journey stage code |
| interaction_ts | TIMESTAMP | Interaction timestamp |
| status | VARCHAR(20) | Interaction outcome |
| duration_seconds | INT | Interaction duration in seconds |
| Column | Type | Description |
|---|---|---|
| stage_codePK | VARCHAR(30) | Unique journey stage code |
| stage_name | VARCHAR(80) | Readable journey stage name |
| stage_order | INT | Business order of the journey stage |