اردو ویکیپیڈیا کے صارفین اور ان کی ترمیمی ترجیحات

SQL

SELECT actor_name,
  SUM(freq) TotalEdits,
  FORMAT(100 * SUM(
    CASE
      WHEN page_namespace = 0 THEN freq
      ELSE 0
    END
  ) / SUM(freq), 1) ContentEdits,
  FORMAT(100 * SUM(
    CASE
      WHEN page_namespace IN (8, 10) THEN freq
      ELSE 0
    END
  ) / SUM(freq), 1) TechnicalEdits,
  FORMAT(100 * SUM(
    CASE
      WHEN page_namespace IN (4, 5) THEN freq
      ELSE 0
    END
  ) / SUM(freq), 1) ProjectDiscussionEdits,
  FORMAT(100 * SUM(
    CASE
      WHEN page_namespace IN (1, 3, 7, 9, 11, 13) THEN freq
      ELSE 0
    END
  ) / SUM(freq), 1) OtherDiscussionEdits,
  FORMAT(100 * SUM(
    CASE
      WHEN page_namespace NOT IN (0, 1, 3, 4, 5, 7, 8, 9, 10, 11, 13) THEN freq
      ELSE 0
    END
  ) / SUM(freq), 1) AllOtherEdits  
FROM (
  SELECT
    actor_name,
    page_namespace,
    COUNT(*) freq
  FROM revision
  JOIN actor
    ON actor_id = rev_actor
  JOIN page
    ON rev_page = page_id
  WHERE rev_actor IN
  (
    SELECT rev_actor
    FROM revision
    LEFT JOIN user_groups
      ON rev_actor = ug_user
      AND ug_group = 'bot'
    WHERE
      rev_timestamp > DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 DAY), '%Y%m%d000000')
      AND rev_actor <> 0
      AND rev_actor NOT IN ('CommonsDelinker', 'EmausBot', 'InternetArchiveBot', 'پیغامبر خوش آمدید')
      AND actor_name NOT LIKE "%bot" OR "%Bot"
      AND ug_user IS NULL
  )
  GROUP BY
    rev_actor,
    page_namespace
) last30days
GROUP BY actor_name
ORDER BY SUM(freq) DESC
SQL

Checking query status...
