SELECT
pg.pagepath AS pagepath,
pg.id AS page_id,
pst.id AS post_id,
-- coalesce returns the first result that is not NULL:
-- either the sessions count or zero
coalesce(t.sessions, 0) AS today_pageviews,
coalesce(y.sessions, 0) AS yesterday_pageviews,
-- calculate the weighted session averages.
( -- Calculate avg_pageviews in the last 24 hours
(coalesce(t.sessions, 0) * 0.18819444444444) +
(coalesce(y.sessions, 0) * (1 - 0.18819444444444))
) AS avg_pageviews,
( -- Calulate how many days_old the post is
TIMESTAMPDIFF( hour, pst.post_date, NOW() ) - 1
) AS days_old,
( -- Calculate weighted_pageviews, using halflife to put less emphasis
-- on older posts
(
(coalesce(t.sessions, 0) * 0.18819444444444) +
(coalesce(y.sessions, 0) * (1 - 0.18819444444444))
) * POWER(
1/2,
( TIMESTAMPDIFF( hour, pst.post_date, NOW() ) - 1 ) / (2 * 24)
)
) AS weighted_pageviews
FROM
wp_analyticbridge_pages as pg
LEFT JOIN (
--
-- Nested select returns today's sessions.
--
SELECT
CAST(value as unsigned) as sessions,
page_id
FROM
wp_analyticbridge_metrics as m
WHERE
m.metric = 'ga:pageviews'
AND
m.startdate >= CURDATE()
) as t ON pg.id = t.page_id
LEFT JOIN (
--
-- Nested select returns yesterday's sessions.
--
SELECT
CAST(value as unsigned) as sessions,
page_id
FROM
wp_analyticbridge_metrics as m
WHERE
m.metric = 'ga:pageviews'
AND
m.startdate >= CURDATE() - 1
AND
m.enddate < CURDATE()
) as y ON pg.id = y.page_id
LEFT JOIN wp_posts as pst
ON pst.id = pg.post_id
-- For now, they must be posts.
WHERE pst.post_type = 'post'
ORDER BY weighted_pageviews DESC
LIMIT 8;