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.
