Payment SQL: Analyze a Payments Table to Report Transaction Volumes, Failures, and Rolling Averages

Question

## Problem

You have a payments table:

```sql
-- payments(id, user_id, amount, currency, status, created_at)
-- status: 'success' | 'failed' | 'pending'
```

**Query 1:** Daily transaction volume and failure rate for the last 30 days.

```sql
SELECT DATE(created_at) AS day,
       COUNT(*) AS total,
       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_pct
FROM payments
WHERE created_at >= CURRENT_DATE - INTERVAL 30 DAY
GROUP BY DATE(created_at)
ORDER BY day;
```

**Query 2:** 7-day rolling average of daily successful payment amounts.

```sql
SELECT day, daily_total,
       AVG(daily_total) OVER (
           ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS rolling_7d_avg
FROM (
    SELECT DATE(created_at) AS day, SUM(amount) AS daily_total
    FROM payments WHERE status='success'
    GROUP BY DATE(created_at)
) t;
```

## Follow-ups
1. Currency conversion needed -- add an exchange-rate lookup table and normalize to USD.
2. A spike in failure rate may indicate fraud. How would you write an alert query?
3. Rewrite the rolling average in Python pandas.
4. How do you handle timezone differences when grouping by day?

Payment SQL: Analyze a Payments Table to Report Transaction Volumes, Failures, and Rolling Averages

Question Details

Full Details

Topics

More from Square/Block