083-exploring-a-single-table.Rmd

# Asking Business Questions From a Single Table {#chapter_exploring-a-single-table}

> This chapter explores:
>
>   * Issues that come up when investigating a single table from a business perspective
>   * Show the multiple data anomalies found in a single AdventureWorks table (*salesorderheader*)
>   * The interplay between "data questions" and "business questions"

The previous chapter has demonstrated some of the automated techniques for showing what's in a table using some standard R functions and packages.  Now we demonstrate a step-by-step process of making sense of what's in one table with more of a business perspective.  We illustrate the kind of detective work that's often involved as we investigate the *organizational meaning* of the data in a table.  We'll investigate the `salesorderheader` table in the `sales` schema in this example to understand the sales profile of the "AdventureWorks" business.  We show that there are quite a few interpretation issues even when we are examining just 3 out of the 25 columns in one table.

For this kind of detective work we are seeking to understand the following elements separately and as they interact with each other:

  * What data is stored in the database
  * How information is represented
  * How the data is entered at a day-to-day level to represent business activities
  * How the business itself is changing over time

## Setup our standard working environment

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
options(digits = 10)
sleep_default <- 3
```

Use these libraries:
```{r libraries, message=FALSE}
library(tidyverse)
library(DBI)
library(RPostgres)
library(connections)
library(glue)
require(knitr)
library(dbplyr)
library(sqlpetr)
library(bookdown)
library(here)
library(lubridate)
library(gt)
library(scales)
library(patchwork)
theme_set(theme_light())
```

Connect to `adventureworks`.  In an interactive session we prefer to use `connections::connection_open` instead of dbConnect


```{r, start adventureworks and connect}
sp_docker_start("adventureworks")
Sys.sleep(sleep_default)
con <- dbConnect(
  RPostgres::Postgres(),
  # without the previous and next lines, some functions fail with bigint data 
  #   so change int64 to integer
  bigint = "integer",  
  host = "localhost",
  port = 5432,
  user = "postgres",
  password = "postgres",
  dbname = "adventureworks")
```

Some queries generate big integers, so we need to include `RPostgres::Postgres()` and `bigint = "integer"` in the connections statement because some functions in the tidyverse object to the **bigint** datatype.


## A word on naming 

> You will find that many tables will have columns with the same name in an enterprise database.  For example, in the *AdventureWorks* database, almost all tables have columns named `rowguid` and `modifieddate` and there are many other examples of names that are reused throughout the database.  Duplicate columns are best renamed or deliberately dropped.  The meaning of a column depends on the table that contains it, so as you pull a column out of a table, when renaming it the collumns provenance should be reflected in the new name.
>
> Naming columns carefully (whether retrieved from the database or calculated)  will pay off, especially as our queries become more complex. Using `soh` as an abbreviation of *sales order header* to tag columns or statistics that are derived from the `salesorderheader` table, as we do in this book, is one example of an intentional naming strategy: it reminds us of the original source of the data.  You, future you, and your collaborators will appreciate the effort no matter what naming convention you adopt.  And a naming convention when rigidly applied can yield some long and ugly names.
>
> In the following example `soh` appears in different positions in the column name but it is easy to guess at a glance that the data comes from the `salesorderheader` table.
>
> Naming derived tables is just as important as naming derived columns.

## The overall AdventureWorks sales picture

We begin by looking at Sales on a yearly basis, then consider monthly sales.  We discover that half way through the period represented in the database, the business appears to begin selling online, which has very different characteristics than sales by Sales Reps.  We then look at the details of how Sales Rep sales are recorded in the system and discover a data anomaly that we can correct. 

## Date range of the data

As we begin to plot the businesses performance over time, let's get familiar with the date ranges we'll be working with. 

```{r Date Table}
dbGetQuery(con,
"SELECT DATE_PART('year', ModifiedDate) as Year,
  MIN(CAST(ModifiedDate AS DATE)) MinDate,
  MAX(CAST(ModifiedDate AS DATE)) MaxDate
FROM sales.SalesOrderDetail
GROUP BY DATE_PART('year', ModifiedDate)
Order by Year"
)
```


## Annual sales

On an annual basis, are sales dollars trending up, down or flat? We begin with annual revenue and number of orders.  

```{r Calculate time period and annual sales dollars 1}
annual_sales <- tbl(con, in_schema("sales", "salesorderheader")) %>%
  mutate(year = substr(as.character(orderdate), 1, 4)) %>%
  group_by(year) %>%
  summarize(
    min_soh_orderdate = min(orderdate, na.rm = TRUE),
    max_soh_orderdate = max(orderdate, na.rm = TRUE),
    total_soh_dollars = round(sum(subtotal, na.rm = TRUE), 2),
    avg_total_soh_dollars = round(mean(subtotal, na.rm = TRUE), 2),
    soh_count = n()
  ) %>%
  arrange(year) %>%
  select(
    year, min_soh_orderdate, max_soh_orderdate, total_soh_dollars,
    avg_total_soh_dollars, soh_count
  ) %>% 
  collect() 
  
