Write SQL and Python, run instantly in your browser, and track your progress.
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:
captured, were for a positive amount, occurred in the last 90 days, and were in 'USD'.method and processor to uppercase.amount to an integer number of cents.capture_time to show only the date (YYYY-MM-DD).The final report should be sorted with the most recent captures first.
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:
captured, were for a positive amount, occurred in the last 90 days, and were in 'USD'.method and processor to uppercase.amount to an integer number of cents.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 | order_id | method | processor | amount | currency | status | transaction_id | auth_time | capture_time | refund_time |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | apple_pay | stripe |
| payment_id | order_id | method | processor | amount | currency | status | transaction_id | auth_time | capture_time | refund_time |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | apple_pay | stripe |
| 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 |
Showing first 5 of 9 rows. Recent captured payments with normalized data, sorted by capture date descending.
Showing first 5 of 9 rows. Recent captured payments with normalized data, sorted by capture date descending.
Your final output should be a result set that meets the following requirements:
1. Filtering:
status of 'captured'.amount greater than 0.currency is 'USD' (case-insensitive).capture_time is within the last 90 days.capture_time is NULL.2. Transformations:
Your final output should be a result set that meets the following requirements:
1. Filtering:
status of 'captured'.amount greater than 0.currency is 'USD' (case-insensitive).capture_time is within the last 90 days.capture_time is NULL.2. Transformations:
| status | TEXT |
| status | TEXT |
| transaction_id | TEXT |
| transaction_id | TEXT |
| auth_time | TEXT |
| auth_time | TEXT |
| capture_time | TEXT |
| capture_time | TEXT |
| refund_time | TEXT |
| refund_time | TEXT |
| 55.32 |
| 55.32 |
| USD |
| USD |
| captured |
| captured |
| TX200002 |
| TX200002 |
| 2025-08-20 07:01:34 |
| 2025-08-20 07:01:34 |
| 2025-08-20 15:01:34 |
| 2025-08-20 15:01:34 |
| 2 | 2 | google_pay | square | 662.48 | USD | partial_refund | TX200003 | 2025-02-24 04:56:03 | 2025-02-26 06:56:03 | 2025-03-04 06:56:03 |
| 2 | 2 | google_pay | square | 662.48 | USD | partial_refund | TX200003 | 2025-02-24 04:56:03 | 2025-02-26 06:56:03 | 2025-03-04 06:56:03 |
| 3 | 3 | bank_transfer | internal | 88.66 | USD | captured | TX200004 | 2025-02-13 17:43:43 | 2025-02-14 16:43:43 |
| 3 | 3 | bank_transfer | internal | 88.66 | USD | captured | TX200004 | 2025-02-13 17:43:43 | 2025-02-14 16:43:43 |
| 4 | 4 | card | adyen | 322.87 | USD | captured | TX200005 | 2025-02-07 13:00:50 | 2025-02-09 11:00:50 |
| 4 | 4 | card | adyen | 322.87 | USD | captured | TX200005 | 2025-02-07 13:00:50 | 2025-02-09 11:00:50 |
| 5 | 5 | bank_transfer | stripe | 629.34 | USD | captured | TX200006 | 2025-06-24 03:59:58 | 2025-06-26 01:59:58 |
| 5 | 5 | bank_transfer | stripe | 629.34 | USD | captured | TX200006 | 2025-06-24 03:59:58 | 2025-06-26 01:59:58 |
| SQUARE |
| SQUARE |
| 13101 |
| 13101 |
| USD |
| USD |
| 2025-08-23 |
| 2025-08-23 |
| 1 | APPLE_PAY | STRIPE | 5532 | USD | 2025-08-20 |
| 1 | APPLE_PAY | STRIPE | 5532 | USD | 2025-08-20 |
| 19 | CARD | INTERNAL | 92838 | USD | 2025-07-22 |
| 19 | CARD | INTERNAL | 92838 | USD | 2025-07-22 |
method column must be converted to uppercase and aliased as method_upper.processor column must be converted to uppercase and aliased as processor_upper.currency column must be converted to uppercase and aliased as currency_upper.amount must be converted to an integer number of cents and aliased as amount_cents.capture_time must be formatted as a date ('YYYY-MM-DD') and aliased as capture_date.method column must be converted to uppercase and aliased as method_upper.processor column must be converted to uppercase and aliased as processor_upper.currency column must be converted to uppercase and aliased as currency_upper.amount must be converted to an integer number of cents and aliased as amount_cents.capture_time must be formatted as a date ('YYYY-MM-DD') and aliased as capture_date.3. Columns:
3. Columns:
payment_idmethod_upperprocessor_upperamount_centscurrency_uppercapture_datepayment_idmethod_upperprocessor_upperamount_centscurrency_uppercapture_date4. Ordering:
4. Ordering:
capture_date in descending order (newest first).payment_id in ascending order.capture_date in descending order (newest first).payment_id in ascending order.