Statistics V2

Updated over 4 years ago by Thomas Ochman
User statistics
First draft:


User

Active during last week
User.where("updated_at > ?", Time.now-7.days).count
=> 57

Active during last month:

User.where("updated_at > ?", Time.now-30.days).count
=> 439



Be advised that we have made changes to the `users` table and are using  `:country_code` as the column to get the relevant information.

Countries represented: 
User.distinct.count(:country)
=> 77

Users per country: 
users = User.group(:country).count
=> {nil=>7, "Cambodia"=>1, "Guatemala"=>1, "Turkey"=>5, "Germany"=>14, "Madagascar"=>1, "France"=>4, "Cyprus"=>1, "Colombia"=>4, "Japan"=>1, "Vietnam"=>2, "Kuwait"=>2, "Jamaica"=>1, "Nepal"=>2, "Denmark"=>1, "Lithuania"=>2, "Bangladesh"=>1, "Netherlands"=>5, "Nigeria"=>2, "Dominican Republic"=>1, "Egypt"=>3, "Australia"=>11, "United States"=>134, "Slovakia"=>1, "Argentina"=>2, "Russian Federation"=>19, "Czech Republic"=>1, "Ghana"=>1, "Ethiopia"=>2, "Azerbaijan"=>1, "Brazil"=>15, "Israel"=>4, "New Zealand"=>1, "Mozambique"=>1, "Mexico"=>2, "Finland"=>1, "Taiwan"=>3, "Sri Lanka"=>1, "Korea, Republic of"=>2, "Thailand"=>2, "Estonia"=>5, "Peru"=>1, "Venezuela"=>2, "Algeria"=>3, "Kyrgyzstan"=>1, "Malaysia"=>1, "Spain"=>18, "Ukraine"=>6, "Serbia"=>4, "Belarus"=>1, "Ireland"=>3, "Haiti"=>1, "Hong Kong"=>1, "Portugal"=>4, "Romania"=>1, "Puerto Rico"=>1, "Trinidad and Tobago"=>1, "Canada"=>15, "China"=>2, "Panama"=>1, "South Africa"=>4, "Kenya"=>2, "Poland"=>5, "United Kingdom"=>53, "Luxembourg"=>1, "Sweden"=>4, "Italy"=>5, "Greece"=>9, "India"=>26, "Morocco"=>4, "Philippines"=>3, "Switzerland"=>6, "Indonesia"=>2, "Bolivia"=>4, "Belgium"=>1, "Europe"=>1, "Bulgaria"=>5, "Norway"=>1}

And sorted by value:
Hash[users.sort_by{|k, v| v}.reverse]
=> {"United States"=>134, "United Kingdom"=>53, "India"=>26, "Russian Federation"=>19, "Spain"=>18, "Canada"=>15, "Brazil"=>15, "Germany"=>14, "Australia"=>11, "Greece"=>9, nil=>7, "Ukraine"=>6, "Switzerland"=>6, "Netherlands"=>5, "Estonia"=>5, "Poland"=>5, "Turkey"=>5, "Italy"=>5, "Bulgaria"=>5, "Morocco"=>4, "South Africa"=>4, "Colombia"=>4, "France"=>4, "Sweden"=>4, "Portugal"=>4, "Israel"=>4, "Serbia"=>4, "Bolivia"=>4, "Ireland"=>3, "Taiwan"=>3, "Philippines"=>3, "Egypt"=>3, "Algeria"=>3, "China"=>2, "Indonesia"=>2, "Venezuela"=>2, "Vietnam"=>2, "Kuwait"=>2, "Thailand"=>2, "Korea, Republic of"=>2, "Nepal"=>2, "Lithuania"=>2, "Kenya"=>2, "Mexico"=>2, "Nigeria"=>2, "Argentina"=>2, "Ethiopia"=>2, "Trinidad and Tobago"=>1, "Azerbaijan"=>1, "Hong Kong"=>1, "Ghana"=>1, "Czech Republic"=>1, "Puerto Rico"=>1, "New Zealand"=>1, "Slovakia"=>1, "Romania"=>1, "Kyrgyzstan"=>1, "Panama"=>1, "Dominican Republic"=>1, "Mozambique"=>1, "Finland"=>1, "Bangladesh"=>1, "Haiti"=>1, "Denmark"=>1, "Sri Lanka"=>1, "Jamaica"=>1, "Belarus"=>1, "Peru"=>1, "Japan"=>1, "Luxembourg"=>1, "Cyprus"=>1, "Malaysia"=>1, "Madagascar"=>1, "Belgium"=>1, "Europe"=>1, "Guatemala"=>1, "Cambodia"=>1, "Norway"=>1}

