Get most popular pages in Sitecore

Assume you have installed the DMS package and it is working. In other words, you can see some data generating in the dbo.Pages table from the your analytics database.
(How to make sure DMS is installed correctly:
http://www.sitecore.net/deutsch/Community/Technical-Blogs/John-West-Sitecore-Blog/Posts/2011/08/Troubleshooting-Analytics-is-Disabled-with-the-Sitecore-Customer-Engagement-Platform.aspx)

Now we need to get the most popular pages (i.e., most viewed pages) or its statistics in Sitecore. Of course, it is easier to expose the relavant tables in the database in your solution using entity framework. However it is a really bad idea; because you don't really want to allow any other operations except retrieval.

So it is better to create a view in the database, fast and readonly ensured. In my case, I actually created a view outside any Sitecore default database. The query is something like:

SELECT     TOP (100) PERCENT pa.ItemId, it.TemplateID, pa.PageViewCount
FROM         (SELECT     ItemId, COUNT(*) AS PageViewCount
                       FROM          My_Sitecore_Analytics.dbo.Pages
                       GROUP BY ItemId) AS pa INNER JOIN
                      My_Sitecore_Master.dbo.Items AS it ON pa.ItemId = it.ID
ORDER BY pa.PageViewCount DESC

Then include this view in your solution using entity framework. You can freely query the view using Linq.