How do I approach Software Engineering Fundamentals interview questions?

Software Engineering Fundamentals questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master software engineering fundamentals interviews.

What difficulty level is this interview question?

This is a medium difficulty Software Engineering Fundamentals question, commonly asked during Take-home Project rounds at Intuit.

What role is this question designed for?

This question is commonly asked for Software Engineer candidates at Intuit during technical interviews.

Aggregate transactions by status and failure reasons (SQL)

Q: Aggregate transactions by status and failure reasons (SQL)

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:

Where:

total_transactions = count of transactions in that status.
total_amount = sum of amount for that status.
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).

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:

Where:

total_transactions = count of transactions in that status.
total_amount = sum of amount for that status.
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).

Aggregate transactions by status and failure reasons (SQL)

Quick Overview

Solution

Comments (0)

Aggregate transactions by status and failure reasons (SQL)

Quick Overview

Solution

Comments (0)