বছর অনুসারে বাংলা উইকিপিডিয়ায় নিবন্ধ তৈরি

SQL

use bnwiki_p;
select count(user_name) as users25, 
count(case when user_editcount >= 50 then 1 else null end) as users50,
count(case when user_editcount >= 100 then 1 else null end) as users100,
count(case when user_editcount >= 500 then 1 else null end) as users500,
count(case when user_editcount >= 1000 then 1 else null end) as users1000, 
count(case when user_editcount >= 5000 then 1 else null end) as users5000,
coalesce(year(user_registration), 0) as year
from user u
where user_editcount >= 25
and coalesce (
  (select 'Y' from user_groups ug where ug.ug_user = u.user_id and ug.ug_group = 'bot' limit 1),
  (select 'Y' from user_former_groups ufg where ufg.ufg_user = u.user_id and ufg.ufg_group = 'bot' limit 1),
  'N'
) = 'N'
and user_name not like '%bot'
and user_name not like '%Bot'
and (
  select r.rev_timestamp
  from revision_userindex r
  join actor a on r.rev_actor = a.actor_id
  where a.actor_user = u.user_id 
  and r.rev_timestamp > DATE_SUB(NOW(), INTERVAL 1 YEAR)
  order by r.rev_timestamp desc limit 1
) is not null
group by year
order by year asc;

SQL

Checking query status...