SQL
x
use tewiki_p;
SELECT
COUNT(actor_id) as concat(@db_name,active_user_count)
FROM actor
WHERE actor_id in (select rev_actor from revision JOIN actor ON rev_actor=actor_id
JOIN page ON rev_page=page_id
WHERE page_namespace = 0
AND actor_user IS NOT NULL
AND rev_timestamp BETWEEN 20220101000000 AND 20221231235959
and actor_name not like "%bot%"
GROUP BY actor_id
having count(rev_actor)>49);
use tawiki_p;
SELECT
COUNT(actor_id) as active_user_count
FROM actor
WHERE actor_id in (select rev_actor from revision JOIN actor ON rev_actor=actor_id
JOIN page ON rev_page=page_id
WHERE page_namespace = 0
AND actor_user IS NOT NULL
AND rev_timestamp BETWEEN 20220101000000 AND 20221231235959
and actor_name not like "%bot%"
GROUP BY actor_id
having count(rev_actor)>49);
use hiwiki_p;
SELECT
COUNT(actor_id) as active_user_count
FROM actor
WHERE actor_id in (select rev_actor from revision JOIN actor ON rev_actor=actor_id
JOIN page ON rev_page=page_id
WHERE page_namespace = 0
AND actor_user IS NOT NULL
AND rev_timestamp BETWEEN 20220101000000 AND 20221231235959
and actor_name not like "%bot%"
GROUP BY actor_id
having count(rev_actor)>49);
use knwiki_p;
SELECT
COUNT(actor_id) as active_user_count
FROM actor
WHERE actor_id in (select rev_actor from revision JOIN actor ON rev_actor=actor_id
JOIN page ON rev_page=page_id
WHERE page_namespace = 0
AND actor_user IS NOT NULL
AND rev_timestamp BETWEEN 20220101000000 AND 20221231235959
and actor_name not like "%bot%"
GROUP BY actor_id
having count(rev_actor)>49);
use bnwiki_p;
SELECT
COUNT(actor_id) as active_user_count
FROM actor
WHERE actor_id in (select rev_actor from revision JOIN actor ON rev_actor=actor_id
JOIN page ON rev_page=page_id
WHERE page_namespace = 0
AND actor_user IS NOT NULL
AND rev_timestamp BETWEEN 20220101000000 AND 20221231235959
and actor_name not like "%bot%"
GROUP BY actor_id
having count(rev_actor)>49);
use aswiki_p;
SELECT
COUNT(actor_id) as active_user_count
FROM actor
WHERE actor_id in (select rev_actor from revision JOIN actor ON rev_actor=actor_id
JOIN page ON rev_page=page_id
WHERE page_namespace = 0
AND actor_user IS NOT NULL
AND rev_timestamp BETWEEN 20220101000000 AND 20221231235959
and actor_name not like "%bot%"
GROUP BY actor_id
having count(rev_actor)>49);
use mrwiki_p;
SELECT
COUNT(actor_id) as active_user_count
FROM actor
WHERE actor_id in (select rev_actor from revision JOIN actor ON rev_actor=actor_id
JOIN page ON rev_page=page_id
WHERE page_namespace = 0
AND actor_user IS NOT NULL
AND rev_timestamp BETWEEN 20220101000000 AND 20221231235959
and actor_name not like "%bot%"
GROUP BY actor_id
having count(rev_actor)>49);
use mlwiki_p;
SELECT
COUNT(actor_id) as active_user_count
FROM actor
WHERE actor_id in (select rev_actor from revision JOIN actor ON rev_actor=actor_id
JOIN page ON rev_page=page_id
WHERE page_namespace = 0
AND actor_user IS NOT NULL
AND rev_timestamp BETWEEN 20220101000000 AND 20221231235959
and actor_name not like "%bot%"
GROUP BY actor_id
having count(rev_actor)>49);
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.