List:General Discussion« Previous MessageNext Message »
From:Jigal van Hemert Date:March 18 2010 6:18pm
Subject:Re: 7-day average
View as plain text  
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.
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