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.

Checking query status...