Aggregate transactions by status and failure reasons (SQL)

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).