SQL Window Functions: Stock Price Averages

1 Overview

This assignment uses SQL window functions to calculate two averages of daily stock prices:

• Year-to-date average: the average closing price from the start of the year up to each day.
• Six-day moving average: the average of each day’s closing price and the five trading days before it.

Both are calculated separately for each stock. A window function is what makes this possible: it calculates a value for every row from a “window” of related rows, without collapsing the rows the way GROUP BY does.

2 The Data

The data is the daily closing price of three stocks, Apple (AAPL), Microsoft (MSFT) and Amazon (AMZN), from January 3, 2022 (the first trading day of 2022) to September 18, 2026. It was downloaded from Yahoo Finance and saved as data/stock_prices.csv in my GitHub repository, so the results do not change when new prices come in. The code below reads the file directly from GitHub. Prices are adjusted for stock splits, such as Amazon’s 20-for-1 split in 2022.

prices <- read_csv(
  "https://raw.githubusercontent.com/AnissSahraoui/DATA607/main/Week3B/data/stock_prices.csv",
  show_col_types = FALSE
)

prices |>
  group_by(ticker) |>
  summarise(trading_days = n(), first_day = min(date), last_day = max(date),
            lowest_close = min(close), highest_close = max(close))

ticker	trading_days	first_day	last_day	lowest_close	highest_close
AAPL	1182	2022-01-03	2026-09-18	125.02	340.08
AMZN	1182	2022-01-03	2026-09-18	81.82	284.02
MSFT	1182	2022-01-03	2026-09-18	214.25	542.07

3 Loading the Data into SQL

The prices go into a SQLite table with one row per stock per day. The primary key (ticker, date) makes sure no day is loaded twice for the same stock.

-- 01_create_table.sql
-- One row per stock per trading day.

DROP TABLE IF EXISTS stock_prices;

CREATE TABLE stock_prices (
  date    TEXT NOT NULL,   -- YYYY-MM-DD
  ticker  TEXT NOT NULL,
  close   REAL NOT NULL,   -- closing price in US dollars
  PRIMARY KEY (ticker, date)
);

# Run a .sql file one statement at a time
run_sql_file <- function(con, path) {
  statements <- readLines(path) |>
    str_remove("--.*$") |>
    paste(collapse = "\n") |>
    str_split_1(";") |>
    str_trim()
  walk(statements[statements != ""], \(s) dbExecute(con, s))
}

con <- dbConnect(SQLite(), ":memory:")
run_sql_file(con, "sql/01_create_table.sql")

dbAppendTable(con, "stock_prices", prices |> mutate(date = as.character(date)))

[1] 3546

dbGetQuery(con, "SELECT ticker, COUNT(*) AS rows FROM stock_prices GROUP BY ticker")

ticker	rows
AAPL	1182
AMZN	1182
MSFT	1182

4 The Window Functions

This is the full query, saved as sql/02_window_functions.sql:

-- 02_window_functions.sql
-- Year-to-date average and six-day moving average of the closing price, for each stock.

