List:General Discussion« Previous MessageNext Message »
From:Brian Dunning Date:March 18 2010 5:36pm
Subject:7-day average
View as plain text  
My brain just squirted out my ear trying to figure this out, so maybe one of you can help
me scoop it back up.

I have a table of accounts, and a table of hits-per-day per account. I'm trying to sort my
accounts by the most hits-per-day, averaged over the preceding 7 days (8 days ago through
yesterday).

Table: accounts
+------------+---------+
| account_id | company |
+------------+---------+
|          1 | ABC     |
|          2 | DEF     |
|          3 | GHI     |
+------------+---------+

Table: hits_per_day
+------------+------------+------+
| account_id |    date    | hits |
+------------+------------+------+
|          1 | 2010-03-01 |   10 |
|          1 | 2010-03-12 |   10 |
|          2 | 2010-03-12 |   10 |
|          3 | 2010-03-12 |   10 |
|          1 | 2010-03-15 |   30 |
|          2 | 2010-03-15 |   70 |
|          3 | 2010-03-15 |   50 |
|          3 | 2010-03-18 |   10 |
+------------+------------+------+
* Notice the first and last rows should be excluded because they are not between 8 days
ago and yesterday.

So the results should look like this:
+------------+---------+----------+
| account_id | company | avg_hits |
+------------+---------+----------+
|          2 | DEF     |       40 |
|          3 | GHI     |       30 |
|          1 | ABC     |       20 |
+------------+---------+----------+

I hope that makes sense! I've driven myself crazy with this to the point that I can no
longer think about it clearly. Thanks for any help.



Thread
7-day averageBrian Dunning18 Mar
  • Re: 7-day averageJigal van Hemert18 Mar
  • Re: 7-day averageChris W18 Mar
    • Re: 7-day averageBrian Dunning18 Mar