```

Note that all of this query is running on the server since the `collect()` statement is at the very end. 

```{r Show the column format of the resulting query}
annual_sales %>% str()
```

We hang on to some date information for later use in plot titles.

```{r You will need time periods later on.}
min_soh_dt <- min(annual_sales$min_soh_orderdate)
max_soh_dt <- max(annual_sales$max_soh_orderdate)
```

### Annual summary of sales, number of transactions and average sale

```{r , fig.height=4, fig.width=5, fig.cap = "AdventureWorks Annual Sales"}
tot_sales <- ggplot(data = annual_sales, aes(x = year, y = total_soh_dollars/100000)) +
  geom_col() +
  geom_text(aes(label = round(as.numeric(total_soh_dollars/100000), digits = 0)), vjust = 1.5, color = "white") +
  scale_y_continuous(labels = scales::dollar_format()) +
  labs(
    title = "Total Sales per Year - Millions",
    x = NULL,
    y = "Sales $M"
  )
```
Both 2011 and 2014 turn out to be are shorter time spans than the other two years, making comparison interpretation difficult.  Still, it's clear that 2013 was the best year for annual sales dollars.

Comparing the number of orders per year has roughly the same overall pattern (2013 ranks highest, etc.) but the proportions between the years are quite different.

```{r number of orders, echo=FALSE}
num_orders <- ggplot(data = annual_sales, aes(x = year, y = as.numeric(soh_count))) +
  geom_col() +
  geom_text(aes(label = round(as.numeric(soh_count), digits = 0)), vjust = 1.5, color = "white") +
  labs(
    title = "Number of Orders per Year",
    y = "Total Number of Orders",
    x = NULL
  )
```

Although 2013 was the best year in terms of total number of orders, there were many more in 2014 compared with 2012.  That suggests looking at the average dollars per sale for each year.

### Average dollars per sale
```{r , echo=FALSE, fig.cap="Average dollars per sale"}
avg_sale <- ggplot(data = annual_sales, aes(x = year, y = avg_total_soh_dollars)) +
  geom_col() +
  scale_y_continuous(labels = scales::dollar_format()) +
  geom_text(aes(label = round(avg_total_soh_dollars, digits = 0)), vjust = 1.5, color = "white") +
  labs(
    title = "Average Dollars per Sale",
    x = glue("Years between ", {format(min_soh_dt, "%B %d, %Y")} , " to  ", 
            {format(max_soh_dt, "%B %d, %Y")}),
    y = "Average Sale Amount"
  )
```

```{r , fig.height=7, fig.cap="AdventureWorks sales performance"}
(tot_sales + num_orders) / avg_sale
```

That's a big drop between average sale of more than $7,000 in the first two years down to the $3,000 range in the last two.  There has been a remarkable change in this business.  At the same time the total number of orders shot up from less than 4,000 a year to more than 14,000.  **Why are the number of orders increasing, but the average dollar amount of a sale is dropping?  **

Perhaps monthly monthly sales has the answer.  We adapt the first query to group by month and year.

## Monthly Sales

Our next iteration drills down from annual sales dollars to monthly sales dollars. For that we download the orderdate as a date, rather than a character variable for the year.  R handles the conversion from the PostgreSQL date-time to an R date-time.  We then convert it to a simple date with a `lubridate` function.

The following query uses the [postgreSQL function `date_trunc`](https://www.postgresqltutorial.com/postgresql-date_trunc/), which is equivalent to `lubridate`'s `round_date` function in R.  If you want to push as much of the processing as possible onto the database server and thus possibly deal with smaller datasets in R, interleaving [postgreSQL functions](https://www.postgresql.org/docs/current/functions.html) into your dplyr code will help.

```{r monthly sales retrieval - sql}
monthly_sales <- tbl(con, in_schema("sales", "salesorderheader")) %>%
  select(orderdate, subtotal) %>%
  mutate(
    orderdate = date_trunc('month', orderdate)
  ) %>%
  group_by(orderdate) %>%
  summarize(
    total_soh_dollars = round(sum(subtotal, na.rm = TRUE), 2),
    avg_total_soh_dollars = round(mean(subtotal, na.rm = TRUE), 2),
    soh_count = n()
  ) %>% 
  show_query() %>% 
  collect() 
