Fintech: Wash Trading
Coinbase, Binance
Problem Statement
Market integrity is crucial in crypto. Wash trading occurs when traders create artificial volume by buying and selling the same asset between two controlled accounts.
Write a query to detect "Wash Trade Pairs." A wash trade is defined as:
- Trade 1: Account A sells a specific amount of a crypto ticker to Account B.
- Trade 2: Account B sells the exact same amount of the same crypto ticker back to Account A.
- Trade 2 occurs within 60 minutes after Trade 1.
Rules:
- Only consider trades where Trade 2 happens after Trade 1.
- Output columns: account_1, account_2, crypto_ticker, wash_trade_count.
- account_1 should always be the smaller ID of the pair (to ensure unique pairs).
- wash_trade_count is the number of times this specific back-and-forth occurred.
- Result must be sorted by wash_trade_count descending, then account_1 ascending.
Tests your understanding of
Self Joins, Time Series and Conditional Logic
Input Tables
trades
| trade_id(INTEGER) | seller_id(INTEGER) | buyer_id(INTEGER) | crypto_ticker(VARCHAR) | amount(DECIMAL) | trade_timestamp(TIMESTAMP) |
|---|---|---|---|---|---|
| 1 | 101 | 202 | BTC | 0.5 | 2026-03-04 10:00:00 |
| 2 | 202 | 101 | BTC | 0.5 | 2026-03-04 10:15:00 |
| 3 | 101 | 202 | BTC | 0.5 | 2026-03-04 11:30:00 |
| 4 | 202 | 101 | BTC | 0.5 | 2026-03-04 11:45:00 |
| 5 | 303 | 404 | ETH | 10 | 2026-03-04 12:00:00 |
| 6 | 404 | 303 | ETH | 10 | 2026-03-04 12:10:00 |
| 7 | 505 | 606 | SOL | 100 | 2026-03-04 13:00:00 |
| 8 | 606 | 505 | SOL | 99 | 2026-03-04 13:05:00 |
Expected Output
| account_1(INTEGER) | account_2(INTEGER) | crypto_ticker(VARCHAR) | wash_trade_count(INTEGER) |
|---|---|---|---|
| 101 | 202 | BTC | 2 |
| 303 | 404 | ETH | 1 |
Tags
HardSelf JoinsTime SeriesConditional Logic
40-50 min
24%