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

SQL

x
 
USE bnwiki_p;
-- Re-format wikitable: `(\d+)\.0\|\|(\S[^|]+)`
-- Replace: `$1 || [[User:$2|$2]] `
SELECT actor_name, thank_count
, thank_count_i as thank_count_intial
, (thank_count + thank_count_i) as thank_count_total
FROM
-- next thank
(
  SELECT actor_name, thank_count, IFNULL(thank_count_i, 0) as thank_count_i
  FROM
  (
    SELECT count(*) as thank_count, log_actor
    FROM logging_logindex 
    WHERE log_type = 'Thank' AND log_action = 'Thank'
    GROUP BY log_actor
  ) r_next
  -- add initial thank
  LEFT JOIN
  (
    SELECT count(*) as thank_count_i, log_actor as log_actor_i
    FROM logging_logindex
    WHERE log_type = 'Thank' AND log_action = 'Thank-i'
    GROUP BY log_actor
  ) r_initial ON log_actor_i = log_actor
  -- actor_name  
  LEFT JOIN actor_logging a ON actor_id = log_actor
) top_thank
ORDER BY thank_count DESC, thank_count_i 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.

Checking query status...