This report uses window functions to compute, for each of three stocks (AAPL, MSFT, NVDA), a year-to-date (YTD) average and a six-trading-day moving average of the daily closing price since January 2022. The calculation is done in SQL (through SQLite) and independently in dplyr; the two results must match exactly. Both averages keep every row of the data, which is what makes them window functions rather than a GROUP BY summary.
Data. Daily closing prices from Yahoo Finance, saved as stock_prices.csv in this repository so the report is reproducible. The series runs from 2022-01-03 to 2026-09-18, the last completed trading day when the data was pulled (an unfinished trading day was removed on purpose). Prices are split-adjusted (for example NVDA’s June 2024 10-for-1 split) but not dividend-adjusted.
Approach
I will load the stock prices and check for missing values, duplicate dates, and gaps between trading days. Then I will calculate the YTD average and six-trading-day moving average in SQLite and repeat the calculations in R. I will compare both results, check a few calculations by hand, and plot the averages alongside the daily prices to see how they follow price changes.
Step 1: Load and check the data
Window functions depend on row order and row count, so we verify the data before using it.
# Integrity checksstopifnot(nrow(prices) ==3546) # 3 items x 1182 daysstopifnot(all(count(prices, item)$n ==1182))stopifnot(nrow(count(prices, item, date) |>filter(n >1)) ==0) # no duplicate daysstopifnot(!anyNA(prices$close)) # no missing pricesmax_gap <- prices |>arrange(item, date) |>mutate(gap_days =as.numeric(date -lag(date)), .by = item) |>pull(gap_days) |>max(na.rm =TRUE)max_gap # at most 4 calendar days (Friday to Tuesday over a long weekend)
[1] 4
stopifnot(max_gap <=4)
Step 2: Window functions in SQL
Two windows, defined once with a WINDOW clause:
ytd: partition by item and year, ordered by date, from the first row of the year up to the current row. Partitioning by year makes the average restart every January.
w6: partition by item, ordered by date, the current row plus the 5 rows before it. The CASE returns NA until a full 6-row window exists.
ROWS counts physical rows (trading days), which is what we want; RANGE would group by values instead.
library(DBI)library(RSQLite)con <-dbConnect(SQLite(), ":memory:")# SQLite has no date type, so we store dates as ISO textdbWriteTable(con, "prices", prices |>mutate(date =as.character(date)))sql_result <-dbGetQuery(con, " SELECT item, date, close, AVG(close) OVER ytd AS ytd_avg, CASE WHEN COUNT(close) OVER w6 = 6 THEN AVG(close) OVER w6 END AS ma6 FROM prices WINDOW ytd AS (PARTITION BY item, strftime('%Y', date) ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), w6 AS (PARTITION BY item ORDER BY date ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) ORDER BY item, date") |>as_tibble() |>mutate(date =as.Date(date))dbDisconnect(con)head(sql_result, 8)
# A tibble: 8 × 5
item date close ytd_avg ma6
<chr> <date> <dbl> <dbl> <dbl>
1 AAPL 2022-01-03 182. 182. NA
2 AAPL 2022-01-04 180. 181. NA
3 AAPL 2022-01-05 175. 179. NA
4 AAPL 2022-01-06 172 177. NA
5 AAPL 2022-01-07 172. 176. NA
6 AAPL 2022-01-10 172. 175. 175.
7 AAPL 2022-01-11 175. 175. 174.
8 AAPL 2022-01-12 176. 175. 174.
Step 3: The same calculation in dplyr (cross-check)
dplyr has no single window clause, so we build each window with a different tool: cummean() for the YTD average and zoo::rollmeanr() for the moving average. Note the different groupings: the YTD average is grouped by item and year, the moving average by item only, because the 6-day window is allowed to cross New Year.
# A tibble: 8 × 5
item date close ytd_avg ma6
<chr> <date> <dbl> <dbl> <dbl>
1 AAPL 2022-01-03 182. 182. NA
2 AAPL 2022-01-04 180. 181. NA
3 AAPL 2022-01-05 175. 179. NA
4 AAPL 2022-01-06 172 177. NA
5 AAPL 2022-01-07 172. 176. NA
6 AAPL 2022-01-10 172. 175. 175.
7 AAPL 2022-01-11 175. 175. 174.
8 AAPL 2022-01-12 176. 175. 174.
Step 4: Validate
# 1. The two independent methods agree everywhere (including the NA pattern)stopifnot(isTRUE(all.equal(sql_result$ytd_avg, dplyr_result$ytd_avg)))stopifnot(isTRUE(all.equal(sql_result$ma6, dplyr_result$ma6)))# 2. Hand check: the 6th AAPL moving average is the mean of the first 6 closesaapl <- sql_result |>filter(item =="AAPL")stopifnot(near(aapl$ma6[6], mean(aapl$close[1:6])))stopifnot(sum(is.na(aapl$ma6)) ==5) # first 5 rows have no full window# 3. YTD average restarts each January: on the first trading day of every year# it equals that day's closesql_result |>mutate(year =year(date)) |>group_by(item, year) |>slice_head(n =1) |>ungroup() |>summarise(restarts_correctly =all(near(close, ytd_avg)))
# A tibble: 1 × 1
restarts_correctly
<lgl>
1 TRUE
Step 5: Visualize
Dashed line: YTD average (note the reset every January). Solid line: 6-day moving average, which follows the price closely. Grey: the daily close.
The six-day moving average follows recent price changes more closely, while the YTD average moves more slowly as more trading days are included. It resets in January, so earlier prices no longer contribute to the new year.
My cross-check gave the same results from both tools. This gives me more confidence in my calculations than relying on one tool alone.
I used only three stocks and a six-day window. The prices account for stock splits but exclude dividend adjustments, so they do not represent total returns.
I would compare different window sizes, try an exponential moving average, and examine signals when a shorter moving average crosses a longer one.
AI Use
Anthropic. (2026). Claude Sonnet 5 [Large language model]. https://claude.ai. Accessed September 24, 2026.
I used Claude to proofread my report, make my explanations clearer, and help me find errors when my R code did not run. I reviewed the suggested corrections and completed my final code, analysis, checks, and interpretation myself.
