I'm trying to get the most popular pages by language & site for Sitecore 8.0.
This one I could find, is for an older version and the table names have changed since then.
string query = string.Format(
"SELECT TOP {0} ItemId, count(*) as cnt, MultiSite
FROM Pages
INNER JOIN Visits ON Pages.VisitId = Visits.VisitId
WHERE DateTime > DATEADD(DAY, -{1}, GETDATE()) AND MultiSite = '{2}'
GROUP BY ItemId, MultiSite
ORDER BY cnt DESC",
numberOfItems, timespan, siteName);
Also in the 8.0 DB, the "Items" table does not have any mappings for Site/language. Could someone please help me with the right query.
Fact_PageViewsByLanguage
- that might be a good place to start.