Transaction Failure Analysis

Original Query

Key Metrics

Analysis

Based on the analysis, transaction failures are significantly higher during peak hours (8-9 PM) due to network timeouts. 4G connections show a 23% higher timeout rate compared to 5G and WiFi.

SQL Query

SELECT 
  HOUR(timestamp) as hour,
  network_type,
  COUNT(*) as total_transactions,
  SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failures,
  ROUND(100.0 * SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) / COUNT(*), 2) as failure_rate
FROM transactions
WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY HOUR(timestamp), network_type
HAVING failure_rate > 10
ORDER BY hour, failure_rate DESC;

Key Findings

• Peak hours (8-9 PM) show 35% higher failure rates
• 4G network has 23% higher timeout rate than 5G/WiFi
• Weekend transactions are 15% higher but maintain similar failure rates
• UPI payments have the lowest failure rate at 2.1%

Recommendations

• Implement retry logic for 4G connections during peak hours
• Consider load balancing to distribute peak hour traffic
• Monitor network quality metrics in real-time
• Optimize timeout thresholds for different network types