Untitled query #4456

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

use ptwiki_p;

SELECT MES, ANO, COUNT(*) AS ativos_editando_escopo FROM
(
SELECT tb_ativos.rev_user, tb_ativos.MES, tb_ativos.ANO FROM (
SELECT rev_user, EXTRACT(MONTH FROM CAST(rev_timestamp AS DATETIME)) AS MES, EXTRACT(YEAR FROM CAST(rev_timestamp AS DATETIME)) AS ANO, COUNT(*) AS edicoes
FROM 
revision 
WHERE
 rev_user NOT IN (SELECT ug_user FROM user_groups WHERE ug_group = 'bot') 
AND
rev_id <> 0
GROUP BY rev_user, MES, ANO 
HAVING edicoes > 0
) tb_ativos,
(
select rev_id, rev_user, EXTRACT(MONTH FROM CAST(rev_timestamp AS DATETIME)) AS MES, EXTRACT(YEAR FROM CAST(rev_timestamp AS DATETIME)) AS ANO from revision where rev_page in (

select page_id from (

select page_id, page_title, page_namespace, cl_timestamp, cl_to from categorylinks, page where page_id = cl_from and cl_type = 'page' and cl_to IN (

SELECT * FROM
(
select page_title from categorylinks, page where page_id = cl_from and cl_to IN (select page_title from categorylinks, page where page_id = cl_from and cl_to IN ('Medicina','Saúde','Farmacologia','Bioquímica','Biomedicina','Farmacologia','Química médica','Embriologia','Especialidades médicas','Fisiologia‎','História da medicina‎','Biologia','Ciências da saúde','Odontologia','Patologia','Doenças‎','Manuais de medicina','Medicina preventiva','Homeopatia','Farmacocinética','Drogas','Saúde pública','Obesidade','Sanitaristas do Brasil','Epidemiologia','Saneamento','Saúde pública','Saúde no Brasil') and cl_type = 'subcat') and cl_type = 'subcat' group by page_title

UNION 

 select page_title from categorylinks, page where page_id = cl_from and cl_to IN ('Medicina','Saúde','Farmacologia','Bioquímica','Biomedicina','Farmacologia','Química médica','Embriologia','Especialidades médicas','Fisiologia‎','História da medicina‎','Biologia','Ciências da saúde','Odontologia','Patologia','Doenças‎','Manuais de medicina','Medicina preventiva','Homeopatia','Farmacocinética','Drogas','Saúde pública','Obesidade','Sanitaristas do Brasil','Epidemiologia','Saneamento','Saúde pública','Saúde no Brasil') and cl_type = 'subcat' group by page_title

UNION
select * from (
select 'Medicina' as page_title UNION select 'Saúde' as page_title UNION select 'Bioquímica' as page_title UNION select 'Biomedicina' as page_title UNION select 'Farmacologia' as page_title UNION select 'Química médica' as page_title UNION select 'Embriologia' as page_title UNION select 'Especialidades médicas' as page_title UNION select 'Fisiologia‎' as page_title UNION select 'História da medicina‎' as page_title UNION select 'Biologia' as page_title UNION select 'Ciências da saúde' as page_title UNION select 'Odontologia' as page_title UNION select 'Patologia' as page_title UNION select 'Doenças‎' as page_title UNION select 'Manuais de medicina' as page_title UNION select 'Medicina preventiva' as page_title UNION select 'Homeopatia' as page_title UNION select 'Farmacocinética' as page_title UNION select 'Drogas' as page_title UNION select 'Obesidade' as page_title UNION select 'Sanitaristas do Brasil' as page_title UNION select 'Epidemiologia' as page_title UNION select 'Saneamento' as page_title UNION select 'Saúde pública' as page_title UNION select 'Saúde no Brasil' as page_title) b

) a group by page_title order by page_title

) and page_namespace in (0,102) group by page_id, page_title, page_namespace, cl_timestamp, cl_to order by  page_title, cl_timestamp
) x
group by page_id order by page_title
)
) tb_edicoes
WHERE 
tb_edicoes.MES = tb_ativos.MES
AND 
tb_edicoes.ANO = tb_ativos.ANO
AND
tb_edicoes.rev_user = tb_ativos.rev_user
GROUP BY rev_user, MES, ANO
) tb_escopo
GROUP BY MES, ANO
ORDER BY ANO DESC, MES 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...