```

> Note that ` date_trunc('month', orderdate)` gets passed through exactly "as is."

In many cases we don't really care whether our queries are executed by R or by the SQL server, but if we do care we need to substitute the `postgreSQL` equivalent for the R functions we might ordinarily use.  In those cases we have to check whether functions from R packages like `lubridate` and the equivalent `postgreSQL` functions are exactly alike.  Often they are subtly different: in the previous query the `postgreSQL` function produces a `POSIXct` column, not a `Date` so we need to tack on a mutate function once the data is on the R side as shown here:

```{r}
monthly_sales <-  monthly_sales %>% 
  mutate(orderdate = as.Date(orderdate))
```

Next let's plot the monthly sales data:

```{r , fig.cap = "Total Monthly Sales"}

ggplot(data = monthly_sales, aes(x = orderdate, y = total_soh_dollars)) +
  geom_col() +
  scale_y_continuous(labels = dollar) +
  theme(plot.title = element_text(hjust = 0.5)) + 
  labs(
    title = glue("Sales by Month\n", {format(min_soh_dt, "%B %d, %Y")} , " to  ", 
            {format(max_soh_dt, "%B %d, %Y")}),
    x = "Month",
    y = "Sales Dollars"
  )
```

That graph doesn't show how the business might have changed, but it is remarkable how much variation there is from one month to another -- particularly in 2012 and 2014.

### Check lagged monthly data

Because of the  month-over-month sales  variation. We'll use `dplyr::lag` to help find the delta and later visualize just how much month-to-month difference there is.

```{r}
monthly_sales <- arrange(monthly_sales, orderdate)

monthly_sales_lagged <- monthly_sales %>%
  mutate(monthly_sales_change = (dplyr::lag(total_soh_dollars)) - total_soh_dollars)

monthly_sales_lagged[is.na(monthly_sales_lagged)] = 0
```

```{r}
median(monthly_sales_lagged$monthly_sales_change, na.rm = TRUE)
(sum_lags <- summary(monthly_sales_lagged$monthly_sales_change))
```

The average month over month change in sales looks OK ($ `r prettyNum(sum_lags[["Mean"]], big.mark = ",", format = "f", digits = 2)`) although the Median is negative: $ `r prettyNum(sum_lags[["Mean"]], big.mark = ",", format = "f", digits = 2)`. There is a very wide spread in our month-over-month sales data between the lower and upper quartile. We can plot the variation as follows:


```{r, message=FALSE, error=FALSE, fig.cap = "Monthly Sales Change"}
ggplot(monthly_sales_lagged, aes(x = orderdate, y = monthly_sales_change)) +
  scale_x_date(date_breaks = "year", date_labels = "%Y", date_minor_breaks = "3 months") +
  geom_line() +
  # geom_point() +
  scale_y_continuous(limits = c(-6000000,5500000), labels = scales::dollar_format()) +
  theme(plot.title = element_text(hjust = .5)) + 
  labs(
    title = glue(
      "Monthly Sales Change \n",
      "Between ", {format(min_soh_dt, "%B %d, %Y")} , " and  ", 
            {format(max_soh_dt, "%B %d, %Y")}
    ),
    x = "Month",
    y = "Dollar Change"
  )
```

It looks like the big change in the business occurred in the summer of 2013 when the number of orders jumped but the dollar volume just continued to bump along.

### Comparing dollars and orders to a base year

To look at dollars and the number of orders together, we compare the monthly data to the yearly average for 2011.

```{r}
baseline_month <- "2011-07-01"
start_month <- monthly_sales %>%
  filter(orderdate == as.Date(baseline_month))
