Querying Transactions in SQL | Dataford Interview Questions

Your question is Querying Transactions in SQL. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Business Context

Shopify Financial Operations needs a monthly view of Shopify Payments activity by merchant for Q1 2025. The report should distinguish settled sales from settled refunds and exclude failed or pending transactions.

Task

Write a PostgreSQL query that joins payment_transactions to shopify_merchants and returns one row per merchant and transaction month.

Requirements

Include only transactions processed from January 1 through March 31, 2025 with status = 'settled'.
Calculate settled sale count, settled transaction count, gross sales, refund amount, net sales, and refund rate as a percentage of gross sales.
Include merchant name and plan, and sort by transaction month followed by merchant name.
Return a NULL refund rate when gross sales are zero.

Schema

shopify_merchants

Column	Type	Description
merchant_idPK	INT	Unique Shopify merchant identifier
merchant_name	VARCHAR(100)	Merchant display name
plan	VARCHAR(30)	Shopify subscription plan

payment_transactions

Column	Type	Description
transaction_idPK	INT	Unique payment transaction identifier
merchant_id	INT	Merchant associated with the transaction
order_id	VARCHAR(30)	Shopify order reference
transaction_type	VARCHAR(20)	Payment transaction type
status	VARCHAR(20)	Transaction processing status
amount	DECIMAL(12,2)	Transaction amount in the reporting currency
processed_at	DATE	Date the transaction was processed