Brian Dunning wrote:
> My brain just squirted out my ear trying to figure this out, so maybe
> one of you can help me scoop it back up.
Yummy, fresh brain! ;-)
> 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).
According to your table definitions:
SELECT `account_id`, `company`, AVG(`hits`) AS avg_hits
JOIN `hits_per_day` USING (`account_id`)
WHERE `date` BETWEEN CURRENT_DATE() - INTERVAL 8 DAY AND CURRENT_DATE()
- INTERVAL 1 DAY
GROUP BY `account_id`
ORDER BY avg_hits DESC
Jigal van Hemert.