IndicWIkis - New Articles & All New pages counts in a period

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

-- use tewiki_p;
select count(substr(rev_timestamp,1,4))
from
    revision,page,actor
where   page_id=rev_page
    and page_namespace=0
    and page_is_redirect=0
    and actor_id=rev_actor
    and rev_timestamp >= "20230328000000"
	and rev_timestamp <= "20240327000000"
-- 	and ctd_name = "contenttranslation" 
    and rev_parent_id=0;
-- group by substr(rev_timestamp,1,4)
--    order by substr(rev_timestamp,1,4);

select count(substr(rev_timestamp,1,4))
from
    revision,page,actor
where   page_id=rev_page
    and page_namespace=0
    and page_is_redirect=0
    and actor_id=rev_actor
    and rev_timestamp >= "20230328000000"
	and rev_timestamp <= "20240327000000";
-- 	and ctd_name = "contenttranslation" 
--    and rev_parent_id=0
-- group by substr(rev_timestamp,1,4)
--    order by substr(rev_timestamp,1,4);


/* use tawiki_p;
select substr(rev_timestamp,1,4) as ta,count(substr(rev_timestamp,1,4))
from
    change_tag,change_tag_def,revision,page,actor
where ct_tag_id = ctd_id
	and rev_id=ct_rev_id
    and page_id=rev_page
    and page_namespace=0
    and page_is_redirect=0
    and actor_id=rev_actor
-- 	and ctd_name = "contenttranslation" 
    and rev_parent_id=0
group by substr(rev_timestamp,1,4)
    order by substr(rev_timestamp,1,4);

use hiwiki_p;
select substr(rev_timestamp,1,4) as hi,count(substr(rev_timestamp,1,4))
from
    change_tag,change_tag_def,revision,page,actor
where ct_tag_id = ctd_id
	and rev_id=ct_rev_id
    and page_id=rev_page
    and page_namespace=0
    and page_is_redirect=0
    and actor_id=rev_actor
-- 	and ctd_name = "contenttranslation" 
    and rev_parent_id=0
group by substr(rev_timestamp,1,4)
    order by substr(rev_timestamp,1,4);

use mlwiki_p;
select substr(rev_timestamp,1,4) as ml,count(substr(rev_timestamp,1,4))
from
    change_tag,change_tag_def,revision,page,actor
where ct_tag_id = ctd_id
	and rev_id=ct_rev_id
    and page_id=rev_page
    and page_namespace=0
    and page_is_redirect=0
    and actor_id=rev_actor
-- 	and ctd_name = "contenttranslation" 
    and rev_parent_id=0
group by substr(rev_timestamp,1,4)
    order by substr(rev_timestamp,1,4);

use knwiki_p;
select substr(rev_timestamp,1,4) as kn,count(substr(rev_timestamp,1,4))
from
    change_tag,change_tag_def,revision,page,actor
where ct_tag_id = ctd_id
	and rev_id=ct_rev_id
    and page_id=rev_page
    and page_namespace=0
    and page_is_redirect=0
    and actor_id=rev_actor
-- 	and ctd_name = "contenttranslation" 
    and rev_parent_id=0
group by substr(rev_timestamp,1,4)
    order by substr(rev_timestamp,1,4);

use bnwiki_p;
select substr(rev_timestamp,1,4) as bn,count(substr(rev_timestamp,1,4))
from
    change_tag,change_tag_def,revision,page,actor
where ct_tag_id = ctd_id
	and rev_id=ct_rev_id
    and page_id=rev_page
    and page_namespace=0
    and page_is_redirect=0
    and actor_id=rev_actor
-- 	and ctd_name = "contenttranslation" 
    and rev_parent_id=0
group by substr(rev_timestamp,1,4)
    order by substr(rev_timestamp,1,4);

use mrwiki_p;
select substr(rev_timestamp,1,4) as mr,count(substr(rev_timestamp,1,4))
from
    change_tag,change_tag_def,revision,page,actor
where ct_tag_id = ctd_id
	and rev_id=ct_rev_id
    and page_id=rev_page
    and page_namespace=0
    and page_is_redirect=0
    and actor_id=rev_actor
-- 	and ctd_name = "contenttranslation" 
    and rev_parent_id=0
group by substr(rev_timestamp,1,4)
    order by substr(rev_timestamp,1,4);

use pawiki_p;
select substr(rev_timestamp,1,4) as pa,count(substr(rev_timestamp,1,4))
from
    change_tag,change_tag_def,revision,page,actor
where ct_tag_id = ctd_id
	and rev_id=ct_rev_id
    and page_id=rev_page
    and page_namespace=0
    and page_is_redirect=0
    and actor_id=rev_actor
-- 	and ctd_name = "contenttranslation" 
    and rev_parent_id=0
group by substr(rev_timestamp,1,4)
    order by substr(rev_timestamp,1,4);

use guwiki_p;
select substr(rev_timestamp,1,4) as gu,count(substr(rev_timestamp,1,4))
from
    change_tag,change_tag_def,revision,page,actor
where ct_tag_id = ctd_id
	and rev_id=ct_rev_id
    and page_id=rev_page
    and page_namespace=0
    and page_is_redirect=0
    and actor_id=rev_actor
-- 	and ctd_name = "contenttranslation" 
    and rev_parent_id=0
group by substr(rev_timestamp,1,4)
    order by substr(rev_timestamp,1,4);

use urwiki_p;
select substr(rev_timestamp,1,4) as ur,count(substr(rev_timestamp,1,4))
from
    change_tag,change_tag_def,revision,page,actor
where ct_tag_id = ctd_id
	and rev_id=ct_rev_id
    and page_id=rev_page
    and page_namespace=0
    and page_is_redirect=0
    and actor_id=rev_actor
-- 	and ctd_name = "contenttranslation" 
    and rev_parent_id=0
group by substr(rev_timestamp,1,4)
    order by substr(rev_timestamp,1,4);

use orwiki_p;
select substr(rev_timestamp,1,4) as orr,count(substr(rev_timestamp,1,4))
from
    change_tag,change_tag_def,revision,page,actor
where ct_tag_id = ctd_id
	and rev_id=ct_rev_id
    and page_id=rev_page
    and page_namespace=0
    and page_is_redirect=0
    and actor_id=rev_actor
-- 	and ctd_name = "contenttranslation" 
    and rev_parent_id=0
group by substr(rev_timestamp,1,4)
    order by substr(rev_timestamp,1,4);

use aswiki_p;
select substr(rev_timestamp,1,4) as ass,count(substr(rev_timestamp,1,4))
from
    change_tag,change_tag_def,revision,page,actor
where ct_tag_id = ctd_id
	and rev_id=ct_rev_id
    and page_id=rev_page
    and page_namespace=0
    and page_is_redirect=0
    and actor_id=rev_actor
-- 	and ctd_name = "contenttranslation" 
    and rev_parent_id=0
group by substr(rev_timestamp,1,4)
    order by substr(rev_timestamp,1,4);
*/
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...
