Question: Merchant Performance
"Calculate the success rate of each merchant's transactions (number of successful transactions divided by total attempted transactions)."

Step-by-Step Explanation:

You have a table called transactions with:

• merchant_id
• transaction_id
• status (values could be 'success' or 'failed')

You need to:

1. For each merchant, calculate the success rate.
   • Success rate = (Number of successful transactions) ÷ (Total number of transactions)

Sample Data (transactions):

Step 1: Count Total Transactions per Merchant

Group by merchant_id and use COUNT(*) to find total transactions.

Step 2: Count Successful Transactions per Merchant

Use SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) to count only the successful ones.

Step 3: Calculate the Success Rate

Success rate = (Number of successful transactions) ÷ (Total number of transactions)

Multiply by 100 if you want to show it as a percentage.

SQL Query:

SELECT
  merchant_id,
  COUNT(*) AS total_transactions,
  SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS successful_transactions,
  ROUND(
    SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) * 1.0 / COUNT(*), 
    2
  ) AS success_rate
FROM
  transactions
GROUP BY
  merchant_id;

Explanation of SQL:

• COUNT(*) counts all transactions (both success and failed).
• SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) counts only successful ones.
• SUM(...) / COUNT(*) calculates success rate as a decimal.
• ROUND(..., 2) rounds the success rate to 2 decimal places for cleaner output.

Expected Output:

Explanation:

• Merchant 101: 2 out of 3 transactions were successful → 2 ÷ 3 = 0.67
• Merchant 102: 2 out of 2 were successful → 2 ÷ 2 = 1.00
• Merchant 103: 0 successes out of 1 → 0 ÷ 1 = 0.00

Important Points:

• Use CASE inside SUM() to selectively count only successful transactions.
• If a merchant has zero successes, success rate will be 0.
• If you need success rate in percentage (like 67%), multiply by 100.

Example:

ROUND(
    SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 
    2
) AS success_rate_percentage