```

Express monthly data relative to `r start_month`

```{r }
monthly_sales_base_year_normalized_to_2011 <- monthly_sales %>%
  mutate(
    dollars = (100 * total_soh_dollars) / start_month$total_soh_dollars,
    number_of_orders = (100 * soh_count) / start_month$soh_count
  ) %>%
  ungroup()

monthly_sales_base_year_normalized_to_2011 <- monthly_sales_base_year_normalized_to_2011 %>%
  select(orderdate, dollars, `# of orders` = number_of_orders) %>%
  pivot_longer(-orderdate,
    names_to = "relative_to_2011_average",
    values_to = "amount"
  )
```


```{r , fig.cap = "Adventureworks Normalized Monthly Sales"}
monthly_sales_base_year_normalized_to_2011 %>%
  ggplot(aes(orderdate, amount, color = relative_to_2011_average)) +
  geom_line() +
  geom_hline(yintercept = 100) +
  scale_x_date(date_labels = "%Y-%m", date_breaks = "6 months") +
  labs(
    title = glue(
      "Adventureworks Normalized Monthly Sales\n",
      "Number of Sales Orders and Dollar Totals\n",
      {format(min_soh_dt, "%B %d, %Y")} , " to  ", 
            {format(max_soh_dt, "%B %d, %Y")}),
    x = "Date",
    y = "",
    color = glue(baseline_month, " values = 100")
  ) +
  theme(legend.position = c(.3,.75))
```

## The effect of online sales

We suspect that the business has changed a lot with the advent of online orders so we check the impact of `onlineorderflag` on annual sales.  The `onlineorderflag` indicates which sales channel accounted for the sale, **Sales Reps** or **Online**.

### Add `onlineorderflag` to our annual sales query

```{r Calculate time period and annual sales dollars- 3}
annual_sales_w_channel <- tbl(con, in_schema("sales", "salesorderheader")) %>%
  select(orderdate, subtotal, onlineorderflag) %>%
  collect() %>%
  mutate(
    orderdate = date(orderdate),
    orderdate = round_date(orderdate, "month"),
    onlineorderflag = if_else(onlineorderflag == FALSE,
      "Sales Rep", "Online"
    ),
    onlineorderflag = as.factor(onlineorderflag)
  ) %>%
  group_by(orderdate, onlineorderflag) %>%
  summarize(
    min_soh_orderdate = min(orderdate, na.rm = TRUE),
    max_soh_orderdate = max(orderdate, na.rm = TRUE),
    total_soh_dollars = round(sum(subtotal, na.rm = TRUE), 2),
    avg_total_soh_dollars = round(mean(subtotal, na.rm = TRUE), 2),
    soh_count = n()
  ) %>%
  select(
    orderdate, onlineorderflag, min_soh_orderdate,
    max_soh_orderdate, total_soh_dollars,
    avg_total_soh_dollars, soh_count
  )
```

Note that we are creating a factor and doing most of the calculations on the R side, not on the DBMS side.

### Annual Sales comparison

Start by looking at total sales.

```{r Calculate annual sales dollars , fig.cap = "Sales Channel Breakdown"}

ggplot(data = annual_sales_w_channel, aes(x = orderdate, y = total_soh_dollars)) +
  geom_col() +
  scale_y_continuous(labels = scales::dollar_format()) +
  facet_wrap("onlineorderflag") +
  labs(
    title = "AdventureWorks Monthly Sales",
    caption = glue( "Between ", {format(min_soh_dt, "%B %d, %Y")} , " - ", 
            {format(max_soh_dt, "%B %d, %Y")}),
    subtitle = "Comparing Online and Sales Rep sales channels",
    x = "Year",
    y = "Sales $"
  )
```

It looks like there are two businesses represented in the AdventureWorks database that have very different growth profiles. 

### Order volume comparison

```{r, fig.cap = "AdventureWorks Monthly Orders by Channel"}
ggplot(data = annual_sales_w_channel, aes(x = orderdate, y = as.numeric(soh_count))) +
  geom_col() +
  facet_wrap("onlineorderflag") +
  labs(
    title = "AdventureWorks Monthly orders",
    caption = glue( "Between ", {format(min_soh_dt, "%B %d, %Y")} , " - ", 
            {format(max_soh_dt, "%B %d, %Y")}),
    subtitle = "Comparing Online and Sales Rep sales channels",
    x = "Year",
    y = "Total number of orders"
  )
