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.