USE ptwiki_p;

SELECT avg(HOUR(TIMEDIFF(CAST(r.rev_timestamp AS DATETIME),CAST(rp.rev_timestamp AS DATETIME)))),

       EXTRACT(MONTH

               FROM CAST(r.rev_timestamp AS DATETIME)) AS MES,

       EXTRACT(YEAR

               FROM CAST(r.rev_timestamp AS DATETIME)) AS ANO

FROM revision r

INNER JOIN revision rp ON r.rev_parent_id = rp.rev_id

INNER JOIN revision rpp ON rp.rev_parent_id = rpp.rev_id

WHERE r.rev_timestamp > 20140000000000

  AND r.rev_sha1 = rpp.rev_sha1

  AND r.rev_page IN

    (SELECT DISTINCT cl_from

     FROM categorylinks

     WHERE cl_to = "!Artigos_destacados")

GROUP BY 2,

         3

ORDER BY 3,

         2 ;