Real-Time SQL Case Study | Dataford Interview Questions

Write the SQL query in real time and explain how you approached it.

Using the provided tables, return each active customer with at least one qualifying transaction, their successful 2025 transaction total, and their rank within their state. Qualifying transactions are completed transactions dated in calendar year 2025.

Output

One row per qualifying active customer.
Columns: full_name, state, total_successful_amount, and state_rank.
Rank customers by total amount within each state, with ties sharing a rank.
Order by state, state_rank, and full_name.

Schema

users

Column	Type	Description
user_idPK	INT	Unique customer identifier
full_name	VARCHAR(100)	Customer's full name
state	VARCHAR(2)	Two-letter customer state code
is_active	BOOLEAN	Whether the customer is active

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
user_id	INT	Customer who made the transaction
transaction_date	DATE	Date the transaction occurred
amount	NUMERIC(10,2)	Transaction amount
status	VARCHAR(20)	Transaction processing status

Your question is Real-Time SQL Case Study. Start with the requirements and the two tables in the Question tab.

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

Problem