## 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?

InterviewDB Experience · San Francisco

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

Interview Experience

Problem You have a payments table: Query 1: Daily transaction volume and failure rate for the last 30 days. Query 2: 7-day rolling average of daily successful payment amounts. Follow-ups Currency conversion needed -- add an exchange-rate lookup table and normalize to USD. A spike in failure rate may indicate fraud. How would you write an alert query? Rewrite the rolling average in Python pandas. How do you handle timezone differences when grouping by day?

Full Details

🔒

Unlock all Square/Block questions

Full insider details, leaked discussions, and candidate experiences.

or every company, $100/year →

About This Question

This is a candidate experience report from a square/block interview.

It covers the following topics: Data Scientist, Sql .

Topics

Data Scientist Sql

More Square/Block Interview Questions

← All Square/Block questions Square/Block Data Scientist Questions → All Sql Questions →