AMD's warranty fraud analytics team is investigating a suspicious pattern: customers who file an RMA (Return Merchandise Authorization) claim for a defective chip and then quickly place a **new order for the exact same product**. This can indicate the customer is reselling the "defective" unit while pocketing a free replacement. Write a query to flag every RMA claim where the same customer ordered the same product again within **30 days after** the claim date (the reorder must happen strictly after the claim, not on the same day). If a customer placed multiple qualifying reorders after a single claim, only report the **earliest** one. Output the customer_id, product, claim_date, next_order_date, and days_between (next_order_date minus claim_date), sorted by customer_id then claim_date. ### Table Schema | Table | Column | Type | |---|---|---| | `rma_claims` | `claim_id` | integer | | `rma_claims` | `customer_id` | integer | | `rma_claims` | `product` | string | | `rma_claims` | `claim_date` | date | | `orders` | `order_id` | integer | | `orders` | `customer_id` | integer | | `orders` | `product` | string | | `orders` | `order_date` | date | ### Example Input: `rma_claims` | claim_id | customer_id | product | claim_date | |---|---|---|---| | 1 | 501 | Ryzen 9 7950X | 2023-01-10 | | 2 | 502 | Radeon RX 7900 XTX | 2023-02-01 | | 3 | 501 | Ryzen 9 7950X | 2023-05-01 | ### Example Input: `orders` | order_id | customer_id | product | order_date | |---|---|---|---| | 101 | 501 | Ryzen 9 7950X | 2023-01-25 | | 102 | 502 | Radeon RX 7900 XTX | 2023-03-15 | | 103 | 501 | Ryzen 9 7950X | 2023-01-05 | ### Example Output Explanation For claim 1 (customer 501, Ryzen 9 7950X claimed on 2023-01-10), the customer reordered the same chip on 2023-01-25 — 15 days later, inside the 30-day window, so it is flagged. Order 103 (2023-01-05) happened *before* the claim, so it is ignored. For claim 2 (customer 502), the next order was on 2023-03-15, 42 days after the claim, which falls outside the 30-day window, so it is not flagged. Claim 3 has no matching order at all in the data, so it is excluded from the results entirely.
DROP TABLE IF EXISTS rma_claims; DROP TABLE IF EXISTS orders; CREATE TABLE rma_claims (claim_id INT, customer_id INT, product VARCHAR(255), claim_date DATE); CREATE TABLE orders (order_id INT, customer_id INT, product VARCHAR(255), order_date DATE); INSERT INTO rma_claims VALUES (1, 501, 'Ryzen 9 7950X', '2023-01-10'), (2, 502, 'Radeon RX 7900 XTX', '2023-02-01'), (3, 501, 'Ryzen 9 7950X', '2023-05-01'); INSERT INTO orders VALUES (101, 501, 'Ryzen 9 7950X', '2023-01-25'), (102, 502, 'Radeon RX 7900 XTX', '2023-03-15'), (103, 501, 'Ryzen 9 7950X', '2023-01-05');