Aggregate transactions by status and failure reasons (SQL)

Quick Overview

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.

Aggregate transactions by status and failure reasons (SQL)

Company: Intuit

Role: Software Engineer

Category: Software Engineering Fundamentals

Difficulty: medium

Interview Round: Online Assessment

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

Overview: 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.

Intuit logo
Intuit
Oct 13, 2025
mediumSoftware EngineerOnline AssessmentSoftware Engineering Fundamentals
12
0

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