Intuit OA 面试真题解析：SQL 统计成功与失败交易并汇总失败原因

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.

这道 Intuit OA SQL 题要求按交易状态汇总结果：统计每个状态的交易笔数和金额总和，并且对失败交易进一步按失败原因做聚合。关键点是要先将 failure_reason 为空的失败记录补成 Unknown，再按状态和原因统计次数；随后利用 GROUP_CONCAT 按“出现次数降序、原因字典序升序”拼接失败原因。对 success 状态则直接输出 N/A。最后按 status 升序返回，并把金额格式化到两位小数，确保尾随 0 也保留。