Transaction Totals and Ranked Failure Reasons
Updated
Problem statement
Build a report from the transactions table. Return exactly two rows, first SUCCESS and then FAILED, with columns status, total_transactions, total_amount, and failure_reasons.
Count all records of each status and sum their amounts using exact decimal arithmetic. For this exercise, assume a missing status still gets a row with count zero and amount zero.
For failed records, normalize each reason by trimming spaces at both ends, replacing each internal run of spaces with one space, and converting ASCII letters to lowercase. Use unknown for a NULL or blank reason. Count occurrences of each normalized reason. Sort reasons by count descending, then reason text in ascending ASCII order for ties.
Write each reason as reason:count and join the entries with ; . Return every reason. The SUCCESS row always has an empty failure_reasons string, even when its input records contain reasons. A FAILED row without records also uses an empty string.
Table schema
Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.
transactions
| Column | Type | Nullable | Description |
|---|---|---|---|
| status | Text | No | Exactly SUCCESS or FAILED. |
| amount | Decimal | No | Nonnegative exact amount with at most two fractional digits. |
| reason | Text | Yes | Raw reason, blank text, or NULL. |
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| status | Text | No | — |
| total_transactions | Integer | No | — |
| total_amount | Decimal | No | — |
| failure_reasons | Text | No | — |
Row order: must match exactly. Numeric tolerance: 0.
Constraints
- For this exercise, assume the table contains 0 to 100 rows, and status is exactly SUCCESS or FAILED.
- Amount is non-null, between 0 and 1000000 inclusive, with at most two fractional digits.
- Reason may be NULL or a string of at most 60 ASCII letters, digits, spaces, hyphens, or underscores.
- After normalization, a non-unknown reason has 1 to 30 characters. There are at most 10 distinct normalized reasons among failed records.
- Repeated records are allowed and count separately. Status is non-null.
More Intuit Online Assessment questions
- Employee Promotion OpportunitiesOA · Seen Oct 2026
- Next Pascal Row CoefficientOA · Seen Oct 2026
- Parse Router LogsOA · Seen Oct 2026
- Longest Repeated Letter Block with Adjacent SwapsOA · Seen Oct 2026
- Maximum Square-Free DivisorOA · Seen Oct 2026
- Two Tribes from Taller-Person CountsOA · Seen Oct 2026
- Shortest Path from an Encoded GraphOA · Seen Sep 2026
- Oldest and Youngest EmployeesOA · Seen Sep 2026
- Pressure-Isolation Shutdown OrderOA · Seen Sep 2026
- Submask Sum QueriesOA · Seen Sep 2026
Similar questions at other companies
- Bond Holders Above the Cash Flow ThresholdBirla Pivot · OA
- Prepare Taxi Driver Classification DataBoston Consulting Group · OABoston Consulting Group interview questions
- Find the Dominant SellerSquadStack.ai · OA
- Track the Book and Mark Final PnLMillennium · OAMillennium interview questions
- Market Share Ticker with ImbalanceWolverine Trading · OAWolverine Trading interview questions
- Overloaded Game Account InventoriesWells Fargo · OAWells Fargo interview questions
- Employees Joined per MonthOdoo · OAOdoo interview questions
- Bond Haircut by Ratings and MaturityJane Street · OAJane Street interview questions
- Customer Order FeesAdobe · OAAdobe interview questions
- Process Airline Seat RequestsAgoda · OAAgoda interview questions