Square Merchant Refund ReconciliationCompany: Square | Difficulty: medium

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 a non-NULL amount in the payments table count toward the payment metrics. Include refunds from the refunds table 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 amount values 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.

SchemaDROP 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);← All SQL Challenges · Square Questions · Learn SQL Free