Top META Trader by Transaction Volume
Write a SQL query to find the trader(s) who had the most combined buy and sell transactions in META stock over the past 30 days. Include ties and return results ordered by total transaction count descending.
You have access to the following table:
transactions_table
| Column | Type | | --- | --- | | transaction_date | datetime | | stock_symbol | text | | buyer_id | bigint | | seller_id | bigint | | transaction_id | bigint | | purchase_price | float |
Expected output format:
| trader_id | transactions | | --- | --- | | 12345 | 85 |
Constraints: - A trader appears as buyer_id when buying and seller_id when selling - Count each transaction once per role (a transaction has one buyer and one seller) - Return all traders tied for the maximum - Filter to stock_symbol = 'META' and transaction_date within the last 30 days
Example: - Trader 101 bought 50 times and sold 35 times -> 85 total transactions - Trader 202 bought 85 times and sold 0 times -> 85 total transactions - Both appear in output (tied at 85)
Auto-grader note (Solve mode): The in-browser grader runs SQLite. The graded table is transactions_table(day_offset, stock_symbol, buyer_id, seller_id, transaction_id), where day_offset is the integer number of days ago the transaction occurred -- "within the last 30 days" means day_offset < 30 (this replaces the transaction_date datetime so the tests are deterministic). Output rows in any order; the grader sorts before comparing.
Hints
- A trader appears as buyer_id on their purchases and seller_id on their sales -- you need to union both roles before aggregating.
- Use
UNION ALL(notUNION) to combine buy counts and sell counts, thenGROUP BY trader_idandSUMto get totals. - To handle ties, apply
RANK() OVER (ORDER BY SUM(transactions) DESC)-- with noPARTITION BY-- after the aggregation, then filterWHERE rnk = 1.
Worked Solution
How to Think About It: Each row in the table represents one transaction, and a trader can appear in that row as either the buyer or the seller -- not both. To count a trader's total activity, you need to union their appearances as buyer with their appearances as seller, then sum. The tricky part is handling ties: using RANK() with RANK() = 1 naturally handles ties, but you need to be careful about window function placement -- the RANK() must be applied after the aggregation, not alongside it in the same SELECT.
Note: the original solution has a bug -- RANK() OVER(PARTITION BY tt.trader_id ORDER BY SUM(tt.transactions) DESC) partitions by trader_id, which gives every trader rank 1. The correct approach is to rank globally (no PARTITION BY) after summing.
Algorithm: 1. Use UNION ALL to combine buyer and seller appearances for META in the past 30 days, each contributing 1 transaction per row. 2. Group by trader and sum to get total transactions. 3. Rank globally by total transactions descending. 4. Return traders with rank 1.
Code:
```sql WITH trader_activity AS ( -- Count buys SELECT buyer_id AS trader_id, COUNT(transaction_id) AS transactions FROM transactions_table WHERE transaction_date >= CURRENT_DATE - INTERVAL '30' DAY AND stock_symbol = 'META' GROUP BY buyer_id
UNION ALL
-- Count sells SELECT seller_id AS trader_id, COUNT(transaction_id) AS transactions FROM transactions_table WHERE transaction_date >= CURRENT_DATE - INTERVAL '30' DAY AND stock_symbol = 'META' GROUP BY seller_id ), trader_totals AS ( SELECT trader_id, SUM(transactions) AS total_transactions, RANK() OVER (ORDER BY SUM(transactions) DESC) AS rnk FROM trader_activity GROUP BY trader_id ) SELECT trader_id, total_transactions AS transactions FROM trader_totals WHERE rnk = 1 ORDER BY total_transactions DESC; ```
Complexity: $O(n)$ scan of the transactions table (filtered by date and symbol), plus a sort for ranking. In practice, indexes on (stock_symbol, transaction_date) and (buyer_id) / (seller_id) are critical for performance.
Answer: Union buyer and seller counts, aggregate per trader, rank globally by total -- return rank-1 traders. Key fix over the naive approach: RANK() must have no PARTITION BY to rank traders against each other.
Graded (SQLite) version -- passes the in-browser judge, using day_offset < 30 for the 30-day window:
```sql WITH activity AS ( SELECT buyer_id AS trader_id FROM transactions_table WHERE stock_symbol = 'META' AND day_offset < 30 UNION ALL SELECT seller_id FROM transactions_table WHERE stock_symbol = 'META' AND day_offset < 30 ), counts AS ( SELECT trader_id, COUNT(*) AS transactions FROM activity GROUP BY trader_id ) SELECT trader_id, transactions FROM counts WHERE transactions = (SELECT MAX(transactions) FROM counts) ORDER BY transactions DESC, trader_id; ```
Intuition
The key pattern here is the trader-as-buyer / trader-as-seller duality. A single transaction table records one event with two participants. Whenever you need per-entity aggregates where an entity can appear in multiple role columns, UNION ALL is the standard approach -- unpivot the roles into a single column, then aggregate.
The bug in the naive RANK() OVER (PARTITION BY trader_id ...) is instructive: partitioning by the same column you are grouping by gives every group rank 1 trivially, defeating the purpose. A similar mistake shows up with ROW_NUMBER() used incorrectly in deduplication queries. In SQL interviews, window function placement relative to GROUP BY is one of the most common sources of subtle bugs.