```

Comparing Online and Sales Rep sales, the difference in the number of orders is even more striking than the difference between annual sales.

### Comparing average order size: **Sales Reps** to **Online** orders

```{r , fig.cap = "Average dollar per Sale comparison"}
ggplot(data = annual_sales_w_channel, aes(x = orderdate, y = avg_total_soh_dollars)) +
  geom_col() +
  facet_wrap("onlineorderflag") +
  scale_y_continuous(labels = scales::dollar_format()) +
  labs(
    title = "AdventureWorks Average Dollars per Sale",
    x = glue( "Year - between ", {format(min_soh_dt, "%B %d, %Y")} , " - ", 
            {format(max_soh_dt, "%B %d, %Y")}),
    y = "Average sale amount"
  )
```


## Impact of order type on monthly sales

To dig into the difference between **Sales Rep** and **Online** sales we can look at monthly data.

### Retrieve monthly sales with the `onlineorderflag` 

This query puts the `collect` statement earlier than the previous queries.

```{r}
monthly_sales_w_channel <- tbl(con, in_schema("sales", "salesorderheader")) %>%
  select(orderdate, subtotal, onlineorderflag) %>%
  collect() %>% # From here on we're in R
  mutate(
    orderdate = date(orderdate),
    orderdate = floor_date(orderdate, unit = "month"),
    onlineorderflag = if_else(onlineorderflag == FALSE,
      "Sales Rep", "Online")
  ) %>% 
  group_by(orderdate, onlineorderflag) %>%
  summarize(
    min_soh_orderdate = min(orderdate, na.rm = TRUE),
    max_soh_orderdate = max(orderdate, na.rm = TRUE),
    total_soh_dollars = round(sum(subtotal, na.rm = TRUE), 2),
    avg_total_soh_dollars = round(mean(subtotal, na.rm = TRUE), 2),
    soh_count = n()
  ) %>%
  ungroup()
```


```{r}
monthly_sales_w_channel %>%
  rename(`Sales Channel` = onlineorderflag) %>%
  group_by(`Sales Channel`) %>%
  summarize(
    unique_dates = n(),
    start_date = min(min_soh_orderdate),
    end_date = max(max_soh_orderdate),
    total_sales = round(sum(total_soh_dollars)), 
    days_span = end_date - start_date
  ) %>%
  gt()
```

As this table shows, the **Sales Rep** dates don't match the **Online** dates.  They start on the same date, but have a different end.  The **Online** dates include 2 months that are not included in the Sales Rep sales (which are the main sales channel by dollar volume).

### Monthly variation compared to a trend line

Jumping to the trend line comparison, we see that the big the source of variation is on the Sales Rep side.

```{r , warning=FALSE, message=FALSE, fig.cap = "Monthly Sales Trend"}

ggplot(
  data = monthly_sales_w_channel,
  aes(
    x = orderdate, y = total_soh_dollars
  )
) +
  geom_line() +
  geom_smooth(se = FALSE) +
  facet_grid("onlineorderflag", scales = "free") +
  scale_y_continuous(labels = dollar) +
  scale_x_date(date_breaks = "year", date_labels = "%Y", date_minor_breaks = "3 months") +
  theme(plot.title = element_text(hjust = .5)) + # Center ggplot title
  labs(
    title = glue(
      "AdventureWorks Monthly Sales Trend"
    ),
    x = glue( "Month - between ", {format(min_soh_dt, "%B %d, %Y")} , " - ", 
           {format(max_soh_dt, "%B %d, %Y")}),
    y = "Sales Dollars"
  )
```

The **monthly** gyrations are much larger on the Sales Rep side, amounting to differences in a million dollars compared to small monthly variations of around $25,000 for the Online orders.

### Compare monthly lagged data by Sales Channel

First consider month-to-month change.
```{r monthly_sales_w_channel_lagged_by_month calc, warning=FALSE}
monthly_sales_w_channel_lagged_by_month <- monthly_sales_w_channel %>%
  group_by(onlineorderflag) %>%
  mutate(
    lag_soh_count = lag(soh_count, 1),
    lag_soh_total_dollars = lag(total_soh_dollars, 1),
    pct_monthly_soh_dollar_change =
      (total_soh_dollars - lag_soh_total_dollars) / lag_soh_total_dollars * 100,
    pct_monthly_soh_count_change =
      (soh_count - lag_soh_count) / lag_soh_count * 100
  )
