Last active
September 9, 2017 21:29
-
-
Save jfly/286f93b43f00f32b971bbd0560779399 to your computer and use it in GitHub Desktop.
Revisions
-
jfly revised this gist
Sep 9, 2017 . 1 changed file with 6 additions and 0 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -1,5 +1,6 @@ ## 2016 ``` irb(main):016:0> year=2016; id_counts = ActiveRecord::Base.connection.execute("SELECT Results.personId, COUNT(DISTINCT Competitions.id) FROM Results INNER JOIN Competitions ON Competitions.id = Results.competitionId WHERE (YEAR(Competitions.start_date)=#{year}) GROUP BY Results.personId").to_a; (1..100).each { |comp_count| people_count = id_counts.sort_by { |id_count| id_count[1] }.select { |id_count| id_count[1] >= comp_count }.length; puts "#{people_count} person(s) went to >= #{comp_count} comps in #{year}"; break if people_count == 0 } (6745.6ms) SELECT Results.personId, COUNT(DISTINCT Competitions.id) FROM Results INNER JOIN Competitions ON Competitions.id = Results.competitionId WHERE (YEAR(Competitions.start_date)=2016) GROUP BY Results.personId 27630 person(s) went to >= 1 comps in 2016 @@ -40,9 +41,11 @@ irb(main):016:0> year=2016; id_counts = ActiveRecord::Base.connection.execute("S 1 person(s) went to >= 36 comps in 2016 1 person(s) went to >= 37 comps in 2016 0 person(s) went to >= 38 comps in 2016 ``` ## 2015 ``` irb(main):015:0> year=2015; id_counts = ActiveRecord::Base.connection.execute("SELECT Results.personId, COUNT(DISTINCT Competitions.id) FROM Results INNER JOIN Competitions ON Competitions.id = Results.competitionId WHERE (YEAR(Competitions.start_date)=#{year}) GROUP BY Results.personId").to_a; (1..100).each { |comp_count| people_count = id_counts.sort_by { |id_count| id_count[1] }.select { |id_count| id_count[1] >= comp_count }.length; puts "#{people_count} person(s) went to >= #{comp_count} comps in #{year}"; break if people_count == 0 } (4211.7ms) SELECT Results.personId, COUNT(DISTINCT Competitions.id) FROM Results INNER JOIN Competitions ON Competitions.id = Results.competitionId WHERE (YEAR(Competitions.start_date)=2015) GROUP BY Results.personId 19420 person(s) went to >= 1 comps in 2015 @@ -79,9 +82,11 @@ irb(main):015:0> year=2015; id_counts = ActiveRecord::Base.connection.execute("S 1 person(s) went to >= 32 comps in 2015 0 person(s) went to >= 33 comps in 2015 => nil ``` ## 2014 ``` irb(main):014:0> year=2014; id_counts = ActiveRecord::Base.connection.execute("SELECT Results.personId, COUNT(DISTINCT Competitions.id) FROM Results INNER JOIN Competitions ON Competitions.id = Results.competitionId WHERE (YEAR(Competitions.start_date)=#{year}) GROUP BY Results.personId").to_a; (1..100).each { |comp_count| people_count = id_counts.sort_by { |id_count| id_count[1] }.select { |id_count| id_count[1] >= comp_count }.length; puts "#{people_count} person(s) went to >= #{comp_count} comps in #{year}"; break if people_count == 0 } (2773.0ms) SELECT Results.personId, COUNT(DISTINCT Competitions.id) FROM Results INNER JOIN Competitions ON Competitions.id = Results.competitionId WHERE (YEAR(Competitions.start_date)=2014) GROUP BY Results.personId 14609 person(s) went to >= 1 comps in 2014 @@ -113,3 +118,4 @@ irb(main):014:0> year=2014; id_counts = ActiveRecord::Base.connection.execute("S 1 person(s) went to >= 27 comps in 2014 0 person(s) went to >= 28 comps in 2014 => nil ``` -
Sep 9, 2017 .There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,115 @@ ## 2016 irb(main):016:0> year=2016; id_counts = ActiveRecord::Base.connection.execute("SELECT Results.personId, COUNT(DISTINCT Competitions.id) FROM Results INNER JOIN Competitions ON Competitions.id = Results.competitionId WHERE (YEAR(Competitions.start_date)=#{year}) GROUP BY Results.personId").to_a; (1..100).each { |comp_count| people_count = id_counts.sort_by { |id_count| id_count[1] }.select { |id_count| id_count[1] >= comp_count }.length; puts "#{people_count} person(s) went to >= #{comp_count} comps in #{year}"; break if people_count == 0 } (6745.6ms) SELECT Results.personId, COUNT(DISTINCT Competitions.id) FROM Results INNER JOIN Competitions ON Competitions.id = Results.competitionId WHERE (YEAR(Competitions.start_date)=2016) GROUP BY Results.personId 27630 person(s) went to >= 1 comps in 2016 9900 person(s) went to >= 2 comps in 2016 5080 person(s) went to >= 3 comps in 2016 2907 person(s) went to >= 4 comps in 2016 1798 person(s) went to >= 5 comps in 2016 1213 person(s) went to >= 6 comps in 2016 835 person(s) went to >= 7 comps in 2016 590 person(s) went to >= 8 comps in 2016 430 person(s) went to >= 9 comps in 2016 324 person(s) went to >= 10 comps in 2016 243 person(s) went to >= 11 comps in 2016 183 person(s) went to >= 12 comps in 2016 134 person(s) went to >= 13 comps in 2016 99 person(s) went to >= 14 comps in 2016 77 person(s) went to >= 15 comps in 2016 59 person(s) went to >= 16 comps in 2016 47 person(s) went to >= 17 comps in 2016 26 person(s) went to >= 18 comps in 2016 21 person(s) went to >= 19 comps in 2016 19 person(s) went to >= 20 comps in 2016 11 person(s) went to >= 21 comps in 2016 10 person(s) went to >= 22 comps in 2016 5 person(s) went to >= 23 comps in 2016 4 person(s) went to >= 24 comps in 2016 3 person(s) went to >= 25 comps in 2016 2 person(s) went to >= 26 comps in 2016 2 person(s) went to >= 27 comps in 2016 2 person(s) went to >= 28 comps in 2016 2 person(s) went to >= 29 comps in 2016 1 person(s) went to >= 30 comps in 2016 1 person(s) went to >= 31 comps in 2016 1 person(s) went to >= 32 comps in 2016 1 person(s) went to >= 33 comps in 2016 1 person(s) went to >= 34 comps in 2016 1 person(s) went to >= 35 comps in 2016 1 person(s) went to >= 36 comps in 2016 1 person(s) went to >= 37 comps in 2016 0 person(s) went to >= 38 comps in 2016 ## 2015 irb(main):015:0> year=2015; id_counts = ActiveRecord::Base.connection.execute("SELECT Results.personId, COUNT(DISTINCT Competitions.id) FROM Results INNER JOIN Competitions ON Competitions.id = Results.competitionId WHERE (YEAR(Competitions.start_date)=#{year}) GROUP BY Results.personId").to_a; (1..100).each { |comp_count| people_count = id_counts.sort_by { |id_count| id_count[1] }.select { |id_count| id_count[1] >= comp_count }.length; puts "#{people_count} person(s) went to >= #{comp_count} comps in #{year}"; break if people_count == 0 } (4211.7ms) SELECT Results.personId, COUNT(DISTINCT Competitions.id) FROM Results INNER JOIN Competitions ON Competitions.id = Results.competitionId WHERE (YEAR(Competitions.start_date)=2015) GROUP BY Results.personId 19420 person(s) went to >= 1 comps in 2015 7109 person(s) went to >= 2 comps in 2015 3731 person(s) went to >= 3 comps in 2015 2248 person(s) went to >= 4 comps in 2015 1464 person(s) went to >= 5 comps in 2015 971 person(s) went to >= 6 comps in 2015 680 person(s) went to >= 7 comps in 2015 466 person(s) went to >= 8 comps in 2015 334 person(s) went to >= 9 comps in 2015 231 person(s) went to >= 10 comps in 2015 155 person(s) went to >= 11 comps in 2015 99 person(s) went to >= 12 comps in 2015 67 person(s) went to >= 13 comps in 2015 52 person(s) went to >= 14 comps in 2015 37 person(s) went to >= 15 comps in 2015 26 person(s) went to >= 16 comps in 2015 19 person(s) went to >= 17 comps in 2015 14 person(s) went to >= 18 comps in 2015 12 person(s) went to >= 19 comps in 2015 6 person(s) went to >= 20 comps in 2015 5 person(s) went to >= 21 comps in 2015 4 person(s) went to >= 22 comps in 2015 3 person(s) went to >= 23 comps in 2015 2 person(s) went to >= 24 comps in 2015 2 person(s) went to >= 25 comps in 2015 1 person(s) went to >= 26 comps in 2015 1 person(s) went to >= 27 comps in 2015 1 person(s) went to >= 28 comps in 2015 1 person(s) went to >= 29 comps in 2015 1 person(s) went to >= 30 comps in 2015 1 person(s) went to >= 31 comps in 2015 1 person(s) went to >= 32 comps in 2015 0 person(s) went to >= 33 comps in 2015 => nil ## 2014 irb(main):014:0> year=2014; id_counts = ActiveRecord::Base.connection.execute("SELECT Results.personId, COUNT(DISTINCT Competitions.id) FROM Results INNER JOIN Competitions ON Competitions.id = Results.competitionId WHERE (YEAR(Competitions.start_date)=#{year}) GROUP BY Results.personId").to_a; (1..100).each { |comp_count| people_count = id_counts.sort_by { |id_count| id_count[1] }.select { |id_count| id_count[1] >= comp_count }.length; puts "#{people_count} person(s) went to >= #{comp_count} comps in #{year}"; break if people_count == 0 } (2773.0ms) SELECT Results.personId, COUNT(DISTINCT Competitions.id) FROM Results INNER JOIN Competitions ON Competitions.id = Results.competitionId WHERE (YEAR(Competitions.start_date)=2014) GROUP BY Results.personId 14609 person(s) went to >= 1 comps in 2014 5391 person(s) went to >= 2 comps in 2014 2855 person(s) went to >= 3 comps in 2014 1734 person(s) went to >= 4 comps in 2014 1119 person(s) went to >= 5 comps in 2014 740 person(s) went to >= 6 comps in 2014 520 person(s) went to >= 7 comps in 2014 356 person(s) went to >= 8 comps in 2014 236 person(s) went to >= 9 comps in 2014 167 person(s) went to >= 10 comps in 2014 118 person(s) went to >= 11 comps in 2014 91 person(s) went to >= 12 comps in 2014 61 person(s) went to >= 13 comps in 2014 50 person(s) went to >= 14 comps in 2014 36 person(s) went to >= 15 comps in 2014 29 person(s) went to >= 16 comps in 2014 17 person(s) went to >= 17 comps in 2014 8 person(s) went to >= 18 comps in 2014 5 person(s) went to >= 19 comps in 2014 5 person(s) went to >= 20 comps in 2014 5 person(s) went to >= 21 comps in 2014 5 person(s) went to >= 22 comps in 2014 4 person(s) went to >= 23 comps in 2014 3 person(s) went to >= 24 comps in 2014 1 person(s) went to >= 25 comps in 2014 1 person(s) went to >= 26 comps in 2014 1 person(s) went to >= 27 comps in 2014 0 person(s) went to >= 28 comps in 2014 => nil