SELECT
  date,
  ticker,
  close,

  -- Year-to-date average: every day from January 1 of the same year up to this day.
  -- PARTITION BY ticker and year makes the average restart each January for each stock.
  AVG(close) OVER (
    PARTITION BY ticker, strftime('%Y', date)
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS ytd_avg,

  -- Six-day moving average: this trading day and the five before it.
  -- The first five days of each stock have fewer than six days of history, so they are NULL.
  CASE
    WHEN COUNT(close) OVER six_days = 6 THEN AVG(close) OVER six_days
  END AS moving_avg_6d

FROM stock_prices
WINDOW six_days AS (
  PARTITION BY ticker
  ORDER BY date
  ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
)
ORDER BY ticker, date;

How each part works:

Part	What it does
PARTITION BY ticker	Calculates each stock separately
strftime('%Y', date)	Also splits by year, so the year-to-date average restarts every January
ORDER BY date	Puts each stock’s days in order
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	Year-to-date: every day so far this year
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW	Six-day: today and the five trading days before it

Two details:

• The moving average counts trading days, not calendar days. Weekends and holidays have no price, so six trading days usually span eight calendar days.
• The first five days of each stock do not have six days of history yet. COUNT(close) OVER six_days counts the days in the window, and the moving average is left empty until there are six.

query <- paste(readLines("sql/02_window_functions.sql"), collapse = "\n")

results <- dbGetQuery(con, query) |>
  as_tibble() |>
  mutate(date = as.Date(date))

dbDisconnect(con)

results |> filter(ticker == "AAPL") |> head(8)

date	ticker	close	ytd_avg	moving_avg_6d
2022-01-03	AAPL	182.01	182.0100	NA
2022-01-04	AAPL	179.70	180.8550	NA
2022-01-05	AAPL	174.92	178.8767	NA
2022-01-06	AAPL	172.00	177.1575	NA
2022-01-07	AAPL	172.17	176.1600	NA
2022-01-10	AAPL	172.19	175.4983	175.4983
2022-01-11	AAPL	175.08	175.4386	174.3433
2022-01-12	AAPL	175.53	175.4500	173.6483

The first row’s year-to-date average equals its closing price, because it is the only day so far. The moving average starts on the sixth row.

The year-to-date average restarts on the first trading day of each year:

results |>
  filter(ticker == "AAPL", date >= "2022-12-28", date <= "2023-01-05")

date	ticker	close	ytd_avg	moving_avg_6d
2022-12-28	AAPL	126.04	155.0364	131.3183
2022-12-29	AAPL	129.61	154.9347	130.8700
2022-12-30	AAPL	129.93	154.8351	129.9500
2023-01-03	AAPL	125.07	125.0700	128.7567
2023-01-04	AAPL	126.36	125.7150	127.8400
2023-01-05	AAPL	125.02	125.4833	127.0050

On January 3, 2023, the year-to-date average drops back to that day’s price. The moving average does not restart, because the five days before it are still the most recent trading days.

5 Checking the Results

To make sure the SQL is correct, I calculate the same averages a second way, with dplyr in R, and compare them.

check <- prices |>
  group_by(ticker, year = year(date)) |>
  arrange(date, .by_group = TRUE) |>
  mutate(ytd_check = cummean(close)) |>
  group_by(ticker) |>
  mutate(ma_check = (close + lag(close, 1) + lag(close, 2) +
                     lag(close, 3) + lag(close, 4) + lag(close, 5)) / 6) |>
  ungroup()

comparison <- results |>
  inner_join(check, by = c("ticker", "date"))

tibble(
  rows_compared             = nrow(comparison),
  largest_ytd_difference    = max(abs(comparison$ytd_avg - comparison$ytd_check)),
  largest_6day_difference   = max(abs(comparison$moving_avg_6d - comparison$ma_check), na.rm = TRUE),
  empty_6day_values_match   = all(is.na(comparison$moving_avg_6d) == is.na(comparison$ma_check))
)

rows_compared	largest_ytd_difference	largest_6day_difference	empty_6day_values_match
3546	0	0	TRUE

The two methods agree on all 3,546 rows: the largest difference is 0, and the empty moving-average values are in the same places.

6 Results

6.1 Latest values

results |>
  group_by(ticker) |>
  slice_max(date) |>
  ungroup() |>
  mutate(across(c(close, ytd_avg, moving_avg_6d), ~ round(.x, 2)),
         `close vs YTD avg` = scales::percent((close - ytd_avg) / ytd_avg, 0.1))

date	ticker	close	ytd_avg	moving_avg_6d	close vs YTD avg
2026-09-18	AAPL	336.13	287.56	333.70	16.9%
2026-09-18	AMZN	253.71	241.62	251.60	5.0%
2026-09-18	MSFT	493.78	425.49	496.66	16.0%

6.2 2026 so far

results |>
  filter(date >= "2026-01-01") |>
  select(date, ticker, `Closing price` = close, `Six-day moving average` = moving_avg_6d,
         `Year-to-date average` = ytd_avg) |>
  pivot_longer(-c(date, ticker), names_to = "series", values_to = "price") |>
  mutate(series = factor(series, levels = c("Closing price", "Six-day moving average",
                                            "Year-to-date average"))) |>
  ggplot(aes(x = date, y = price, color = series, linewidth = series)) +
  geom_line() +
  facet_wrap(~ ticker, ncol = 1, scales = "free_y") +
  scale_color_manual(values = c("Closing price" = "grey70",
                                "Six-day moving average" = "#2a78d6",
                                "Year-to-date average" = "#eb6834"), name = NULL) +
  scale_linewidth_manual(values = c(0.5, 0.9, 0.9), guide = "none") +
  scale_y_continuous(labels = scales::dollar) +
  labs(x = NULL, y = NULL) +
  theme_minimal(base_size = 12) +
  theme(legend.position = "top", strip.text = element_text(face = "bold", hjust = 0))

Figure 1: Daily closing price in 2026 with the six-day moving average and the year-to-date average.

The chart shows what each average is good for:

• The six-day moving average follows the price closely but smooths out day-to-day noise.
• The year-to-date average moves slowly and becomes more stable as the year goes on, because each new day is a smaller share of the total. A price above the year-to-date average means the stock is trading higher than it has on average this year.

7 Conclusion

• One SQL query with two window functions calculates both averages for all three stocks and all years at once, without any loops.
• PARTITION BY keeps the stocks separate, and adding the year to the partition makes the year-to-date average restart each January.
• The ROWS BETWEEN frame controls how many days go into each average.
• The results match the same calculation done with dplyr in R.

The same query would work for any number of stocks, or for any other daily time series, by changing only the table.