Introduction

This analysis generates a simulated daily price dataset for three instruments Apple (AAPL), Microsoft (MSFT), and Bitcoin (BTC) from January 1, 2022 through December 31, 2023, and uses SQL window functions to calculate a year to date running average and a 6 day moving average for each instrument.

Body

set.seed(607)

dates <- seq(as.Date("2022-01-01"), as.Date("2023-12-31"), by = "day")
n <- length(dates)

simulate_price <- function(start_price, drift, volatility, n) {
  changes <- rnorm(n, mean = drift, sd = volatility)
  pmax(1, round(start_price + cumsum(changes), 2))  
}

prices <- bind_rows(
  tibble(ticker = "AAPL", date = dates, price = simulate_price(175,    0.05, 2,   n)),
  tibble(ticker = "MSFT", date = dates, price = simulate_price(310,    0.08, 3,   n)),
  tibble(ticker = "BTC",  date = dates, price = simulate_price(46000,  5,    800, n))
)

glimpse(prices)
## Rows: 2,190
## Columns: 3
## $ ticker <chr> "AAPL", "AAPL", "AAPL", "AAPL", "AAPL", "AAPL", "AAPL", "AAPL",…
## $ date   <date> 2022-01-01, 2022-01-02, 2022-01-03, 2022-01-04, 2022-01-05, 20…
## $ price  <dbl> 173.05, 175.06, 175.61, 177.85, 176.42, 173.09, 170.69, 172.98,…
con <- dbConnect(RSQLite::SQLite(), "prices.db")


dbWriteTable(con, "prices", prices, overwrite = TRUE)

dbGetQuery(con, "SELECT COUNT(*) AS row_count FROM prices;")
##   row_count
## 1      2190
window_results <- dbGetQuery(con, "
  SELECT
    ticker,
    date,
    price,
    ROUND(AVG(price) OVER (
      PARTITION BY ticker, strftime('%Y', date)
      ORDER BY date
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ), 2) AS ytd_avg,
    ROUND(AVG(price) OVER (
      PARTITION BY ticker
      ORDER BY date
      ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
    ), 2) AS moving_avg_6day
  FROM prices
  ORDER BY ticker, date;
")

glimpse(window_results)
## Rows: 2,190
## Columns: 5
## $ ticker          <chr> "AAPL", "AAPL", "AAPL", "AAPL", "AAPL", "AAPL", "AAPL"…
## $ date            <dbl> 18993, 18994, 18995, 18996, 18997, 18998, 18999, 19000…
## $ price           <dbl> 173.05, 175.06, 175.61, 177.85, 176.42, 173.09, 170.69…
## $ ytd_avg         <dbl> 173.05, 174.06, 174.57, 175.39, 175.60, 175.18, 174.54…
## $ moving_avg_6day <dbl> 173.05, 174.06, 174.57, 175.39, 175.60, 175.18, 174.79…
head(window_results, 10)
##    ticker  date  price ytd_avg moving_avg_6day
## 1    AAPL 18993 173.05  173.05          173.05
## 2    AAPL 18994 175.06  174.06          174.06
## 3    AAPL 18995 175.61  174.57          174.57
## 4    AAPL 18996 177.85  175.39          175.39
## 5    AAPL 18997 176.42  175.60          175.60
## 6    AAPL 18998 173.09  175.18          175.18
## 7    AAPL 18999 170.69  174.54          174.79
## 8    AAPL 19000 172.98  174.34          174.44
## 9    AAPL 19001 175.09  174.43          174.35
## 10   AAPL 19002 173.76  174.36          173.67
tail(window_results, 10)
##      ticker  date  price ytd_avg moving_avg_6day
## 2181   MSFT 19713 498.67  440.44          499.46
## 2182   MSFT 19714 497.86  440.62          498.79
## 2183   MSFT 19715 494.51  440.79          498.29
## 2184   MSFT 19716 498.64  440.97          498.15
## 2185   MSFT 19717 497.36  441.14          497.55
## 2186   MSFT 19718 491.82  441.30          496.48
## 2187   MSFT 19719 488.95  441.44          494.86
## 2188   MSFT 19720 491.40  441.60          493.78
## 2189   MSFT 19721 495.31  441.76          493.91
## 2190   MSFT 19722 497.45  441.93          493.71
window_results$date <- as.Date(window_results$date)

ggplot(window_results, aes(x = date)) +
  geom_line(aes(y = price, color = "Daily Price"), alpha = 0.3) +
  geom_line(aes(y = moving_avg_6day, color = "6-Day Moving Avg"), linewidth = 0.6) +
  geom_line(aes(y = ytd_avg, color = "YTD Avg"), linewidth = 0.6) +
  facet_wrap(~ ticker, scales = "free_y") +
  scale_x_date(date_breaks = "6 months", date_labels = "%b %Y") +
  labs(title = "Price, 6-Day Moving Average, and YTD Average by Instrument",
       x = "Date", y = "Price", color = "") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

dbDisconnect(con)

Conclusions

The 6 day moving average tracks daily price swings closely, while the year to date average moves much more slowly and smoothly, since it factors in every day since January 1st. This is clearest for MSFT, which trended upward strongly, the 6 day average kept pace with that climb, while the YTD average lagged behind. BTC was the most volatile of the three instruments, swinging between roughly $40,000 and $55,000, while AAPL stayed comparatively calmer between $130 and $185. The YTD average also visibly resets at the start of each new year, confirming the window function is working as intended.