Obs! Note the `nil =>7`

And converted to json:
y Hash[users.sort_by{|k, v| v}.reverse].to_json
--- '{"United States":134,"United Kingdom":53,"India":26,"Russian Federation":19,"Spain":18,"Canada":15,"Brazil":15,"Germany":14,"Australia":11,"Greece":9,"":7,"Ukraine":6,"Switzerland":6,"Netherlands":5,"Estonia":5,"Poland":5,"Turkey":5,"Italy":5,"Bulgaria":5,"Morocco":4,"South
  Africa":4,"Colombia":4,"France":4,"Sweden":4,"Portugal":4,"Israel":4,"Serbia":4,"Bolivia":4,"Ireland":3,"Taiwan":3,"Philippines":3,"Egypt":3,"Algeria":3,"China":2,"Indonesia":2,"Venezuela":2,"Vietnam":2,"Kuwait":2,"Thailand":2,"Korea, Republic of":2,"Nepal":2,"Lithuania":2,"Kenya":2,"Mexico":2,"Nigeria":2,"Argentina":2,"Ethiopia":2,"Trinidad and Tobago":1,"Azerbaijan":1,"Hong Kong":1,"Ghana":1,"Czech Republic":1,"Puerto
  Rico":1,"New Zealand":1,"Slovakia":1,"Romania":1,"Kyrgyzstan":1,"Panama":1,"Dominican
  Republic":1,"Mozambique":1,"Finland":1,"Bangladesh":1,"Haiti":1,"Denmark":1,"Sri
  Lanka":1,"Jamaica":1,"Belarus":1,"Peru":1,"Japan":1,"Luxembourg":1,"Cyprus":1,"Malaysia":1,"Madagascar":1,"Belgium":1,"Europe":1,"Guatemala":1,"Cambodia":1,"Norway":1}'

Here is the final method on the `User`-model

def self.map_data
users = User.group(:country_code).count
#if there are users that have not been geocoded we will end up with a 'nil' key and we need to get rid of that.
 clean = proc{ |k,v| !k.nil? ? Hash === v ? v.delete_if(&clean) : false : true }
users.delete_if(&clean)
users.to_json
end

EventInstance (WIP)

EventInstance.pluck(:updated_at, :created_at)

Hours of Scrum 

EventInstance.where(category: 'Scrum').map(&:duration).sum

Hours of PP sessions

EventInstance.where(category: 'PairProgramming').map(&:duration).sum
Last month: EventInstance.where(category: 'PairProgramming').where(created_at: 1.month.ago..Date.today).map(&:duration).sum
Last week: EventInstance.where(category: 'PairProgramming').where(created_at: 1.week.ago..Date.today).map(&:duration).sum
Specific project(assuming that wso = Project.where...
wso.event_instances.map(&:duration).sum.to_i/60 <= returns total duration in minutes
wso.event_instances.where(category: 'Scrum').map(&:duration).sum.to_i/60 <= will always equal 0. There are no scrums for projects ;-)


Stats for user

Let's see if we can get the total minutes for a users total time in scrum. Sure we can. event_instances is a method we can call on the User instance...

Assuming that `thomas` is `User.find_by(email:....)
thomas.event_instances.where(category: 'Scrum').map(&:duration).sum.to_i/60 <= could be used for Karma calculations
thomas.event_instances.count

But these are 'only' the EventInstances that `thomas`has created. How about the ones he has takin part in but that ware created by somebody else. Here we have to use the `youtube_user_name`attribute and that's where we might run into trouble. Hmm... actually, we can't get into any trouble since we are not querying the YT API in this. We have all the data stored in our own db. 

First we need to grab ALL EventInstance where `thomas` is listed as a participant. We can use some SQL to get those results in a one liner:

EventInstance.where('participants LIKE ?', "%displayName: #{thomas.youtube_user_name}%")
and by adding:
.sum.to_i/60 <= we get the total minutes

Actually... this is ALL we need to do. Oh... good. ;-)

So, to compare:

irb(main):048:0> thomas.event_instances.count
=> 47
irb(main):049:0> EventInstance.where('participants LIKE ?', "%displayName: #{thomas.youtube_user_name}%").count
=> 166
Am I on the right track here?
Revisions

comments powered by Disqus