Untitled query #77053

This query is marked as a draft This query has been published by Huji.
SQL

SELECT
  user_name,
  IFNULL(del.cnt, 0),
  IFNULL(res.cnt, 0),
  IFNULL(revdel.cnt, 0),
  IFNULL(logdel.cnt, 0),
  IFNULL(prot.cnt, 0),
  IFNULL(unprot.cnt, 0),
  IFNULL(editprot.cnt, 0),
  IFNULL(block.cnt, 0),
  IFNULL(unblock.cnt, 0),
  IFNULL(editblock.cnt, 0),
  IFNULL(renames.cnt, 0),
  IFNULL(rights.cnt, 0),
  (
    IFNULL(del.cnt, 0) +
    IFNULL(res.cnt, 0) +
    IFNULL(revdel.cnt, 0) +
    IFNULL(logdel.cnt, 0) +
    IFNULL(prot.cnt, 0) +
    IFNULL(unprot.cnt, 0) +
    IFNULL(editprot.cnt, 0) +
    IFNULL(block.cnt, 0) +
    IFNULL(unblock.cnt, 0) +
    IFNULL(editblock.cnt, 0) +
    IFNULL(renames.cnt, 0) +
    IFNULL(rights.cnt, 0)
  ) tot
FROM user JOIN user_groups
  ON user_id = ug_user
  AND ug_group = 'sysop'
LEFT JOIN (
  SELECT
   actor_user,
   COUNT(log_id) cnt
  FROM logging
  JOIN actor
    ON log_actor = actor_id
  WHERE
    log_type = 'delete'
    AND log_action='delete'
    AND log_timestamp > '20230801000000'
  GROUP BY actor_user
) del
  ON user_id = del.actor_user
LEFT JOIN (
  SELECT
    actor_user,
    COUNT(log_id) cnt
  FROM logging
  JOIN actor
    ON log_actor = actor_id
  WHERE
    log_type = 'delete'
    AND log_action='restore'
    AND log_timestamp > '20230801000000'
  GROUP BY actor_user
) res
  ON user_id = res.actor_user
LEFT JOIN (
  SELECT
    actor_user,
    COUNT(log_id) cnt
  FROM logging
  JOIN actor
    ON log_actor = actor_id
  WHERE
    log_type = 'delete'
    AND log_action='revision'
    AND log_timestamp > '20230801000000'
  GROUP BY actor_user
) revdel
  ON user_id = revdel.actor_user
LEFT JOIN (
  SELECT
    actor_user,
    COUNT(log_id) cnt
  FROM logging
  JOIN actor
    ON log_actor = actor_id
  WHERE
    log_type = 'delete'
    AND log_action = 'event'
    AND log_timestamp > '20230801000000'
  GROUP BY actor_user
) logdel
  ON user_id = logdel.actor_user
LEFT JOIN (
  SELECT
    actor_user,
    COUNT(log_id) cnt
  FROM logging
  JOIN actor
    ON log_actor = actor_id
  WHERE
    log_type = 'protect'
    AND log_action = 'protect'
    AND log_timestamp > '20230801000000'
  GROUP BY actor_user
) prot
  ON user_id = prot.actor_user
LEFT JOIN (
  SELECT
    actor_user,
    COUNT(log_id) cnt
  FROM logging
  JOIN actor
    ON log_actor = actor_id
  WHERE
    log_type = 'protect'
    AND log_action = 'unprotect'
    AND log_timestamp > '20230801000000'
  GROUP BY actor_user
) unprot
  ON user_id = unprot.actor_user
LEFT JOIN (
  SELECT
    actor_user,
    COUNT(log_id) cnt
  FROM logging
  JOIN actor
    ON log_actor = actor_id
  WHERE
    log_type = 'protect'
    AND log_action = 'modify'
    AND log_timestamp > '20230801000000'
  GROUP BY actor_user
) editprot
  ON user_id = editprot.actor_user
LEFT JOIN (
  SELECT
    actor_user,
    COUNT(log_id) cnt
  FROM logging
  JOIN actor
    ON log_actor = actor_id
  WHERE
    log_type = 'block'
    AND log_action = 'block'
    AND log_timestamp > '20230801000000'
  GROUP BY actor_user
) block
  ON user_id = block.actor_user
LEFT JOIN (
  SELECT
    actor_user,
    COUNT(log_id) cnt
  FROM logging
  JOIN actor
    ON log_actor = actor_id
  WHERE
    log_type = 'block'
    AND log_action = 'unblock'
    AND log_timestamp > '20230801000000'
  GROUP BY actor_user
) unblock
  ON user_id = unblock.actor_user
LEFT JOIN (
  SELECT
    actor_user,
    COUNT(log_id) cnt
  FROM logging
  JOIN actor
    ON log_actor = actor_id
  WHERE
    log_type = 'block'
    AND log_action = 'reblock'
    AND log_timestamp > '20230801000000'
  GROUP BY actor_user
) editblock
  ON user_id = editblock.actor_user
LEFT JOIN (
  SELECT
    actor_user,
    COUNT(log_id) cnt
  FROM logging
  JOIN actor
    ON log_actor = actor_id
  WHERE log_type = 'renameuser'
    AND log_timestamp > '20230801000000'
  GROUP BY actor_user
) renames
  ON user_id = renames.actor_user
LEFT JOIN (
  SELECT
    actor_user,
    COUNT(log_id) cnt
  FROM logging
  JOIN actor
    ON log_actor = actor_id
  WHERE
    log_type = 'rights'
    AND log_timestamp > '20230801000000'
  AND log_action = 'rights'
  GROUP BY actor_user
) rights
  ON user_id = rights.actor_user
ORDER BY tot DESC
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
