Katkat
Back

Aggregate transactions by status and failure reasons (SQL)

IntuitSoftware Engineer · Online Assessment
MediumSoftware EngineerOnline AssessmentSoftware Engineering Fundamentals

Last updated

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.

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