@Tbayer : Would you mind help us again this year?

Useful link, that shows per-month stats: https://tools.wmflabs.org/topviews/?project=zh.wikipedia.org&platform=all-access&date=last-month&excludes=

In T183903#3866690, @Framawiki wrote:

Useful link, that shows per-month stats: https://tools.wmflabs.org/topviews/?project=zh.wikipedia.org&platform=all-access&date=last-month&excludes=

Yes, thank you, that's a useful link, but I'm not sure that I can get the annual data by it or not. If some articles were hit in one month but cool down soon, could I get it's viewing count during cold month?

In T183903#3866690, @Framawiki wrote:

Useful link, that shows per-month stats: https://tools.wmflabs.org/topviews/?project=zh.wikipedia.org&platform=all-access&date=last-month&excludes=

Yes, but as @Reke pointed out, this doesn't directly yield the yearly top 10. T154446: Provide a yearly "Data type" option for topviews was filed on this occasion a year ago, but is still open (CC @MusikAnimal ).

EDIT: Made a bunch of mistakes in my query here, fixed in T183903#4824489

In T183903#4818567, @Milimetric wrote:

quickly adding an example for all wikis, in case it's needed in the future (I haven't tested it but I'll edit it if it breaks and you yell at me on IRC :)):

 SELECT CONCAT('https://', project, '.org/wiki/', page_title),
        SUM(view_count) AS views
   FROM wmf.pageview_hourly
  WHERE year = 2018
    AND project = 'zh.wikipedia'
    AND agent_type = 'user'
  GROUP BY project, page_title
  ORDER BY project, views DESC
  LIMIT 100
;

I'm guessing the project = 'zh.wikipedia' shouldn't be there? :)

The ORDER BY project also seems to not work, for whatever reason: Line 8:9 Invalid table alias or column reference 'project': (possible column names are: _c0, views) (state=42000,code=10004)

This is running without errors:

SELECT CONCAT('https://', project, '.org/wiki/', page_title), SUM(view_count) AS views
FROM wmf.pageview_hourly
WHERE
   year = 2018
   AND agent_type = 'user'
GROUP BY project, page_title
ORDER BY views DESC LIMIT 100;

I'm running this now. Who knows when/if it will finish! ;)

I'm sorry! I messed up, and completely forgot this is a little trickier than a simple query. Here's the real query that's doing this in production for the daily and monthly tops: https://github.com/wikimedia/analytics-refinery/blob/master/oozie/cassandra/daily/pageview_top_articles.hql

And here's a version that would work in theory for yearly. However, I'm pretty sure this will need more resources than we have available on the cluster, and therefore won't ever finish. The 45 minute run was probably because of that limit 100 statement. But I could be wrong, may be worth a shot. I think to improve the performance, you could either join to a table of statistics compiled per-wiki to weed out pages with low view_counts (this would dramatically reduce the number of records in the counted subquery below), or use a different approach of iterating over the data and keeping bloom filter counts:

   WITH counted AS (
         SELECT project,
                page_title,
                SUM(view_count) as views
           FROM pageview_hourly
          WHERE year=2018
            AND agent_type = 'user'
            AND page_title != '-'
            AND month=12
            AND day=12
            AND hour=12
          GROUP BY project, page_title, year, month, day, hour
        ),

        ranked AS (
         SELECT project,
                page_title,
                views,
                rank() OVER (PARTITION BY project ORDER BY views DESC) as ranking
           FROM counted
        )

 SELECT project,
        CONCAT('https://', project, '.org/wiki/', page_title) AS article,
        views
   FROM ranked
  WHERE ranking <= 100
;

(note you should remove the month/day/hour filters if running this for real. For reference it took 90 seconds on 1 hour which if it scaled linearly (which it doesn't) would imply 10 days of processing (definitely do this on a screen if you plan on doing it))

In T183903#4824489, @Milimetric wrote:

...

And here's a version that would work in theory for yearly. However, I'm pretty sure this will need more resources than we have available on the cluster, and therefore won't ever finish. The 45 minute run was probably because of that limit 100 statement. But I could be wrong, may be worth a shot. I think to improve the performance, you could either join to a table of statistics compiled per-wiki to weed out pages with low view_counts (this would dramatically reduce the number of records in the counted subquery below), or use a different approach of iterating over the data and keeping bloom filter counts:

...

(note you should remove the month/day/hour filters if running this for real. For reference it took 90 seconds on 1 hour which if it scaled linearly (which it doesn't) would imply 10 days of processing (definitely do this on a screen if you plan on doing it))

Actually the query worked just fine when applied to (almost) an entire year's worth of data - it took less than an hour to complete even while using the nice queue. This was not too surprising, considering that 1. processing one year of pageview_hourly data is not too demanding per se (judging from previous examples) and 2. the complexity of the second and third query seems to mainly depend on the number of pages present in the dataset, not the number of rows that the first query has to process.

I modified and expanded the above query a bit (P7945 ), in particular with the following:

• including the percentage of mobile view for that pages (considering that it is an often used criterion to weed out anomalies)
• including both the page's name and its (desktop) URL, for easier processing
• ranking pages only by mainspace views (i.e. views tagged with namespace_id =0), to avoid special pages etc. clogging the results
• but still reporting all views for each page (including those where the namespace wasn't logged, which still happens due to some bugs)

The result is a TSV file with several MB in size. (It still needs some manual vetting for each project to weed out anomalies, see e.g. @MusikAnimal 's notes at T211827#4838895 .)
Below is a sample excerpt illustrating the format (top 10 pages for barwiki in November 2018).

@MusikAnimal, would you like to run this sometime in the next few days and distribute the result? (If not, I might be able to do it myself later this week.)

Query for the entire year: