Find (or ask an LLM to generate!) a dataset that includes time series for two or more separate items. For example, you could use end of day stock or cryptocurrency prices since Jan 1, 2022 for several instruments.
Use window functions (in SQL or dplyr) to calculate the year-to-date average and the six-day moving averages for each item. ## Approach
I have daily temperature reports for four buildings. Each report contains 24 temperature readings collected every 15 minutes. I plan to combine these CSV files into a single dataset, then calculate the YTD average and a 6-day moving average.
The 6-day moving average will help identify temperature drops, which may indicate boiler issues such as leaks or air vent locks in distributed steam pipes
Data set from https://www.bitget.com/price/filecoin/historical-data
Running Code
library(dplyr)
Attaching package: 'dplyr'
The following objects are masked from 'package:stats':
filter, lag
The following objects are masked from 'package:base':
intersect, setdiff, setequal, union
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag() masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
#install.packages("slider")library(slider)mydata_utc <- mydata_utc %>%arrange(timeopen)%>%mutate(ma_6day =slide_dbl(volume_million, mean, .before =5, .complete =TRUE))ggplot(mydata_utc, aes(x = timeopen)) +geom_line(aes(y = volume_million, color ="Volume"), linewidth =1) +geom_line(aes(y = ma_6day, color ="6-Day MA"), linewidth =1) +scale_color_manual(values =c("Volume"="steelblue", "6-Day MA"="red")) +labs(x =" Year ", y ="Volume in million", color ="Legend") +theme_minimal()
Warning: Removed 5 rows containing missing values or values outside the scale range
(`geom_line()`).
ggsave("m6_days_sales.png", width =8, height =6)
Warning: Removed 5 rows containing missing values or values outside the scale range
(`geom_line()`).
Conclusion
The 6-day moving average, calculated using window functions, provides a smoothed view of the volume over time, reducing the impact of daily fluctuations or anomalies. By applying the window function (slide_dbl), each data point incorporates the values of the previous 5 days plus the current day, giving a rolling average that highlights underlying trends.
From the analysis:
Short-term spikes or drops in volume are effectively smoothed, making trends more visible.compare the blue line and red line in the last figure
Comparing the raw volume to the 6-day moving average allows us to quickly identify periods of sustained increase or decrease.
Window functions make it easy to compute such rolling aggregates without collapsing the dataset, preserving the granularity of daily data for further analysis.
Overall, the 6-day moving average serves as a robust indicator of short-term trends, while window functions provide the flexibility to calculate it efficiently and consistently across the dataset.
LLMS used:
• OpenAI. (2025). ChatGPT (Version 5.2) [Large language model]. https://chat.openai.com. Accessed Feb 14, 2026.
