Three day average

window functions

Three day average

Coinbase SQL Interview Question

Coinbase smooths the price line on its charts so one volatile hour does not read as a trend reversal. The smoothing is a three day average ending on the day being drawn, and it has to start from the asset's very first day rather than three days late.

Return the symbol, the priced_on date, the close_usd price, and the average close over that day and the two before it as three_day_avg, rounded to 2 decimal places. On an asset's first two days, average whatever days exist. Sort by symbol and then by priced_on.

Asked of

  • Data Analyst
  • Business Analyst
  • BI Analyst
  • Product Analyst
  • Analytics Engineer
  • Data Engineer
  • Data Scientist

coinbase_daily_pricesTable24 rows

Column NameType
symbolVARCHAR
priced_onDATE
close_usdDOUBLE

coinbase_daily_pricesExample Input

symbolpriced_onclose_usd
BTC2024-05-0158000
BTC2024-05-0259200
BTC2024-05-0360500
BTC2024-05-0461100
BTC2024-05-0559800
ETH2024-05-012900
ETH2024-05-022860
ETH2024-05-032945
ETH2024-05-043010
ETH2024-05-053080

Example Output

symbolpriced_onclose_usdthree_day_avg
BTC2024-05-015800058000
BTC2024-05-025920058600
BTC2024-05-036050059233.33
BTC2024-05-046110060266.67
BTC2024-05-055980060466.67
ETH2024-05-0129002900
ETH2024-05-0228602880
ETH2024-05-0329452901.67
ETH2024-05-0430102938.33
ETH2024-05-0530803011.67

Explanation

On BTC's first day there is only one price to average, so the figure is 58000. On the second day it averages two days and gives 58600. From the third day on it averages three, so 58000, 59200 and 60500 come out at 59233.33.

The example above is a small slice of the data. Your query runs against the full tables.