WLM-2017 New Users

SQL

WITH
cat_filter AS (
  SELECT rev_id, rev_actor
  FROM categorylinks
  INNER JOIN revision ON cl_from = rev_page
  WHERE cl_to = "Images_from_Wiki_Loves_Monuments_2017" AND cl_type = "file" AND rev_parent_id = 0),

newuser_filter AS (
  SELECT DISTINCT actor_user, actor_id
  FROM cat_filter
  INNER JOIN actor ON actor_id = rev_actor AND rev_actor IS NOT NULL
  INNER JOIN user ON actor_user = user_id AND user_id IS NOT NULL
  WHERE user_registration BETWEEN "20170901000000" and "20170930235959"),
  
edit_filter AS (
  SELECT user_name, user_registration, user_id, rev_timestamp, rev_id
  FROM newuser_filter
  LEFT JOIN user ON actor_user = user_id
  LEFT JOIN revision ON actor_id = rev_actor AND rev_actor IS NOT NULL),
  
notbot_filter AS (
  SELECT *
  FROM edit_filter
  LEFT JOIN user_groups on ug_user = user_id
  WHERE ug_group NOT IN 'bot' OR ug_group IS NULL),
  
month_filter AS (
  SELECT user_name, YEAR(user_registration) AS year, COUNT(rev_timestamp), user_registration, ug_group, user_id
  FROM notbot_filter
  GROUP BY user_name)

SELECT user_name, user_id, year AS cohort
FROM month_filter
SQL

Checking query status...
