SQL for High-Value Users | Dataford Interview Questions

Paytm wants to identify high-value users based on successful transaction activity. Analyze the rolling 30-day window ending 2025-01-31, inclusive, and consider only transactions from 2025-01-02 through 2025-01-31.

Write a PostgreSQL query that returns users with at least 3 successful transactions and successful transaction volume of at least ₹1,000 during this window.

Requirements

Join users to transactions while preserving users without qualifying transactions.
Exclude failed, pending, or null-status transactions and transactions outside the rolling window.
Return user_id, full_name, transaction count, and total successful volume.
Order by total volume descending, transaction count descending, and user_id ascending.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique Paytm user identifier
full_name	VARCHAR(100)	User's full name

transactions

Column	Type	Description
transaction_idPK	BIGINT	Unique transaction identifier
user_id	INTEGER	User associated with the transaction
occurred_at	TIMESTAMPTZ	Timestamp when the transaction occurred
amount	NUMERIC(12,2)	Transaction amount in INR
status	VARCHAR(20)	Transaction status
transaction_type	VARCHAR(30)	Type of Paytm transaction

Your question is SQL for High-Value Users. 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