SQL
x
WITH base_table AS (
SELECT DISTINCT
rev_id, rev_actor, rev_timestamp, rev_len, rev_parent_id,
page_namespace, page_id, actor_id
FROM revision rev
JOIN page ON rev.rev_page = page.page_id
JOIN actor ON rev.rev_actor = actor.actor_id
WHERE page_namespace IN (0, 1, 10, 11, 14, 15, 828, 829)
ORDER BY rev_id DESC),
edits_table AS (
SELECT page_namespace, COUNT(DISTINCT rev_id) AS edit_count
FROM base_table
GROUP BY page_namespace),
actor_table AS (
SELECT page_namespace, COUNT(DISTINCT actor_id) AS actor_count
FROM base_table
GROUP BY page_namespace),
page_table AS (
SELECT page_namespace, COUNT(DISTINCT page_id) AS pages_count
FROM base_table
GROUP BY page_namespace),
before_grad AS (
SELECT *, YEAR(rev_timestamp) AS year, MONTH(rev_timestamp) AS month
FROM base_table
WHERE MONTH(rev_timestamp) < 8
AND YEAR(rev_timestamp) < 2019),
after_grad AS (
SELECT *, YEAR(rev_timestamp) AS year, MONTH(rev_timestamp) AS month
FROM base_table
WHERE MONTH(rev_timestamp) >= 8
AND YEAR(rev_timestamp) >= 2019),
monthly_grouping_before_grad AS (
SELECT page_namespace, month,
COUNT(DISTINCT rev_id) AS edits_by_month_bg,
COUNT(DISTINCT actor_id) AS editors_by_month_bg
FROM before_grad
GROUP BY page_namespace, month, year),
monthly_grouping_after_grad AS (
SELECT page_namespace, month,
COUNT(DISTINCT rev_id) AS edits_by_month_ag,
COUNT(DISTINCT actor_id) AS editors_by_month_ag
FROM after_grad
GROUP BY page_namespace, month, year),
avg_table_before_grad AS (
SELECT page_namespace,
AVG(edits_by_month_bg) AS avg_monthly_edits_before_grad
AVG(editors_by_month_bg) AS avg_monthly_editors_before_grad
FROM monthly_grouping
GROUP BY page_namespace),
avg_table_after_grad AS (
SELECT page_namespace,
AVG(edits_by_month_ag) AS avg_monthly_edits_after_grad
AVG(editors_by_month_ag) AS avg_monthly_editors_after_grad
FROM monthly_grouping
GROUP BY page_namespace)
SELECT e.page_namespace,
CAST(avg_monthly_editors_before_grad AS INT) AS avg_monthly_edits_before,
CAST(avg_monthly_editors_after_grade AS INT) AS avg_monthly_editors_after
FROM edits_table e
JOIN actor_table a ON a.page_namespace = e.page_namespace
JOIN avg_table_before_grad ON avg_table_before_grad.page_namespace = e.page_namespace
JOIN avg_table_after_grad ON avg_table_after_grad.page_namespace = e.page_namespace
ORDER BY e.edit_count DESC
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.