```

The following table shows some wild changes in dollar amounts and number of sales from one month to the next.

```{r monthly_sales_w_channel_lagged_by_month table}
monthly_sales_w_channel_lagged_by_month %>%
  filter(abs(pct_monthly_soh_count_change) > 150 | 
         abs(pct_monthly_soh_dollar_change) > 150 ) %>% 
  ungroup() %>% 
  arrange(onlineorderflag, orderdate) %>% 
  mutate(
    total_soh_dollars = round(total_soh_dollars),
    lag_soh_total_dollars = round(lag_soh_total_dollars),
    pct_monthly_soh_dollar_change = round(pct_monthly_soh_dollar_change),
    pct_monthly_soh_count_change = round(pct_monthly_soh_count_change)) %>% 
  select(orderdate, onlineorderflag,  total_soh_dollars, lag_soh_total_dollars, 
         soh_count, lag_soh_count, pct_monthly_soh_dollar_change, pct_monthly_soh_count_change) %>% 
  # names()
  gt() %>%
  fmt_number(
    columns = c(3:4), decimals = 0) %>%
  fmt_percent(
    columns = c(7:8), decimals = 0) %>%
  cols_label(
    onlineorderflag = "Channel",
    total_soh_dollars = "$ this Month",
    lag_soh_total_dollars = "$ last Month",
    soh_count = "# this Month",
    lag_soh_count = "# last Month",
    pct_monthly_soh_dollar_change = "$ change",
    pct_monthly_soh_count_change = "# change"
  )
```


We suspect that the business has changed a lot with the advent of **Online** orders.

## Detect and diagnose the day of the month problem

There have been several indications that Sales Rep sales are recorded once a month while Online sales are recorded on a daily basis.

### Sales Rep Orderdate Distribution

Look at the dates when sales are entered for sales by **Sales Reps**.  The following query / plot combination shows this pattern.  and the exception for transactions entered on the first day of the month.

```{r , fig.cap = "Days of the month with Sales Rep activity recorded"}
  tbl(con, in_schema("sales", "salesorderheader")) %>%
  filter(onlineorderflag == FALSE) %>% # Drop online orders
  mutate(orderday = day(orderdate)) %>%
  count(orderday, name = "Orders") %>% 
  collect() %>% 
  full_join(tibble(orderday = seq(1:31))) %>% 
  mutate(orderday = as.factor(orderday)) %>% 
  ggplot(aes(orderday, Orders)) +
  geom_col() +
  coord_flip() +
  labs(title = "The first day of the month looks odd",
       x = "Day Number")
  
```

We can check on which months have orders entered on the first of the month.
```{r}
sales_rep_day_of_month_sales <- tbl(con, in_schema("sales", "salesorderheader")) %>%
  filter(onlineorderflag == FALSE) %>% # Drop online orders
  select(orderdate, subtotal) %>%
  mutate(
    year = year(orderdate),
    month = month(orderdate),
    day = day(orderdate)
  ) %>%
  count(year, month, day) %>% 
  collect() %>%
  pivot_wider(names_from = day, values_from = n, names_prefix = "day_", values_fill = list(day_1 = 0, day_28 = 0, day_29 = 0, day_30 = 0, day_31 = 0) ) %>% 
  as.data.frame() %>% 
  select(year, month, day_1, day_28, day_29, day_30, day_31) %>% 
  filter(day_1 > 0) %>% 
  arrange(year, month)

sales_rep_day_of_month_sales

```

There are two months with multiple sales rep order days for 2011, (11/08 and 11/10), one for 2012, (1201), and two in 2014, (14/01 and 14/03).  The 14/03 is the only three day sales rep order month.

Are there months where there were no sales recorded for the sales reps?

There are two approaches.  The first is to generate a list of months between the beginning and end of history and compare that to the Sales Rep records
```{r}
monthly_sales_rep_sales <- monthly_sales_w_channel %>% 
  filter(onlineorderflag == "Sales Rep") %>% 
  mutate(orderdate = as.Date(floor_date(orderdate, "month"))) %>% 
  count(orderdate)

str(monthly_sales_rep_sales)

