Fork of
hewiki new users gender nums per year
by Ijon
This query is marked as a draft
This query has been published
by Chicocvenancio.
SQL
x
# Fugly, I know, but I was too lazy to figure out a more elegant query.
SET @years = '2004,2005,2006,2007,2008,2009,2010,2011,2012,2013,2014,2015,2016,2017,2018,';
WHILE (LOCATE(',', @years) > 0)
DO
SET @year = SUBSTRING(ELT(1, @years), 1, 4);
SET @years = SUBSTRING(@years, LOCATE(',',@years) + 1);
select CONCAT("FOR YEAR ",@year, " -->") as year;
select IFNULL(up_value, 'not specified') as gender, count(IFNULL(up_value, 1)) as num
from ptwiki_p.user us
left join ptwiki_p.user_properties ps
on ps.up_user = us.user_id
where (ps.up_property = 'gender' or ps.up_value is null) and us.user_editcount > 1 and us.user_registration BETWEEN DATE_FORMAT(CONCAT(@year, "-01-01"),'%Y%m%d%H%i%s') AND DATE_FORMAT(CONCAT(@year, "-12-31"),'%Y%m%d%H%i%s')
group by up_value;
END WHILE;
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.