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.