Square's merchant analytics team wants to measure January 2025 payment performance for each seller. For every merchant with at least three valid captured payments during January 2025, calculate the captured payment count, gross sales, refunded amount, net sales, and refund rate. Only captured payments with non-NULL amounts count toward the payment metrics. Include refunds issued during January 2025 only when they belong to an eligible captured payment. A payment may have multiple refunds, and each refund should be included once. Treat missing refund amounts as zero. The refund rate is calculated as refunded amount divided by gross sales and expressed as a percentage rounded to two decimal places. If gross sales are zero, return NULL for the refund rate. Return the columns in this order: merchant ID, merchant name, captured payment count, gross sales, refunded amount, net sales, and refund rate. Round monetary values to two decimal places. Sort by net sales descending, then merchant ID descending. ### Table Schema | Table | Column | Type | |---|---|---| | merchants | merchant_id | integer | | merchants | merchant_name | varchar(100) | | payments | payment_id | integer | | payments | merchant_id | integer | | payments | paid_at | timestamp | | payments | status | varchar(20) | | payments | amount | decimal(10,2) | | refunds | refund_id | integer | | refunds | payment_id | integer | | refunds | refunded_at | timestamp | | refunds | amount | decimal(10,2) | ### Example Input: merchants | merchant_id | merchant_name | |---:|---| | 1 | Ava's Coffee | | 2 | Ben's Books | | 3 | Cara's Crafts | ### Example Input: payments | payment_id | merchant_id | paid_at | status | amount | |---:|---:|---|---|---:| | 1 | 1 | 2025-01-01 09:00:00 | captured | 100.00 | | 2 | 1 | 2025-01-05 10:00:00 | captured | 50.00 | | 3 | 1 | 2025-01-10 11:00:00 | captured | 25.00 | | 4 | 2 | 2025-01-02 09:00:00 | captured | 200.00 | | 5 | 2 | 2025-01-06 10:00:00 | captured | 100.00 | | 6 | 2 | 2025-01-12 11:00:00 | captured | 50.00 | ### Example Input: refunds | refund_id | payment_id | refunded_at | amount | |---:|---:|---|---:| | 1 | 1 | 2025-01-15 09:00:00 | 20.00 | | 2 | 2 | 2025-01-20 10:00:00 | 10.00 | | 3 | 4 | 2025-01-18 11:00:00 | 50.00 | ### Example Output Explanation Ava's Coffee has three captured payments totaling 175.00 and 30.00 in refunds, producing 145.00 in net sales and a 17.14% refund rate. Ben's Books has three captured payments totaling 350.00 and 50.00 in refunds, producing 300.00 in net sales and a 14.29% refund rate. Cara's Crafts is excluded because it does not have at least three qualifying captured payments.
DROP TABLE IF EXISTS refunds; DROP TABLE IF EXISTS payments; DROP TABLE IF EXISTS merchants; CREATE TABLE merchants ( merchant_id INT, merchant_name VARCHAR(100) ); CREATE TABLE payments ( payment_id INT, merchant_id INT, paid_at TIMESTAMP, status VARCHAR(20), amount DECIMAL(10,2) ); CREATE TABLE refunds ( refund_id INT, payment_id INT, refunded_at TIMESTAMP, amount DECIMAL(10,2) ); INSERT INTO merchants VALUES (1, 'Ava''s Coffee'), (2, 'Ben''s Books'), (3, 'Cara''s Crafts'); INSERT INTO payments VALUES (1, 1, '2025-01-01 09:00:00', 'captured', 100.00), (2, 1, '2025-01-05 10:00:00', 'captured', 50.00), (3, 1, '2025-01-10 11:00:00', 'captured', 25.00), (4, 2, '2025-01-02 09:00:00', 'captured', 200.00), (5, 2, '2025-01-06 10:00:00', 'captured', 100.00), (6, 2, '2025-01-12 11:00:00', 'captured', 50.00), (7, 3, '2025-01-03 12:00:00', 'captured', 75.00), (8, 3, '2025-01-08 13:00:00', 'captured', 25.00), (9, 1, '2025-01-15 14:00:00', 'failed', 500.00), (10, 2, '2024-12-31 15:00:00', 'captured', 300.00); INSERT INTO refunds VALUES (1, 1, '2025-01-15 09:00:00', 20.00), (2, 2, '2025-01-20 10:00:00', 10.00), (3, 4, '2025-01-18 11:00:00', 50.00);