library(tidyverse)
library(lubridate)
library(scales)
library(readxl)

# Set the correct working directory
setwd("C:/Users/William/OneDrive/Desktop/hw")

# Verify it worked
getwd()

## [1] "C:/Users/William/OneDrive/Desktop/hw"

# List files to confirm you can see bike_orderlines
list.files()

##  [1] "bike_orderlines (5).xlsx"       "bike_orderlines.csv"           
##  [3] "bikes.csv.xlsx"                 "final-exam.html"               
##  [5] "final-financial-database.html"  "final-financial-database.Rmd"  
##  [7] "final-financial-database_files" "final exam.Rmd"                
##  [9] "final financial database.Rmd"   "final.html"                    
## [11] "final.Rmd"                      "final_files"                   
## [13] "finanace-database.html"         "finanace database.Rmd"         
## [15] "hw10.html"                      "hw10.Rmd"                      
## [17] "New folder"                     "rsconnect"

# Now read the file
library(readxl)
bike_orderlines <- read_excel("bikes.csv.xlsx")
# In the chunk where you read the file, change it to:


# Then in the next chunk, change this line:
# OLD (won't work):
# sales_data <- sales_data_raw %>%

# NEW (will work):
sales_data <- bike_orderlines %>%
  mutate(
    order_date = as.Date(order_date),
    year = year(order_date)
  ) %>%
  select(order_date, category_1, category_2, total_price)

# Quarterly aggregation
total_quarterly <- sales_data %>%
  mutate(date_rounded = floor_date(order_date, unit = "quarter")) %>%
  group_by(date_rounded) %>%
  summarise(total_revenue = sum(total_price))

# Monthly aggregation
total_monthly <- sales_data %>%
  mutate(date_rounded = floor_date(order_date, unit = "month")) %>%
  group_by(date_rounded) %>%
  summarise(total_revenue = sum(total_price))

# Weekly aggregation
total_weekly <- sales_data %>%
  mutate(date_rounded = floor_date(order_date, unit = "week")) %>%
  group_by(date_rounded) %>%
  summarise(total_revenue = sum(total_price))

# Category quarterly
category_quarterly <- sales_data %>%
  mutate(date_rounded = floor_date(order_date, unit = "quarter")) %>%
  group_by(category_1, category_2, date_rounded) %>%
  summarise(total_revenue = sum(total_price)) %>%
  ungroup()

# Category monthly
category_monthly <- sales_data %>%
  mutate(date_rounded = floor_date(order_date, unit = "month")) %>%
  group_by(category_1, category_2, date_rounded) %>%
  summarise(total_revenue = sum(total_price)) %>%
  ungroup()

# Category weekly
category_weekly <- sales_data %>%
  mutate(date_rounded = floor_date(order_date, unit = "week")) %>%
  group_by(category_1, category_2, date_rounded) %>%
  summarise(total_revenue = sum(total_price)) %>%
  ungroup()

SECTION 1: TOTAL BIKE SALES

# Chart 1: Total Sales - Quarterly Trends
p1 <- ggplot(total_quarterly, aes(x = date_rounded, y = total_revenue)) +
  geom_point(size = 2, color = "#2c3e50") +
  geom_line(color = "#2c3e50") +
  geom_smooth(method = "loess", span = 0.3, color = "blue", se = FALSE) +
  scale_y_continuous(labels = dollar_format(scale = 1e-6, suffix = "M")) +
  theme_bw() +
  labs(title = "Total Sales: Quarterly Trends", x = "", y = "Revenue (USD)")

print(p1)

# Chart 2: Total Sales - Monthly Trends
p2 <- ggplot(total_monthly, aes(x = date_rounded, y = total_revenue)) +
  geom_point(size = 1.5, color = "#2c3e50") +
  geom_line(color = "#2c3e50", alpha = 0.3) +
  geom_smooth(method = "loess", span = 0.1, color = "blue", se = TRUE) +
  scale_y_continuous(labels = dollar_format(scale = 1e-6, suffix = "M")) +
  theme_bw() +
  labs(title = "Total Sales: Monthly Trends", x = "", y = "Revenue (USD)")

print(p2)

# Chart 3: Total Sales - Weekly Trends
p3 <- ggplot(total_weekly, aes(x = date_rounded, y = total_revenue)) +
  geom_point(size = 1, color = "#2c3e50", alpha = 0.5) +
  geom_smooth(method = "loess", span = 0.1, color = "blue", se = TRUE) +
  scale_y_continuous(labels = dollar_format(scale = 1e-6, suffix = "M")) +
  theme_bw() +
  labs(title = "Total Sales: Weekly Trends", x = "", y = "Revenue (USD)")

