https://bugzilla.wikimedia.org/show_bug.cgi?id=53485
--- Comment #14 from Alvaro <[email protected]> --- Quim, I have already the queries to get this information. Now we need to format it a report. You have always the last SQL database in: http://korma.wmflabs.org/browser/data/db/ In this case, we need the reviews database so: acs@lenovix:/tmp$ wget http://korma.wmflabs.org/browser/data/db/reviews.mysql.7z acs@lenovix:/tmp$ 7zr x reviews.mysql.7z acs@lenovix:/tmp$ mysqladmin -u root create wikimedia_gerrit acs@lenovix:/tmp$ mysql -u root wikimedia_gerrit < reviews.mysql And now time to play SQL: **** Who is contributing merged code each quarter? acs@lenovix:/tmp$ mysql -u root wikimedia_gerrit SELECT total, name, email, quarter, year FROM (SELECT COUNT(i.id) AS total, upeople_id, p.id, name, email, user_id, QUARTER(submitted_on) as quarter, YEAR(submitted_on) year FROM issues i, people p , people_upeople pup WHERE i.submitted_by=p.id AND pup.people_id=p.id AND status='merged' GROUP BY upeople_id,year,quarter ORDER BY year,quarter,total DESC ) t WHERE total>50; With this query you get a list ordered in time for all quarters of contributors that have merged more than 50 contributions. We can create queries, one for each quarter, in order to get the 25 top "mergers". But with just one query we can get the full picture. And for organizations it is pretty similar. But in this case with this query we get just 60 rows so it is usable without working in specific queries for each quarter. SELECT COUNT(i.id) AS total, c.name, QUARTER(submitted_on) as quarter, YEAR(submitted_on) year FROM issues i, people p , people_upeople pup, acs_cvsanaly_mediawiki_2029_1.upeople_companies upc, acs_cvsanaly_mediawiki_2029_1.companies c WHERE i.submitted_by=p.id AND pup.people_id=p.id AND pup.upeople_id = upc.upeople_id AND upc.company_id = c.id AND status='merged' GROUP BY year, quarter, c.id ORDER BY year, quarter, total DESC Quim, I have attached to this issue the people-quarters.txt and orgs-quarters.csv with the results of this queries. With this results we can generate HTML tables. -- You are receiving this mail because: You are on the CC list for the bug. _______________________________________________ Wikibugs-l mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
