Introduction

SQL window functions perform calculations across groups of related rows without reducing the dataset to one row per group. They are useful for analyzing time-series data because each original observation can remain visible while cumulative and moving statistics are calculated.

This project uses daily stock-price data for Apple, Microsoft, and Google. PostgreSQL stores the observations, and SQL window functions will later calculate a year-to-date average and a six-day moving average for each company.

Data Collection

Daily adjusted closing prices were retrieved from Yahoo Finance using the R package quantmod and its getSymbols() function.

The dataset contains daily adjusted closing prices for the following companies:

The observations begin on January 3, 2022, and include one row for each available trading day and company. The latest observations used in this approach submission are dated September 16, 2026.

Each record contains:

Non-trading days were not created or estimated. Only available trading-day observations were stored.

Planned Approach

The project will follow these steps:

  1. Obtain daily adjusted closing prices for the three selected companies.
  2. Combine the observations into a consistent long-format dataset.
  3. Validate dates, company identifiers, prices, and duplicate records.
  4. Import the prepared data into PostgreSQL.
  5. Confirm the number and date range of observations for each company.
  6. Use a SQL window function to calculate the year-to-date average for each company and calendar year.
  7. Use a second SQL window function to calculate the six-day moving average for each company.
  8. Preserve each daily observation while adding the calculated averages.
  9. Review representative dates to confirm that the window calculations are correct.
  10. Present the SQL code, results, and conclusions in the final report.

The year-to-date calculation will partition the observations by company and calendar year, order them by date, and include all available records from the beginning of the year through the current row.

The six-day moving calculation will partition the observations by company, order them by date, and use the current observation together with the five preceding trading-day observations.

Database Design

The observations will be stored in a PostgreSQL table named daily_prices.

The table will contain:

A uniqueness rule will prevent more than one observation for the same stock symbol and trading date.

PostgreSQL and pgAdmin 4 will be used to store, inspect, validate, and later analyze the dataset. The calculations will not be performed until the data have been imported and validated.

Data Retrieval from PostgreSQL

The prepared observations were imported into the PostgreSQL table daily_prices. R retrieves the stored data to confirm that the database contains the expected records before any window calculations are performed.

library(DBI)
library(RPostgres)
daily_prices <- dbGetQuery(
  con,
  "
  SELECT price_date, symbol, company, adjusted_close
  FROM daily_prices
  ORDER BY symbol, price_date
  "
)

head(daily_prices)
##   price_date symbol company adjusted_close
## 1 2022-01-03   AAPL   Apple       177.7864
## 2 2022-01-04   AAPL   Apple       175.5300
## 3 2022-01-05   AAPL   Apple       170.8609
## 4 2022-01-06   AAPL   Apple       168.0087
## 5 2022-01-07   AAPL   Apple       168.1747
## 6 2022-01-10   AAPL   Apple       168.1943

Data Validation

The imported data were validated before applying any SQL window functions.

data.frame(
  total_observations = nrow(daily_prices),
  companies = length(unique(daily_prices$symbol)),
  duplicate_symbol_dates = sum(
    duplicated(daily_prices[c("symbol", "price_date")])
  ),
  missing_prices = sum(is.na(daily_prices$adjusted_close))
)
##   total_observations companies duplicate_symbol_dates missing_prices
## 1               3540         3                      0              0

AI Use

ChatGPT was used to help interpret the assignment requirements, organize the planned approach, improve the English writing, select an appropriate database structure, and provide coding guidance. I collected and imported the data, ran the validation code, reviewed the results, and confirmed the conclusions myself.