Obtaining an average per day value of a field with MySQL's DATEDIFF function

Quite some time ago, I added a views field to my articles table, and implemented a small function to log a "view" on each of my articles as it was read. A lot of CMS & blog systems do this, and people often display the number of clicks/views/reads on the article page.

While I don't display that specific information currently on my articles, I wanted to calculate the average number of views/reads per day as a measure of determining which of my articles were the most popular, as simply putting up the total number of view/reads is basically going to give you a persistent list of the oldest articles, unless you get dugg, that list will likely not change.

This is easy to accomplish when using a MySQL datetime field for the article publish date.

Here is the basic MySQL code used;

SELECT title, views / DATEDIFF(CURDATE(), date) AS views_per_day
FROM articles
WHERE DATEDIFF(CURDATE(), date) > '14'
    AND views > '100'
ORDER BY `views_per_day` DESC
LIMIT 0, 10

This produces a list of the top 10 articles (over 14 days old with an excess of 100 views to avoid recent/front page posts diluting results), and displays their titles along with the average views per day, descending in order from highest views per day.