use commonswiki_p;

select user_name, user_registration,

                (case when user_registration between 20171015000000 and 20181201000000 then 1 else 0 end) newbie,

                count(1) uploads

                from categorylinks

                join page pg on page_id = cl_from

                join image img on img_name = page_title

                join `user` us on img_user = user_id

              where cl_to = 'Images_from_Wiki_Science_Competition_2017_in_Chile'

                and cl_type = 'file'

                group by 1, 2, 3

                order by 4 desc