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.
