Intuit OA Interview Question: SQL Combining Successful and Failed Transactions Analysis

SQL: Combining Successful and Failed Transactions Analysis

A financial technology service is conducting a comprehensive analysis of its transactions to identify areas for improvement. The company wants to generate a report that details both the successful and failed transactions, focusing on the volume and total amount involved.

For failed transactions, the report must list all the reasons for failure.

The result should have the following columns: status, total_transactions, total_amount, failure_reasons.

• status: the status of the transaction
• total_transactions: the total number of transactions for a specific status
• total_amount: the total amount of money involved in the transactions for a specific status, rounded to two decimal places, including trailing zeros if necessary
• failure_reasons: the list of reasons for transaction failures, separated by commas, sorted first descending by number of occurrences, then ascending by reason

The result should be sorted in ascending order by status.

Note:

• For transactions with a status of success, set the failure reason to N/A.
• For transactions with a status of failure but without a specified reason (null value), set the failure reason to Unknown.

Language: MySQL

Enter your query below. Please append a semicolon ; at the end of the query.

This Intuit OA SQL problem asks you to summarize transactions by status, including the transaction count, total amount, and a combined list of failure reasons for failed transactions. The main challenge is to treat missing failure reasons as Unknown, aggregate reason frequencies per failure status, and order the concatenated reasons by count descending and reason ascending. Successful transactions should show N/A for the failure_reasons field. The final result must be sorted by status in ascending order, with amounts rounded to two decimal places.