print(p3)

SECTION 2: ROAD BIKE SALES

# Filter for Road Bikes
road_qt <- category_quarterly %>% filter(category_1 == "Road")
road_mo <- category_monthly %>% filter(category_1 == "Road")
road_wk <- category_weekly %>% filter(category_1 == "Road")

# Chart 4: Road - Quarterly
p4 <- ggplot(road_qt, aes(x = date_rounded, y = total_revenue)) +
  geom_line(color = "black") +
  geom_point(size = 1.5) +
  facet_wrap(~ category_2, ncol = 1, scales = "free_y") +
  scale_y_continuous(labels = dollar_format(scale = 1e-3, suffix = "K")) +
  theme_bw() +
  labs(title = "Road Sales: Quarterly", x = "", y = "Revenue")

print(p4)

# Chart 5: Road - Monthly
p5 <- ggplot(road_mo, aes(x = date_rounded, y = total_revenue)) +
  geom_point(size = 1.5, alpha = 0.5) +
  geom_smooth(method = "loess", span = 0.3, color = "red", se = TRUE) +
  facet_wrap(~ category_2, ncol = 1, scales = "free_y") +
  scale_y_continuous(labels = dollar_format(scale = 1e-3, suffix = "K")) +
  theme_bw() +
  labs(title = "Road Sales: Monthly", x = "", y = "Revenue")

print(p5)

# Chart 6: Road - Weekly
p6 <- ggplot(road_wk, aes(x = date_rounded, y = total_revenue)) +
  geom_point(size = 1, alpha = 0.3) +
  geom_smooth(method = "loess", span = 0.1, color = "red", se = TRUE) +
  facet_wrap(~ category_2, ncol = 1, scales = "free_y") +
  scale_y_continuous(labels = dollar_format(scale = 1e-3, suffix = "K")) +
  theme_bw() +
  labs(title = "Road Sales: Weekly", x = "", y = "Revenue")

print(p6)

SECTION 3: MOUNTAIN BIKE SALES

# Filter for Mountain Bikes
mtn_qt <- category_quarterly %>% filter(category_1 == "Mountain")
mtn_mo <- category_monthly %>% filter(category_1 == "Mountain")
mtn_wk <- category_weekly %>% filter(category_1 == "Mountain")

# Chart 7: Mountain - Quarterly
p7 <- ggplot(mtn_qt, aes(x = date_rounded, y = total_revenue)) +
  geom_line(color = "black") +
  geom_point(size = 1.5) +
  facet_wrap(~ category_2, ncol = 1, scales = "free_y") +
  scale_y_continuous(labels = dollar_format(scale = 1e-3, suffix = "K")) +
  theme_bw() +
  labs(title = "Mountain Sales: Quarterly", x = "", y = "Revenue")

print(p7)

# Chart 8: Mountain - Monthly
p8 <- ggplot(mtn_mo, aes(x = date_rounded, y = total_revenue)) +
  geom_point(size = 1.5, alpha = 0.5, color = "#2ecc71") +
  geom_smooth(method = "loess", span = 0.3, color = "#27ae60", se = TRUE) +
  facet_wrap(~ category_2, ncol = 1, scales = "free_y") +
  scale_y_continuous(labels = dollar_format(scale = 1e-3, suffix = "K")) +
  theme_bw() +
  labs(title = "Mountain Sales: Monthly", x = "", y = "Revenue")

print(p8)

# Chart 9: Mountain - Weekly
p9 <- ggplot(mtn_wk, aes(x = date_rounded, y = total_revenue)) +
  geom_point(size = 1, alpha = 0.3, color = "#2ecc71") +
  geom_smooth(method = "loess", span = 0.1, color = "#27ae60", se = TRUE) +
  facet_wrap(~ category_2, ncol = 1, scales = "free_y") +
  scale_y_continuous(labels = dollar_format(scale = 1e-3, suffix = "K")) +
  theme_bw() +
  labs(title = "Mountain Sales: Weekly", x = "", y = "Revenue")

print(p9)

final exam

汪華黎-112031070

2025-12-23

SECTION 1: TOTAL BIKE SALES

SECTION 2: ROAD BIKE SALES

SECTION 3: MOUNTAIN BIKE SALES