Business Problem

You are a Data Analyst at Amazon. The finance team has requested a detailed report of all successful USD payment captures over the last 90 days for internal accounting and reconciliation. To provide a clean and consistent report, you need to standardize several fields.

Your task is to write a SQL query that transforms the payments table. The query must:

Filter for payments that were captured, were for a positive amount, occurred in the last 90 days, and were in 'USD'.
Standardize the method and processor to uppercase.
Convert the amount to an integer number of cents.
Format the capture_time to show only the date (YYYY-MM-DD).

The final report should be sorted with the most recent captures first.

Business Problem

Your task is to write a SQL query that transforms the payments table. The query must:

Filter for payments that were captured, were for a positive amount, occurred in the last 90 days, and were in 'USD'.
Standardize the method and processor to uppercase.
Convert the amount to an integer number of cents.
Format the capture_time to show only the date (YYYY-MM-DD).

The final report should be sorted with the most recent captures first.

Column Name	Type
payment_id	INTEGER
order_id	INTEGER
method	TEXT
processor	TEXT
amount	REAL
currency	TEXT

Column Name	Type
payment_id	INTEGER
order_id	INTEGER
method	TEXT
processor	TEXT
amount	REAL
currency	TEXT

payment_id	method_upper	processor_upper	amount_cents	currency_upper	capture_date
35	BANK_TRANSFER	PAYPAL	10578	USD	2025-09-02
54	COD	PAYPAL	11876	USD	2025-08-25
15	CARD

payment_id	method_upper	processor_upper	amount_cents	currency_upper	capture_date
35	BANK_TRANSFER	PAYPAL	10578	USD	2025-09-02
54	COD	PAYPAL	11876	USD	2025-08-25
15	CARD

2506. Recent Captured Payments (Normalized)

Relevant companies

Tags

2506. Recent Captured Payments (Normalized)

Relevant companies

Tags

Sign in to Practice