Aggregate transactions by status and failure reasons (SQL)
Company: Intuit
Role: Software Engineer
Category: Software Engineering Fundamentals
Difficulty: medium
Interview Round: Take-home Project
You are given a table `transactions` with (at least) the following columns:
- `transaction_id` (unique identifier)
- `status` (e.g., `SUCCESS`, `FAILED`, `PENDING`, etc.)
- `amount` (numeric)
- `reason` (nullable text; raw failure reason, often messy/inconsistent)
Write a SQL query that outputs one row per `status` with exactly these columns:
| status | total_transactions | total_amount | failure_reasons |
Where:
1. `total_transactions` = count of transactions in that status.
2. `total_amount` = sum of `amount` for that status.
3. `failure_reasons` = a single string that summarizes the most common **normalized** failure reasons for that status (typically only relevant for failure statuses), built by:
- Normalizing the raw `reason` values (e.g., trim whitespace, lowercase, map known synonyms/patterns to a canonical reason).
- Counting occurrences by `(status, normalized_reason)`.
- Ordering reasons by count descending (and breaking ties deterministically).
- Concatenating them into one string (e.g., using `LISTAGG`/`STRING_AGG`) so the final output has only one row per status.
Assume your SQL dialect supports window functions and an aggregation function for string concatenation (e.g., `LISTAGG` or `STRING_AGG`).
Quick Answer: This question evaluates a candidate's proficiency with SQL aggregation, grouping, window functions, string aggregation, and normalization techniques for summarizing transactional records by status and failure reasons.