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
FROM `accounts`
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.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.