How do I determine for each order whether it is over or under the average order value in SQL

Question

My Table looks like this

CUST   Date       Value
102  2019-12-03   7
101  2019-12-06   8
102  2019-12-06   7
102  2019-12-13   10
102  2019-12-17   5
102  2019-12-18   5
103  2019-12-22   5
102  2019-12-22   7

Answer 1

Here is one way to do this using R with dplyr library.

library(dplyr)

df %>%
  group_by(CUST) %>%
  mutate(Label = ifelse(Value > mean(Value, na.rm = TRUE), 
                        'over average', 'under average'))

#    CUST  Date       Value Label        
#  <int> <chr>      <int> <chr>        
#1   102 2019-12-03     7 over average 
#2   101 2019-12-06     8 under average
#3   102 2019-12-06     7 over average 
#4   102 2019-12-13    10 over average 
#5   102 2019-12-17     5 under average
#6   102 2019-12-18     5 under average
#7   103 2019-12-22     5 under average
#8   102 2019-12-22     7 over average 

data

df <- structure(list(CUST = c(102L, 101L, 102L, 102L, 102L, 102L, 103L, 
102L), Date = c("2019-12-03", "2019-12-06", "2019-12-06", "2019-12-13", 
"2019-12-17", "2019-12-18", "2019-12-22", "2019-12-22"), Value = c(7L, 
8L, 7L, 10L, 5L, 5L, 5L, 7L)), class = "data.frame", row.names = c(NA, -8L))