Return AVG DayName

Hey I was wondering if there is a beastmode windows function that would allow me to return the avg dayname from the average of x?

 

I have data that includes a domain, a column for day name(made via beastmode), and an avg of impressions).

 

Any ideas? I thought the following would work if I added concat in front of it but I keep getting 0 as a returned response. 

avg(distinct DAYNAME(date))

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤
    Accepted Answer

    Average doesn't work for words, but you could use the DAYOFWEEK function to get the numerical day, average that and then convert that to the day name. Would look something like this:

    CASE ROUND(AVG(DAYOFWEEK(`date`)),0) WHEN 1 THEN 'Sunday'
    WHEN 2 THEN 'Monday'
    WHEN 3 THEN 'Tuesday'
    WHEN 4 THEN 'Wednesday'
    WHEN 5 THEN 'Thursday'
    WHEN 6 THEN 'Friday'
    WHEN 7 THEN 'Saturday'
    END

Answers

  • Oh my gosh I didn't even think to use DAYOFWEEK.

     

    It works well too!

     

    I was curious, do you think this returns there's a way to FLOOR or CEILING to get the lowest avg and highest avg to DAYOFWEEK?

  • Kind of like this?
    (CASE ROUND(AVG(FLOOR(DAYOFWEEK(`date`))),0) WHEN 1 THEN 'Sunday'
    WHEN 2 THEN 'Monday'
    WHEN 3 THEN 'Tuesday'
    WHEN 4 THEN 'Wednesday'
    WHEN 5 THEN 'Thursday'
    WHEN 6 THEN 'Friday'
    WHEN 7 THEN 'Saturday'
    END)

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    Yes, I thought about including floor or ceiling in my reply, but wasn't sure which way you would want to go. I would do some testing and just see what works best for your situation.

  • Will do! Thanks so much for your help! 

     

    Also, did I apply that correctly? Like the FLOOR to the query?

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    Actually FLOOR would replace ROUND and it would be outside the average function. Like this:

     

     FLOOR(AVG(DAYOFWEEK(`date`)))
  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    Here's link describing the floor and ceiling functions.

    https://www.mathsisfun.com/sets/function-floor-ceiling.html

     

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    ... oof are you trying to figure out on which day of the week people tend to visit your site?

    if yes, then you should definitely bake day of week into your dataset via ETL, different implementations of dayofweek will shift Monday from 1 to 0, and that's not the sort of thing you want to guess about.

     

    meanwhile, it's interesting you mention avg. number of impressions, b/c depending on the granularity of your dataset (what each row of data represents), the avg. number of impressions may differ from the avg day of week in your dataset.  

     

    make sure to be sure the math you're calculating is answering the business question!