SQL for Top Trading Users | Dataford Interview Questions

Your question is SQL for Top Trading Users. 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

OKX Business Analytics needs to identify the highest-volume users on the OKX trading platform. Write a PostgreSQL query that ranks all users by their total trading volume during the 30-day period ending today.

Requirements

Include every user, including users with no qualifying trades, with zero volume for users without activity.
Include only trades whose executed_at falls between CURRENT_DATE - INTERVAL '29 days' and the end of CURRENT_DATE.
Use a window function to identify the top 10% of users. For this exercise, divide users into ten volume-ordered deciles with NTILE(10) and return users in decile 1.
Return the user ID, username, 30-day trading volume, and decile, ordered by volume descending and user ID ascending.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique OKX user identifier
username	VARCHAR(50)	User display name
country_code	VARCHAR(2)	User registration country code

trades

Column	Type	Description
trade_idPK	BIGINT	Unique trade identifier
user_id	INTEGER	User who executed the trade
executed_at	DATE	Trade execution date
usd_volume	NUMERIC(18,2)	Trade volume converted to USD