USE tewiki_p;

​

-- Most thanked, total

SELECT

    log_title as thanked, COUNT(*) as count

  FROM

    logging

  WHERE

    log_type = "thanks"

  GROUP BY

    log_title

  ORDER BY

    count DESC, log_title;

-- Most thanking, total

SELECT

    actor_name as thanking, COUNT(*) as count

  FROM

    logging, actor_user

  WHERE

    actor_id = log_actor and

    log_type = "thanks"

  GROUP BY

    actor_name

  ORDER BY

    count DESC, actor_name;    

​

-- Most thanked, last 12 months

SELECT

    log_title as thanked_last_year, COUNT(*) as count

  FROM

    logging

  WHERE

    log_type = "thanks" and

    log_timestamp > DATE_SUB(NOW(), INTERVAL 1 YEAR)

  GROUP BY

    log_title

  ORDER BY

    count DESC, log_title;

-- Most thanking, last 12 months

SELECT

    actor_name as thanking_last_year, COUNT(*) as count

  FROM

    logging, actor_user

  WHERE

    actor_id = log_actor and

    log_type = "thanks" and

    log_timestamp > DATE_SUB(NOW(), INTERVAL 1 YEAR)

  GROUP BY

    actor_name

  ORDER BY

    count DESC, actor_name;