How to create "Most Read" block of a website in Kentico API

Hello,

I think it cannot be done with API. You need to use "custom" query, because this query is taken from the reports Page Views. Here's my function

            private DataSet GetTopVisistedPages(int taskSiteID, string nodeAliasPath, int topN, string culture, string className = "bg.faq")
        {
            QueryParameters qp = new QueryParameters(@"DECLARE @PaveViews TABLE
            (
              PagePath NVARCHAR(500),
              ObjectID INT,
              Pageviews INT,
              Percents DECIMAL(10,2),
              Average INT  
            )

            DECLARE @Sum DECIMAL;
            declare @CMSContextCurrentSiteID int;

            set @CMSContextCurrentSiteID=" + taskSiteID + @";


            SELECT @Sum =   
              SUM(HitsCount) 
              FROM Analytics_Statistics
              INNER JOIN Analytics_YearHits ON Analytics_YearHits.HitsStatisticsID = Analytics_Statistics.StatisticsID
              LEFT JOIN View_CMS_Tree_Joined ON View_CMS_Tree_Joined.NodeID = Analytics_Statistics.StatisticsObjectID  AND StatisticsObjectCulture = DocumentCulture
              WHERE (StatisticsSiteID = @CMSContextCurrentSiteID)
              AND (StatisticsCode='pageviews')


            INSERT INTO @PaveViews (PagePath,ObjectID,PageViews,Percents)
              SELECT TOP 100 StatsWithPath.NodeAliasPath, StatsWithPath.ObjectID, SUM(StatsWithPath.HitsCount) AS PageViews, (SUM(StatsWithPath.HitsCount)/@Sum)*100 AS Percents FROM
              (
                SELECT
                 -- When NodeAliasPath is empty, use value from StatisticsObjectName instead. This could happed for example when document does not longer exist in one of the cultures.
                 -- In this case row can't be joined with View_CMS_Tree_Joined.
                 CASE
                  WHEN NodeAliasPath = '' OR NodeAliasPath IS NULL THEN StatisticsObjectName
                  ELSE NodeAliasPath
                 END AS NodeAliasPath, StatisticsObjectID AS ObjectID, HitsCount 
                 FROM Analytics_Statistics
                 INNER JOIN Analytics_YearHits ON Analytics_YearHits.HitsStatisticsID = Analytics_Statistics.StatisticsID
                 LEFT JOIN View_CMS_Tree_Joined ON Analytics_Statistics.StatisticsObjectID = View_CMS_Tree_Joined.NodeID AND StatisticsObjectCulture = DocumentCulture
                 WHERE (StatisticsSiteID = @CMSContextCurrentSiteID) AND (StatisticsCode = 'pageviews')
              ) AS StatsWithPath
              GROUP BY StatsWithPath.NodeAliasPath, StatsWithPath.ObjectID
              ORDER BY PageViews DESC

             UPDATE @PaveViews SET Average = (SELECT SUM(HitsValue)/SUM(HitsCount) FROM Analytics_YearHits JOIN
                  Analytics_Statistics ON HitsStatisticsID = StatisticsID
                  WHERE StatisticsObjectID = objectID
                    AND StatisticsCode ='avgtimeonpage' AND StatisticsSiteID = @CMSContextCurrentSiteID
                   )

             SELECT top " + topN + @" Pageviews,t.NodeAliasPath,t.DocumentName
               FROM @PaveViews p RIGHT JOIN View_CMS_Tree_Joined t ON p.ObjectID=t.NodeID
               where
               t.NodeAliasPath like N'" + nodeAliasPath + @"/%' and t.DocumentCulture=N'" + culture + @"' and t.ClassName=N'" + className + @"'
               and
               ([DocumentCanBePublished] = 1 AND ([DocumentPublishFrom] IS NULL OR [DocumentPublishFrom] <= GETDATE()) AND ([DocumentPublishTo] IS NULL OR [DocumentPublishTo] >= GETDATE()))
                ORDER BY PageViews DESC", null, QueryTypeEnum.SQLQuery);

            return ConnectionHelper.ExecuteQuery(qp);
        }