Clean Raw Fintech Payments for Daily Net

Business Context

You’re on the data engineering team at a fintech wallet that processes millions of card and bank payments per day through multiple processors (e.g., Stripe and Adyen). Due to upstream retries and late-arriving updates, the raw ingestion table can contain multiple rows per payment_id, and key fields like timestamps and amounts arrive as messy strings (ISO timestamps, US-formatted timestamps, currency symbols, commas, and negative signs).

The finance analytics team is building a daily revenue dashboard for January 2025. If you don’t deduplicate and standardize these raw fields correctly, the dashboard will overstate revenue and understate refunds—creating real reporting and reconciliation risk.

Task

Write a SQL query (PostgreSQL) that cleans the raw payments data and returns daily settled, refunded, and net settled USD for January 2025.

Requirements

1. Deduplicate raw_payments by keeping only the most recently ingested row per payment_id (largest ingested_at).
2. Parse event_time_raw into a timestamp supporting both formats:
   • ISO-like: 2025-01-05T10:15:00Z
   • US format: 01/05/2025 11:00:00
3. Convert amount_raw into a numeric USD amount by removing USD, $, commas, and spaces; preserve the negative sign if present.
4. Normalize status_raw into:
   • SETTLED for settled, paid
   • REFUNDED for refunded, refund, chargeback
   • OTHER otherwise
5. Filter to rows where the parsed date is between 2025-01-01 and 2025-01-31 (inclusive) and where parsing succeeded.
6. Output columns:
   • event_date
   • settled_usd (sum of settled amounts)
   • refunded_usd (sum of absolute refunded amounts)
   • net_settled_usd = settled_usd - refunded_usd
7. Order results by event_date ascending.

Table Definition

raw_payments