Your question is Monthly Trader Insight Summary. 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.
You are given trading activity data from a platform such as Wolverine Trading's internal analytics environment. Write a PostgreSQL query that returns one row per trader for February 2024, showing each trader's name, team, number of executed orders, total filled quantity, gross trading revenue, and an activity label of High Activity, Moderate Activity, or Low Activity based on executed order count. Include traders even if they had no executed orders in February.
Use only February 2024 orders, count only rows where status = 'EXECUTED', and classify traders as High Activity for 3+ executed orders, Moderate Activity for 1-2, and Low Activity for 0.
| Column | Type | Description |
|---|---|---|
| trader_idPK | INT | Primary key for each trader |
| trader_name | VARCHAR(100) | Trader full name |
| team_id | INT | Reference to the trader's team |
| start_date | DATE | Date the trader joined |
| Column | Type | Description |
|---|---|---|
| team_idPK | INT | Primary key for each team |
| team_name | VARCHAR(100) | Team name |
| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Primary key for each order |
| trader_id | INT | Reference to the trader who placed the order |
| symbol | VARCHAR(20) | Traded symbol |
| order_date | DATE | Order date |
| status | VARCHAR(20) | Order status such as EXECUTED or CANCELLED |
| filled_qty | INT | Filled quantity for the order |
| gross_revenue | DECIMAL(12,2) | Gross revenue generated by the order |