SQL Joins, CTEs, and Pandas Strings

HardSQL · PostgreSQL

Problem

How would you answer SQL questions on joins and CTEs, and Python questions using pandas for string manipulation?

For the SQL portion, write a query that analyzes merchants, customers, payments, and payment events for Q1 2025. The result should combine payment totals with repeat-customer and month-over-month metrics.

Output

One row per merchant, including merchants without qualifying payments.
Columns: merchant_name, total_success_amount, active_customers, repeat_customers, latest_success_date, and avg_monthly_change.
Include only successful payments from Q1 2025 for payment metrics. Order by total_success_amount descending, then merchant_name ascending.

Schema

merchants

Column	Type	Description
merchant_idPK	INT	Unique merchant identifier
merchant_name	VARCHAR(100)	Merchant display name
region	VARCHAR(50)	Merchant operating region

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer name
email	VARCHAR(150)	Customer email address

payments

Column	Type	Description
payment_idPK	INT	Unique payment identifier
merchant_id	INT	Merchant receiving the payment
customer_id	INT	Customer associated with the payment
amount	NUMERIC(12,2)	Payment amount
payment_status	VARCHAR(20)	Payment lifecycle status
created_at	DATE	Payment creation date

payment_events

Column	Type	Description
event_idPK	INT	Unique payment event identifier
payment_id	INT	Payment associated with the event
event_type	VARCHAR(30)	Payment event type
event_at	TIMESTAMP	Event timestamp

Tablesmerchantscustomerspaymentspayment_events