Trade Volume
ASKED IN INTERVIEWCoinbase
Problem Statement
Coinbase monitors trading activity to identify peak liquidity periods. The total USD volume for a trade is calculated by multiplying the quantity of the crypto asset traded by its USD price at that specific hour.
Write an SQL query to calculate the total USD trade volume for every hour where trades occurred.
Rules:
- Trades should be aggregated by the hour (e.g., all trades between 10:00:00 and 10:59:59 belong to the 10:00:00 window).
- Total volume should be rounded to 2 decimal places.
- Output columns: trade_hour, total_usd_volume.
- Sort the results in ascending order by trade_hour.
Table Schema:
- Trades: trade_id, ticker, quantity, trade_timestamp.
- HourlyPrices: ticker, price_usd, price_hour (The start of the hour the price applies to).
Tests your understanding of
DateTime, Joins, Aggregation and Time Series
Input Tables
Trades
| trade_id(INTEGER) | ticker(VARCHAR) | quantity(DECIMAL) | trade_timestamp(TIMESTAMP) |
|---|---|---|---|
| 1 | BTC | 0.5 | 2026-02-11 08:05:00 |
| 2 | BTC | 0.1 | 2026-02-11 08:45:00 |
| 3 | ETH | 2 | 2026-02-11 08:30:00 |
| 4 | BTC | 0.2 | 2026-02-11 09:15:00 |
| 5 | ETH | 5 | 2026-02-11 09:59:00 |
| 6 | SOL | 100 | 2026-02-11 09:10:00 |
HourlyPrices
| ticker(VARCHAR) | price_usd(DECIMAL) | price_hour(TIMESTAMP) |
|---|---|---|
| BTC | 50000 | 2026-02-11 08:00:00 |
| ETH | 3000 | 2026-02-11 08:00:00 |
| BTC | 51000 | 2026-02-11 09:00:00 |
| ETH | 3100 | 2026-02-11 09:00:00 |
| SOL | 100 | 2026-02-11 09:00:00 |
Expected Output
| trade_hour(TIMESTAMP) | total_usd_volume(DECIMAL) |
|---|---|
| 2026-02-11 08:00:00 | 36000 |
| 2026-02-11 09:00:00 | 35700 |
Tags
MediumASKED IN INTERVIEWDateTimeJoinsAggregationTime Series
15-20 min
55%