Calculate the daily trading volume and the percentage change from the previous day. Return trade_date, volume, and pct_change. Use window functions to compare with the previous day.
This time-series analysis problem is central to Coinbase's cryptocurrency exchange analytics. Understanding volume trends helps traders identify market momentum and liquidity patterns. This question tests your mastery of window functions, specifically LAG() for accessing previous rows, and percentage change calculations—critical skills for financial data analysis.
Advanced concepts: LAG() window function to access previous row values, ORDER BY in window function to define row sequence, percentage change formula ((current - previous) / previous * 100), and NULL handling for the first row (no previous day). Understanding window function syntax and when to use them vs self-joins is essential.
Coinbase's analytics teams use similar queries to: generate daily volume reports for market makers, detect unusual trading activity for fraud prevention, calculate volatility metrics for risk management, power real-time trading dashboards, identify optimal trading times based on liquidity, and feed machine learning models for price prediction.
When tackling this Coinbase problem, the key is to understand the grain of the result. Are you returning one row per user, or one row per category? Always start by identifying your unique join keys and consider if filtered aggregations (CASE WHEN) are more efficient than multiple subqueries.
Be careful with NULL values in your JOIN conditions or aggregate functions. In interview scenarios, datasets often include edge cases like zero-count categories or duplicate entries that can throw off a simple COUNT(*) if not handled with DISTINCT.
Share your approach, optimized queries, or ask questions. Learning from others is the fastest way to master SQL.
SELECT
trade_date,
volume,
ROUND(
(volume - LAG(volume) OVER (ORDER BY trade_date)) * 100.0
/ LAG(volume) OVER (ORDER BY trade_date),
2
) AS pct_change
FROM (
SELECT
trade_date,
SUM(amount) AS volume
FROM trades
GROUP BY trade_date
) daily_volumes
ORDER BY trade_dateThis approach aggregates daily volume in an inline subquery, then applies LAG() over the ordered date sequence in the outer query. LAG() retrieves the previous day's volume without a self-join, making the percentage change calculation clean and readable. The 100.0 literal forces floating-point division, avoiding integer truncation. Two LAG() calls are made for the subtraction and division — some optimizers compute this once, but a CTE can make it explicit. Best when the dataset fits in memory and query simplicity is valued.
WITH daily_volumes AS (
SELECT
trade_date,
SUM(amount) AS volume
FROM trades
GROUP BY trade_date
),
with_lag AS (
SELECT
trade_date,
volume,
LAG(volume) OVER (ORDER BY trade_date) AS prev_volume
FROM daily_volumes
)
SELECT
trade_date,
volume,
ROUND((volume - prev_volume) * 100.0 / prev_volume, 2) AS pct_change
FROM with_lag
ORDER BY trade_dateBreaking the query into CTEs computes LAG() once, storing prev_volume as a named column. This avoids calling LAG() twice and makes the percentage formula readable without nested function calls. It also surfaces prev_volume for debugging — you can SELECT * FROM with_lag to inspect the intermediate state. Prefer this in production pipelines where maintainability matters. The CTE chain adds negligible overhead in modern optimizers and is significantly easier to modify if the business logic changes (e.g., switching to 7-day lag).
The bottleneck is the GROUP BY aggregation over the trades table, which requires a full scan and sort or hash aggregation. An index on (trade_date, amount) as a covering index lets the engine scan only the index, avoiding heap access entirely. For Coinbase's scale (millions of trades per day), pre-computing daily_volumes into a materialized view or summary table updated incrementally is standard practice. LAG() is a window function that requires sorting the result set by trade_date — ensure trade_date has an index to avoid an in-memory sort. The ROUND() call is negligible. NULL handling for the first row (where LAG returns NULL) means pct_change will be NULL for the earliest date, which is correct but should be documented. Date gaps (missing trading days) will cause LAG to pull from the last available day, which may misrepresent growth; use LEAD/LAG with explicit date arithmetic for gap-aware analysis.
The most dangerous pitfall is integer division: (volume - prev_volume) / prev_volume truncates to 0 in SQL dialects that default to integer arithmetic. Always use 100.0 or CAST(... AS FLOAT). Candidates often forget to handle the first row where LAG() returns NULL, causing a division-by-zero or unexpected NULL in pct_change. Another mistake is ordering by volume instead of trade_date in the OVER clause, producing nonsensical lag values. Missing the GROUP BY aggregation step — applying LAG directly on the raw trades table — is a logic error that inflates the window partition. Always confirm whether the problem expects calendar-day gaps to be included or only trading days.
LAG() will look at the previous row in the result set, which may be two or more days prior, not necessarily yesterday. The pct_change would silently span multiple days, making the metric misleading. To handle gaps, generate a complete date spine using a calendar table or recursive CTE, LEFT JOIN trades onto it, and use COALESCE(volume, 0). This ensures every calendar day is represented with correct lag.
Replace the GROUP BY subquery with AVG(amount) OVER (ORDER BY trade_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as a window function on the raw trades table, or aggregate daily first then apply the rolling window. Use ROWS BETWEEN 6 PRECEDING AND CURRENT ROW in the window frame. This computes a trailing 7-day average per day, smoothing out daily volatility — a common metric in financial dashboards.
A self-join on date - 1 day requires an explicit date arithmetic condition and generates a larger intermediate result set. LAG() is a single-pass window operation that is both more readable and typically faster because it avoids the join overhead and duplicate row generation. Self-joins also fail silently on date gaps — LAG is more predictable. LAG() is the standard modern SQL approach for accessing adjacent rows in an ordered sequence.