Pageviews for W:PM and W pages with external links¶

wpm_query = """
SELECT access_method, SUM(view_count) AS total_pageviews, SUM(view_count)/{} AS daily_average_pageviews
FROM wmf.pageview_hourly
WHERE project = 'en.wikipedia'
AND page_id IN
    (SELECT DISTINCT page_id 
    FROM ryanmax.population_wpm_pages_with_extlinks)
AND agent_type = 'user'
AND to_date(CONCAT(year,'-',month,'-',day)) >= '{}'
AND to_date(CONCAT(year,'-',month,'-',day)) <= '{}'
GROUP BY access_method
"""
wpm_pageviews = spark.sql(wpm_query.format(days_in_study, start_date_string, end_date_string))
wpm_pageviews_rdd = wpm_pageviews.rdd
wpm_pageviews_df = sqlContext.createDataFrame(wpm_pageviews_rdd)
wpm_pageviews_pandas = wpm_pageviews_df.toPandas()

wpm_pageviews_pandas

w_query = """
SELECT access_method, SUM(view_count) AS total_pageviews, SUM(view_count)/{} AS daily_average_pageviews
FROM wmf.pageview_hourly
WHERE project = 'en.wikipedia'
AND page_id IN
    (SELECT DISTINCT page_id 
    FROM ryanmax.population_w_pages_with_extlinks)
AND agent_type = 'user'
AND to_date(CONCAT(year,'-',month,'-',day)) >= '{}'
AND to_date(CONCAT(year,'-',month,'-',day)) <= '{}'
GROUP BY access_method
"""
w_pageviews = spark.sql(w_query.format(days_in_study,start_date_string, end_date_string))
w_pageviews_rdd = w_pageviews.rdd
w_pageviews_df = sqlContext.createDataFrame(w_pageviews_rdd)
w_pageviews_pandas = w_pageviews_df.toPandas()

w_pageviews_pandas

# most visited WP:M pages with external links
top_wpm_query = """
SELECT pageview_hourly.page_id, population_page_titles_20190420.page_title, SUM(pageview_hourly.view_count) AS total_pageviews, SUM(pageview_hourly.view_count)/{} AS daily_average_pageviews
FROM wmf.pageview_hourly
LEFT JOIN ryanmax.population_page_titles_20190420 ON pageview_hourly.page_id = population_page_titles_20190420.page_id
WHERE project = 'en.wikipedia'
AND pageview_hourly.page_id IN
    (SELECT DISTINCT page_id 
    FROM ryanmax.population_wpm_pages_with_extlinks)
AND agent_type = 'user'
AND to_date(CONCAT(year,'-',month,'-',day)) >= '{}'
AND to_date(CONCAT(year,'-',month,'-',day)) <= '{}'
GROUP BY pageview_hourly.page_id, population_page_titles_20190420.page_title 
ORDER BY total_pageviews DESC
LIMIT 50
"""

top_wpm = spark.sql(top_wpm_query.format(days_in_study,start_date_string, end_date_string))
top_wpm.limit(50).toPandas()