date_list <- tibble(month_date = seq.Date(floor_date(as.Date(min_soh_dt), "month"), 
         floor_date(as.Date(max_soh_dt), "month"), 
         by = "month"),
         date_exists = FALSE)

date_list %>% 
  anti_join(monthly_sales_rep_sales, 
            by = c("month_date" = "orderdate") )
```


  * June, September, and November are missing for 2011. 
  * June for 2014

The second approach is to use the dates found in the database for online orders.  Defining "complete" may not always be as simple as generating a complete list of months.
```{r}
sales_order_header_online <- tbl(con, in_schema("sales", "salesorderheader")) %>% 
  filter(onlineorderflag == TRUE) %>% 
  mutate(
    orderdate = date_trunc('month', orderdate)
  ) %>%
  count(orderdate, name = "online_count") 

sales_order_header_sales_rep <- tbl(con, in_schema("sales", "salesorderheader")) %>% 
  filter(onlineorderflag == FALSE) %>% 
  mutate(
    orderdate = date_trunc('month', orderdate)
  ) %>%
  count(orderdate, name = "sales_rep_count") 

missing_dates <- sales_order_header_sales_rep %>% 
  full_join(sales_order_header_online) %>% 
  show_query() %>% 
  collect()

missing_dates <- sales_order_header_online %>% 
  anti_join(sales_order_header_sales_rep) %>% 
  arrange(orderdate) %>% 
  collect()

missing_dates
str(missing_dates)
```

And in this case they agree!

discuss February issues.  and stuff. 

look at each year sepraately as a diagnostic

Use the same pivot strategy on the corrected data.

difference between detective work with a graph and just print it out.  "now I see what's driving the hint." 

We have xx months when we add the month before and the month after the **suspicious months**.  We don't know whether the problem postings have been carried forward or backward.  We check for and eliminate duplicates as well.

*  Most of the **Sales Reps**' orders are entered on a single day of the month, unique days = 1. It is possible that these are monthly recurring orders that get released on a given day of the month.  If that is the case, what are the **Sales Reps** doing the rest of the month?
*  ** ?? The lines with multiple days, unique_days > 1, have a noticeable higher number of orders, so_cnt, and associated so dollars.?? **


## Correcting the order date for **Sales Reps**


### Define a date correction function in R

This code does the date-correction work on the R side:

```{r}
monthly_sales_rep_adjusted <- tbl(con, in_schema("sales", "salesorderheader")) %>%
  filter(onlineorderflag == FALSE) %>% 
  select(orderdate, subtotal, onlineorderflag) %>%
  group_by(orderdate) %>%
  summarize(
    total_soh_dollars = round(sum(subtotal, na.rm = TRUE), 2),
    soh_count = n()
  ) %>%
  mutate(
    orderdate = as.Date(orderdate),
    day = day(orderdate)
  ) %>%
  collect() %>%
  ungroup() %>% 
  mutate(
    adjusted_orderdate = case_when(
      day == 1L ~ orderdate -1,
      TRUE ~ orderdate
    ),
    year_month = floor_date(adjusted_orderdate, "month")
  ) %>% 
  group_by(year_month) %>% 
  summarize(
      total_soh_dollars = round(sum(total_soh_dollars, na.rm = TRUE), 2),
      soh_count = sum(soh_count)
    ) %>%
  ungroup()
```
Inspect:

```{r}
str(monthly_sales_rep_adjusted)
monthly_sales_rep_adjusted %>% filter(year(year_month) %in% c(2011,2014))
```

### Define and store a PostgreSQL function to correct the date{#define-postgres-date-function}

The following code defines a function on the server side to correct the date:

```{r}
dbExecute(
  con,
  "CREATE OR REPLACE FUNCTION so_adj_date(so_date timestamp, ONLINE_ORDER boolean) RETURNS timestamp AS $$
     BEGIN
        IF (ONLINE_ORDER) THEN
            RETURN (SELECT so_date);
        ELSE
            RETURN(SELECT CASE WHEN EXTRACT(DAY FROM so_date) = 1
                               THEN  so_date - '1 day'::interval
                               ELSE  so_date
                          END
                  );
        END IF;
 END; $$
LANGUAGE PLPGSQL;
"
)
```

### Use the PostgreSQL function

If you can do the heavy lifting on the database side, that's good.  R can do it, but it's best for finding the issues.

```{r}
monthly_sales_rep_adjusted_with_psql_function <- tbl(con, in_schema("sales", "salesorderheader")) %>%
  select(orderdate, subtotal, onlineorderflag) %>%
  mutate(
    orderdate = as.Date(orderdate)) %>%
  mutate(adjusted_orderdate = as.Date(so_adj_date(orderdate, onlineorderflag))) %>%
  filter(onlineorderflag == FALSE) %>%
  group_by(adjusted_orderdate) %>%
  summarize(
    total_soh_dollars = round(sum(subtotal, na.rm = TRUE), 2),
    soh_count = n()
  ) %>%
  collect() %>%
  mutate( year_month = floor_date(adjusted_orderdate, "month")) %>% 
    group_by(year_month) %>%
  ungroup() %>% 
  arrange(year_month)
```

```{r}
monthly_sales_rep_adjusted_with_psql_function %>% 
  filter(year(year_month) %in% c(2011,2014))

```

There's one minor difference between the two:
```{r}
all_equal(monthly_sales_rep_adjusted, monthly_sales_rep_adjusted_with_psql_function)
```


### Monthly Sales by Order Type with corrected dates -- relative to a trend line


```{r, eval=FALSE, fig.cap = "Resolved dates"}

monthly_sales_rep_as_is <- monthly_sales_w_channel %>%
  filter(onlineorderflag == "Sales Rep")


ggplot(
  data = monthly_sales_rep_adjusted,
  aes(x = year_month, y = soh_count)
) +
  geom_line(alpha = .5) +
  geom_smooth(se = FALSE) +
  geom_smooth(
    data = monthly_sales_rep_as_is, aes(
      orderdate, soh_count
    ), color = "red", alpha = .5,
    se = FALSE
  ) +
  theme(plot.title = element_text(hjust = .5)) + # Center ggplot title
  labs(
    title = glue(
      "Number of Sales per month using corrected dates\n",
      "Counting Sales Order Header records"
    ),
    x = paste0("Monthly - between ", min_soh_dt, " - ", max_soh_dt),
    y = "Number of Sales Recorded"
  )
```
 
 
```{r}
monthly_sales_rep_as_is <- monthly_sales_w_channel %>%
  filter(onlineorderflag == "Sales Rep") %>% 
  mutate(orderdate = as.Date(floor_date(orderdate, unit = "month"))) %>% 
  group_by(orderdate) %>% 
  summarize(
      total_soh_dollars = round(sum(total_soh_dollars, na.rm = TRUE), 2),
      soh_count = sum(soh_count)
    ) 

monthly_sales_rep_as_is %>% 
  filter(year(orderdate) %in% c(2011,2014))
```


```{r, fig.cap="Comparing monthly_sales_rep_adjusted and monthly_sales_rep_as_is"}
ggplot(
  data = monthly_sales_rep_adjusted,
  aes(x = year_month, y = soh_count)
  ) +
  geom_line(alpha = .5 , color = "green") +
  geom_point(color = "green") +
  geom_point(
    data = monthly_sales_rep_as_is,
    aes(orderdate, soh_count
    ), color = "red", alpha = .5
  ) +
  geom_smooth(aes(group=0), method="lm") +
  #Labeling
  theme(plot.title = element_text(hjust = .5)) + # Center ggplot title
  labs(
    title = glue(
      "Number of Sales per month using corrected dates\n",
      "Counting Sales Order Header records"
    ),
    subtitle = glue("Red dots are Sales Reps as is. Green line is adjusted."),
    caption = glue("Datasets Include: \n
                   monthly_sales_rep_adjusted, monthly_sales_rep_as_is"),
    x = paste0("Monthly - between ", min_soh_dt, " - ", max_soh_dt),
    y = "Number of Sales Recorded"
  )
```

```{r}
mon_sales <- monthly_sales_rep_adjusted %>% 
  rename(orderdate = year_month)

sales_original_and_adjusted <- bind_rows(mon_sales, monthly_sales_rep_as_is, .id = "date_kind")

```

Sales still seem to gyrate!  We have found that sales rep sales data is often very strange.

## Disconnect from the database and stop Docker

```{r}
dbDisconnect(con)
# when running interactively use:
connection_close(con) 

sp_docker_stop("adventureworks")
```