Top Users by Recent Transaction Volume | Dataford Interview Questions

Your question is Top Users by Recent Transaction Volume. Start with the requirements and the three 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

FinEdge wants a report of the most active users by recent completed transaction volume. Write a SQL query to return the top 3 users based on completed transactions in the 30-day window ending on 2024-06-30.

Requirements

Use only transactions with status = 'completed'.
Restrict the date range to 2024-05-31 through 2024-06-30, inclusive.
Count only transactions whose card_id belongs to the same user in the cards table.
For each qualifying user, return:
- user_id
- full_name
- country_code
- total_volume as the sum of ABS(amount)
- transaction_count
Order by total_volume descending, then user_id ascending.
Return only the top 3 rows.

Schema

users

Column	Type	Description
user_idPK	INT	Primary key for the user
full_name	VARCHAR(100)	User full name
country_code	VARCHAR(2)	ISO country code
signup_date	DATE	Date the user signed up

transactions

Column	Type	Description
transaction_idPK	INT	Primary key for the transaction
user_id	INT	User who made the transaction
card_id	INT	Card used for the transaction
amount	DECIMAL(12,2)	Signed transaction amount
transaction_date	DATE	Date of the transaction
status	VARCHAR(20)	Transaction status

cards

Column	Type	Description
card_idPK	INT	Primary key for the card
user_id	INT	Owner of the card
card_type	VARCHAR(20)	Type of card
card_status	VARCHAR(30)	Current card status