Sitecore xDB Trending Content Query

I realized that while I supplied the way to query xDB cloud in a previous post that I never provided the Trending Content Query to use. This blog post will be short but wanted to share these:


SELECT Top 20 FACT_Pageviews.ItemId, SUM(Views) as cnt, Url  
FROM FACT_Pageviews INNER JOIN Items On FACT_Pageviews.ItemId = Items.ItemId  
WHERE  Date > '11/30/2016 1:50:10 PM'  
GROUP BY FACT_Pageviews.ItemId, Url 
ORDER BY cnt desc


There are multiple ways of retrieving the count but I feel that SUM(Views) is the correct way to approach it. I've seen others use count(*) but that is really giving you the number of unique contacts who have viewed the page. SUM(Views) accounts for the fact that a contact may have viewed the page multiple times.


Comments