Not enough coffee yet, but hopefully this is helpful.
Everything i can think of takes two query steps:
query A: group(date) sum(visits)
query B: group(DayOfWeek(A.day) count(DayOfWeek(A.date) sum(A.visits) average: visits/count....
The key is to retain info that keeps each day...