library(DBI)
library(RPostgres)
library(tidyverse)
library(dplyr)
library(readr)Week3B - Window Functions
Window Functions Analysis
Approach
Goal
The goal of this assignment is to utilize and understand the concept of Window Functions used in R or SQL.
Window Functions
What is a window function? It preserves information from the previous row as it iterates through the next row.
Some functions include, average, sum or any type of function that involves grouping things.
The Plan
The first thing to do is to generate a dataset, synthetic or based off real data, with an LLM or find a dataset on popular sites such as:
Kaggle
Google Databases
UCI Machine Learning Repository
Following that dataset, we need to do the following steps:
Clean the dataset (if necessary)
Understand window functions in SQL or R
Apply the concept of window functions for the dataset
Dataset
Given that this assignment allows leniency with either searching for a dataset online or generating a synthetic dataset using an LLM, I have taken the LLM approach and asked Gemeni to generate a synthetic dataset.
Codebase
Before being able to do anything with this csv file, the first thing to do was separate all of the data in Microsoft Excel using the =TEXTSPLIT() function. The reason this is essential is because when using a generated dataset from an LLM it pastes all of the information in one column separated by columns. So having to separate them into new columns using that function was a must. Following that we will need to import that dataset into R and to get rid of the first column as it is being used as a reference column.
Data Cleaning & Transformation
raw_data <- read_csv("https://raw.githubusercontent.com/HaiderrX/CUNY-SPS-MSDS/refs/heads/main/DATA607/Week%203%20-%20R%20Character%20Manipulation%20and%20Date%20Processing/inventory_synthetic_data%20-%20Sheet1.csv")
head(raw_data)# A tibble: 6 × 8
Date,Product_ID,Product_Name,U…¹ Date Product_ID Product_Name Units_Sold
<chr> <date> <chr> <chr> <dbl>
1 2026-08-01,PROD_A,Wireless Head… 2026-08-01 PROD_A Wireless He… 42
2 2026-08-01,PROD_B,Mechanical Ke… 2026-08-01 PROD_B Mechanical … 28
3 2026-08-01,PROD_C,Ergonomic Mou… 2026-08-01 PROD_C Ergonomic M… 65
4 2026-08-02,PROD_A,Wireless Head… 2026-08-02 PROD_A Wireless He… 38
5 2026-08-02,PROD_B,Mechanical Ke… 2026-08-02 PROD_B Mechanical … 31
6 2026-08-02,PROD_C,Ergonomic Mou… 2026-08-02 PROD_C Ergonomic M… 58
# ℹ abbreviated name:
# ¹`Date,Product_ID,Product_Name,Units_Sold,Price_Per_Unit,Revenue,Ending_Inventory_Count`
# ℹ 3 more variables: Price_Per_Unit <dbl>, Revenue <dbl>,
# Ending_Inventory_Count <dbl>colnames(raw_data)[1] "Date,Product_ID,Product_Name,Units_Sold,Price_Per_Unit,Revenue,Ending_Inventory_Count"
[2] "Date"
[3] "Product_ID"
[4] "Product_Name"
[5] "Units_Sold"
[6] "Price_Per_Unit"
[7] "Revenue"
[8] "Ending_Inventory_Count" As mentioned, we need to get rid of the first column in order to proceed. However we can also rename columns for conventional reasons.
clean_data <- raw_data |>
select(
date = Date,
product_id = Product_ID,
product_name = Product_Name,
units_sold = Units_Sold,
price_per_unit = Price_Per_Unit,
revenue = Revenue,
ending_inventory_count = Ending_Inventory_Count
)head(clean_data)# A tibble: 6 × 7
date product_id product_name units_sold price_per_unit revenue
<date> <chr> <chr> <dbl> <dbl> <dbl>
1 2026-08-01 PROD_A Wireless Headphones 42 90.0 3780.
2 2026-08-01 PROD_B Mechanical Keyboard 28 130. 3640.
3 2026-08-01 PROD_C Ergonomic Mouse 65 50.0 3249.
4 2026-08-02 PROD_A Wireless Headphones 38 90.0 3420.
5 2026-08-02 PROD_B Mechanical Keyboard 31 130. 4030.
6 2026-08-02 PROD_C Ergonomic Mouse 58 50.0 2899.
# ℹ 1 more variable: ending_inventory_count <dbl>glimpse(clean_data)Rows: 90
Columns: 7
$ date <date> 2026-08-01, 2026-08-01, 2026-08-01, 2026-08-02…
$ product_id <chr> "PROD_A", "PROD_B", "PROD_C", "PROD_A", "PROD_B…
$ product_name <chr> "Wireless Headphones", "Mechanical Keyboard", "…
$ units_sold <dbl> 42, 28, 65, 38, 31, 58, 55, 19, 72, 61, 24, 80,…
$ price_per_unit <dbl> 89.99, 129.99, 49.99, 89.99, 129.99, 49.99, 89.…
$ revenue <dbl> 3779.58, 3639.72, 3249.35, 3419.62, 4029.69, 28…
$ ending_inventory_count <dbl> 158, 92, 235, 120, 61, 177, 65, 42, 105, 204, 1…Data has been cleaned, we can now proceed.
SQL Connection
connection <- dbConnect(
RPostgres::Postgres(),
dbname = "products_db",
host = "localhost",
port = 5432,
user = "postgres",
password = "haider14xx"
)dbExecute(connection, "DROP TABLE IF EXISTS products_info;")[1] 0create_table_query <- "
CREATE TABLE IF NOT EXISTS products_info (
date DATE,
product_id VARCHAR(50),
product_name TEXT,
units_sold INT,
prices_per_unit NUMERIC(10, 2),
revenue NUMERIC(10,2),
ending_inventory_count INT
);
"
dbExecute(connection, create_table_query)[1] 0dbWriteTable(
con = connection,
name = "products_info",
value = clean_data,
overwrite = TRUE,
field.types = c(
date = "DATE",
product_id = "VARCHAR(50)",
product_name = "TEXT",
units_sold = "INTEGER",
price_per_unit = "NUMERIC(10, 2)",
revenue = "NUMERIC(10, 2)",
ending_inventory_count = "INTEGER"
)
)
dbGetQuery(connection, "SELECT * FROM products_info") date product_id product_name units_sold price_per_unit revenue
1 2026-08-01 PROD_A Wireless Headphones 42 89.99 3779.58
2 2026-08-01 PROD_B Mechanical Keyboard 28 129.99 3639.72
3 2026-08-01 PROD_C Ergonomic Mouse 65 49.99 3249.35
4 2026-08-02 PROD_A Wireless Headphones 38 89.99 3419.62
5 2026-08-02 PROD_B Mechanical Keyboard 31 129.99 4029.69
6 2026-08-02 PROD_C Ergonomic Mouse 58 49.99 2899.42
7 2026-08-03 PROD_A Wireless Headphones 55 89.99 4949.45
8 2026-08-03 PROD_B Mechanical Keyboard 19 129.99 2469.81
9 2026-08-03 PROD_C Ergonomic Mouse 72 49.99 3599.28
10 2026-08-04 PROD_A Wireless Headphones 61 89.99 5489.39
11 2026-08-04 PROD_B Mechanical Keyboard 24 129.99 3119.76
12 2026-08-04 PROD_C Ergonomic Mouse 80 49.99 3999.20
13 2026-08-05 PROD_A Wireless Headphones 48 89.99 4319.52
14 2026-08-05 PROD_B Mechanical Keyboard 33 129.99 4289.67
15 2026-08-05 PROD_C Ergonomic Mouse 63 49.99 3149.37
16 2026-08-06 PROD_A Wireless Headphones 50 89.99 4499.50
17 2026-08-06 PROD_B Mechanical Keyboard 30 129.99 3899.70
18 2026-08-06 PROD_C Ergonomic Mouse 71 49.99 3549.29
19 2026-08-07 PROD_A Wireless Headphones 68 89.99 6119.32
20 2026-08-07 PROD_B Mechanical Keyboard 45 129.99 5849.55
21 2026-08-07 PROD_C Ergonomic Mouse 94 49.99 4699.06
22 2026-08-08 PROD_A Wireless Headphones 73 89.99 6569.27
23 2026-08-08 PROD_B Mechanical Keyboard 41 129.99 5329.59
24 2026-08-08 PROD_C Ergonomic Mouse 88 49.99 4399.12
25 2026-08-09 PROD_A Wireless Headphones 35 89.99 3149.65
26 2026-08-09 PROD_B Mechanical Keyboard 22 129.99 2859.78
27 2026-08-09 PROD_C Ergonomic Mouse 51 49.99 2549.49
28 2026-08-10 PROD_A Wireless Headphones 44 89.99 3959.56
29 2026-08-10 PROD_B Mechanical Keyboard 26 129.99 3379.74
30 2026-08-10 PROD_C Ergonomic Mouse 60 49.99 2999.40
31 2026-08-11 PROD_A Wireless Headphones 49 89.99 4409.51
32 2026-08-11 PROD_B Mechanical Keyboard 29 129.99 3769.71
33 2026-08-11 PROD_C Ergonomic Mouse 67 49.99 3349.33
34 2026-08-12 PROD_A Wireless Headphones 52 89.99 4679.48
35 2026-08-12 PROD_B Mechanical Keyboard 34 129.99 4419.66
36 2026-08-12 PROD_C Ergonomic Mouse 70 49.99 3499.30
37 2026-08-13 PROD_A Wireless Headphones 46 89.99 4139.54
38 2026-08-13 PROD_B Mechanical Keyboard 31 129.99 4029.69
39 2026-08-13 PROD_C Ergonomic Mouse 64 49.99 3199.36
40 2026-08-14 PROD_A Wireless Headphones 70 89.99 6299.30
41 2026-08-14 PROD_B Mechanical Keyboard 48 129.99 6239.52
42 2026-08-14 PROD_C Ergonomic Mouse 91 49.99 4549.09
43 2026-08-15 PROD_A Wireless Headphones 77 89.99 6929.23
44 2026-08-15 PROD_B Mechanical Keyboard 52 129.99 6759.48
45 2026-08-15 PROD_C Ergonomic Mouse 99 49.99 4949.01
46 2026-08-16 PROD_A Wireless Headphones 40 89.99 3599.60
47 2026-08-16 PROD_B Mechanical Keyboard 25 129.99 3249.75
48 2026-08-16 PROD_C Ergonomic Mouse 55 49.99 2749.45
49 2026-08-17 PROD_A Wireless Headphones 43 89.99 3869.57
50 2026-08-17 PROD_B Mechanical Keyboard 27 129.99 3509.73
51 2026-08-17 PROD_C Ergonomic Mouse 62 49.99 3099.38
52 2026-08-18 PROD_A Wireless Headphones 51 89.99 4589.49
53 2026-08-18 PROD_B Mechanical Keyboard 32 129.99 4159.68
54 2026-08-18 PROD_C Ergonomic Mouse 68 49.99 3399.32
55 2026-08-19 PROD_A Wireless Headphones 47 89.99 4229.53
56 2026-08-19 PROD_B Mechanical Keyboard 28 129.99 3639.72
57 2026-08-19 PROD_C Ergonomic Mouse 66 49.99 3299.34
58 2026-08-20 PROD_A Wireless Headphones 53 89.99 4769.47
59 2026-08-20 PROD_B Mechanical Keyboard 36 129.99 4679.64
60 2026-08-20 PROD_C Ergonomic Mouse 73 49.99 3649.27
61 2026-08-21 PROD_A Wireless Headphones 72 89.99 6479.28
62 2026-08-21 PROD_B Mechanical Keyboard 44 129.99 5719.56
63 2026-08-21 PROD_C Ergonomic Mouse 89 49.99 4449.11
64 2026-08-22 PROD_A Wireless Headphones 81 89.99 7289.19
65 2026-08-22 PROD_B Mechanical Keyboard 50 129.99 6499.50
66 2026-08-22 PROD_C Ergonomic Mouse 95 49.99 4749.05
67 2026-08-23 PROD_A Wireless Headphones 39 89.99 3509.61
68 2026-08-23 PROD_B Mechanical Keyboard 21 129.99 2729.79
69 2026-08-23 PROD_C Ergonomic Mouse 54 49.99 2699.46
70 2026-08-24 PROD_A Wireless Headphones 45 89.99 4049.55
71 2026-08-24 PROD_B Mechanical Keyboard 29 129.99 3769.71
72 2026-08-24 PROD_C Ergonomic Mouse 61 49.99 3049.39
73 2026-08-25 PROD_A Wireless Headphones 50 89.99 4499.50
74 2026-08-25 PROD_B Mechanical Keyboard 31 129.99 4029.69
75 2026-08-25 PROD_C Ergonomic Mouse 69 49.99 3449.31
76 2026-08-26 PROD_A Wireless Headphones 54 89.99 4859.46
77 2026-08-26 PROD_B Mechanical Keyboard 35 129.99 4549.65
78 2026-08-26 PROD_C Ergonomic Mouse 74 49.99 3699.26
79 2026-08-27 PROD_A Wireless Headphones 48 89.99 4319.52
80 2026-08-27 PROD_B Mechanical Keyboard 33 129.99 4289.67
81 2026-08-27 PROD_C Ergonomic Mouse 67 49.99 3349.33
82 2026-08-28 PROD_A Wireless Headphones 69 89.99 6209.31
83 2026-08-28 PROD_B Mechanical Keyboard 46 129.99 5979.54
84 2026-08-28 PROD_C Ergonomic Mouse 92 49.99 4599.08
85 2026-08-29 PROD_A Wireless Headphones 75 89.99 6749.25
86 2026-08-29 PROD_B Mechanical Keyboard 49 129.99 6369.51
87 2026-08-29 PROD_C Ergonomic Mouse 97 49.99 4849.03
88 2026-08-30 PROD_A Wireless Headphones 37 89.99 3329.63
89 2026-08-30 PROD_B Mechanical Keyboard 23 129.99 2989.77
90 2026-08-30 PROD_C Ergonomic Mouse 52 49.99 2599.48
ending_inventory_count
1 158
2 92
3 235
4 120
5 61
6 177
7 65
8 42
9 105
10 204
11 168
12 225
13 156
14 135
15 162
16 106
17 105
18 91
19 238
20 210
21 297
22 165
23 169
24 209
25 130
26 147
27 158
28 86
29 121
30 98
31 237
32 242
33 281
34 185
35 208
36 211
37 139
38 177
39 147
40 69
41 129
42 56
43 192
44 227
45 207
46 152
47 202
48 152
49 109
50 175
51 90
52 58
53 143
54 222
55 211
56 115
57 156
58 158
59 79
60 83
61 86
62 185
63 244
64 205
65 135
66 149
67 166
68 114
69 95
70 121
71 85
72 234
73 71
74 204
75 165
76 217
77 169
78 91
79 169
80 136
81 224
82 100
83 90
84 132
85 225
86 241
87 235
88 188
89 218
90 183Successfully imported the table to SQL, now to start Window Function queries.
Window Functions
products_avg <- dbGetQuery(connection, "
SELECT ")