[bug] cannot find github id under https://all.devstats.cncf.io/

kfaseela commented 11 months ago

I am an Istio contributor, however I cannot find my github Id under users when I filter in https://all.devstats.cncf.io/d/48/users-statistics-by-repository-group But you would see my github id under https://istio.devstats.cncf.io/d/66/developer-activity-counts-by-companies?orgId=1 I think the same issue applies for many Istio contributors, so wondering if there was any integration that is missing for Istio project, while it was migrated to CNCF.

kfaseela commented 11 months ago

cc @lukaszgryglicki

lukaszgryglicki commented 11 months ago

I will TAL on Friday, but in the All CNCF instance we are limiting users (cutting some from the long tail with lowest contributions - so this can be a case, but I'll do a detailed investigation on Friday and update here).

lukaszgryglicki commented 11 months ago

This is not an issue, we are only showing some top users in the drop-down (In All CNCF there are so many contributors that you just don't get on that list).

Now explanation: In this Istio dashboard your user kfaseela is shown with 1686 contributions during the last decade, see:

That user is also visible in the second dashboard's drop-down list (I've selected the biggest aggregation period (1 year) and that user has 679 contributions:

On All CNCF dashboard you can see that the user at the botton has over 5K contributions (there is a limitation on haw many values there can be in the drop-down selector)

I will now also investiagte data in the databases manually, and if I find any issue I'll fix and update here.

kfaseela commented 11 months ago

Thanks for looking into this! What is the way to check for devstats score at https://all.devstats.cncf.io/ then? The above link was the suggestion given to check the score, so if it filters out only top users, what would be the right mechanism to check the score for other users? I was thinking if I filter Repository to Istio atleast, I can see my github handle

lukaszgryglicki commented 11 months ago

Istio DB check:

istio=# select count(*) from gha_events where actor_id in (select id from gha_actors where login = 'kfaseela') and type in ('PushEvent', 'PullRequestEvent', 'IssuesEvent', 'PullRequestReviewEvent', 'CommitCommentEvent', 'IssueCommentEvent', 'PullRequestReviewCommentEvent');
 count 
-------
  1684
(1 row)

We use the following metric to get top 255 users for the drop-down:

select
  i.name
from (
  select
    -- string_agg(sub.name, ',') from (
    0 as ord,
    sub.name as name
  from (
    select dup_actor_login as name,
      count(distinct id) as ecnt
    from
      gha_events
    where
      type in (
        'PullRequestReviewCommentEvent', 'PushEvent', 'PullRequestEvent',
        'IssuesEvent', 'IssueCommentEvent', 'CommitCommentEvent', 'PullRequestReviewEvent'
      )
      and created_at > now() - '3 months'::interval
      and (lower(dup_actor_login) not like all(array['claassistant', 'containersshbuilder', 'wasmcloud-automation', 'fossabot', 'facebook-github-whois-bot-0', 'knative-automation', 'covbot', 'cdk8s-automation', 'github-action-benchmark', 'goreleaserbot', 'imgbotapp', 'backstage-service', 'openssl-machine', 'sizebot', 'dependabot', 'cncf-ci', 'poiana', 'svcbot-qecnsdp', 'nsmbot', 'ti-srebot', 'cf-buildpacks-eng', 'bosh-ci-push-pull', 'gprasath', 'zephyr-github', 'zephyrbot', 'strimzi-ci', 'athenabot', 'k8s-reviewable', 'codecov-io', 'grpc-testing', 'k8s-teamcity-mesosphere', 'angular-builds', 'devstats-sync', 'googlebot', 'hibernate-ci', 'coveralls', 'rktbot', 'coreosbot', 'web-flow', 'prometheus-roobot', 'cncf-bot', 'kernelprbot', 'istio-testing', 'spinnakerbot', 'pikbot', 'spinnaker-release', 'golangcibot', 'opencontrail-ci-admin', 'titanium-octobot', 'asfgit', 'appveyorbot', 'cadvisorjenkinsbot', 'gitcoinbot', 'katacontainersbot', 'prombot', 'prowbot', 'travis%bot', 'k8s-%', '%-bot', '%-robot', 'bot-%', 'robot-%', '%[bot]%', '%[robot]%', '%-jenkins', 'jenkins-%', '%-ci%bot', '%-testing', 'codecov-%', '%clabot%', '%cla-bot%', '%-gerrit', '%-bot-%', '%envoy-filter-example%', '%cibot', '%-ci']))
    group by
      dup_actor_login
    order by
      ecnt desc,
      name asc
    limit 255
    ) sub
  union select 1 as ord,
    'None' as name
) i
order by
  i.ord asc
limit 255
;

This gives your user while in context of istio database:

root@devstats-node-2:~/go/src/github.com/cncf/devstats-k8s-lf/util# k exec -in devstats-prod devstats-postgres-0 -- psql istio < q.sql | grep kfaseela
 kfaseela

When I run the inner query to see your rank and number of contributions (still istio database):

    select
      row_number() over (order by count(distinct id) desc, dup_actor_login asc) as rank,
      dup_actor_login as name,
      count(distinct id) as ecnt
    from
      gha_events
    where
      type in (
        'PullRequestReviewCommentEvent', 'PushEvent', 'PullRequestEvent',
        'IssuesEvent', 'IssueCommentEvent', 'CommitCommentEvent', 'PullRequestReviewEvent'
      )
      and created_at > now() - '3 months'::interval
      and (lower(dup_actor_login) not like all(array['claassistant', 'containersshbuilder', 'wasmcloud-automation', 'fossabot', 'facebook-github-whois-bot-0', 'knative-automation', 'covbot', 'cdk8s-automation', 'github-action-benchmark', 'goreleaserbot', 'imgbotapp', 'backsta
    group by
      dup_actor_login
    order by
      ecnt desc,
      name asc
    limit 255
;

I'm getting:

root@devstats-node-2:~/go/src/github.com/cncf/devstats-k8s-lf/util# k exec -in devstats-prod devstats-postgres-0 -- psql istio < q.sql | grep kfaseela
   19 | kfaseela                 |  193

This checks users contributions in the last 3 months (so those drop-downs are constantly changing and showing most active contributors from the last quarter).

Your rank is 19th in Istio.

Now doing the same in allprj database (which is for all CNCF projects combined):

allprj=# select count(*) from gha_events where actor_id in (select id from gha_actors where login = 'kfaseela') and type in ('PushEvent', 'PullRequestEvent', 'IssuesEvent', 'PullRequestReviewEvent', 'CommitCommentEvent', 'IssueCommentEvent', 'PullRequestReviewCommentEvent');
 count 
-------
  2017
(1 row)

You have a bit more contributions, probably from some other(s) CNCF project(s).

Now running the same query for top users on the allprj database gives:

root@devstats-node-2:~/go/src/github.com/cncf/devstats-k8s-lf/util# k exec -in devstats-prod devstats-postgres-0 -- psql allprj < q.sql | grep kfaseela
root@devstats-node-2:~/go/src/github.com/cncf/devstats-k8s-lf/util#

Which means you're not in top 255, but I will remove that limit (for the query execution) and see you rank there:

root@devstats-node-2:~/go/src/github.com/cncf/devstats-k8s-lf/util# k exec -in devstats-prod devstats-postgres-0 -- psql allprj < q.sql | grep kfaseela
   835 | kfaseela                          |  203

Your rank is 835th with 203 contributions in the last quarter, you need to have at least 563 to get to that list in context of All CNCF projects combined (this is why each projhect has a separate DevStats instance to track more details):

root@devstats-node-2:~/go/src/github.com/cncf/devstats-k8s-lf/util# k exec -in devstats-prod devstats-postgres-0 -- psql allprj < q.sql
 rank |         name          | ecnt 
------+-----------------------+------
    1 | sbueringer            | 3516
    2 | crenshaw-dev          | 3291
    3 | sftim                 | 3207
    4 | phlax                 | 3140
    5 | howardjohn            | 2897
    6 | innobead              | 2851
    7 | alculquicondor        | 2661
    8 | leecalcote            | 2579
    9 | eddycharly            | 2402
   10 | ItalyPaleAle          | 2398
   11 | yurishkuro            | 2359
   12 | toddbaert             | 2225
   13 | tiagolobocastro       | 2185
   14 | tenzen-y              | 2115
   15 | codeboten             | 2103
   16 | scholzj               | 2102
   17 | dmitryax              | 2002
   18 | trask                 | 1977
   19 | saschagrunert         | 1944
   20 | aojea                 | 1880
   21 | travisn               | 1849
   22 | tnqn                  | 1762
   23 | AkihiroSuda           | 1678
   24 | pellared              | 1649
   25 | stefanprodan          | 1646
   26 | chipzoller            | 1636
   27 | tpantelis             | 1630
   28 | ericvn                | 1613
   29 | kannon92              | 1591
   30 | jonkoops              | 1579
   31 | windsonsea            | 1565
   32 | alban                 | 1558
   33 | ahrtr                 | 1556
   34 | serathius             | 1523
   35 | antoninbas            | 1516
   36 | mateuszrzeszutek      | 1511
   37 | killianmuldoon        | 1484
   38 | chalin                | 1468
   39 | MrAlias               | 1442
   40 | kleinfreund           | 1426
   41 | ppatierno             | 1414
   42 | arkodg                | 1402
   43 | zirain                | 1395
   44 | gaius-qi              | 1382
   45 | hzxuzhonghu           | 1381
   46 | dims                  | 1365
   47 | TylerHelmuth          | 1355
   48 | beeme1mr              | 1349
   49 | cpanato               | 1349
   50 | RainbowMango          | 1324
   51 | mimowo                | 1315
   52 | JorTurFer             | 1282
   53 | atoulme               | 1275
   54 | utpilla               | 1256
   55 | djaglowski            | 1254
   56 | derekbit              | 1251
   57 | realshuting           | 1250
   58 | frouioui              | 1234
   59 | skitt                 | 1229
   60 | viccuad               | 1221
   61 | liggitt               | 1220
   62 | hiddeco               | 1215
   63 | andyzhangx            | 1211
   64 | hydai                 | 1207
   65 | tengqm                | 1189
   66 | pierDipi              | 1184
   67 | joestringer           | 1182
   68 | aanm                  | 1171
   69 | johngmyers            | 1170
   70 | hakman                | 1169
   71 | dwertent              | 1162
   72 | ahus1                 | 1159
   73 | FedeDP                | 1158
   74 | l5io                  | 1143
   75 | pohly                 | 1140
   76 | spowelljr             | 1134
   77 | freben                | 1127
   78 | jack-berg             | 1120
   79 | benjdlambert          | 1116
   80 | flavio                | 1111
   81 | brb                   | 1106
   82 | Kielek                | 1099
   83 | hanxiaop              | 1097
   84 | terrytangyuan         | 1093
   85 | julianwiedmann        | 1087
   86 | mauriciovasquezbernal | 1079
   87 | Rugvip                | 1078
   88 | 0xFelix               | 1067
   89 | Kavindu-Dodan         | 1056
   90 | michi-covalent        | 1054
   91 | dprotaso              | 1046
   92 | shizhMSFT             | 1046
   93 | mx-psi                | 1040
   94 | tklauser              | 1032
   95 | michaelbeaumont       | 1019
   96 | johncowen             | 1009
   97 | pacoxu                | 1009
   98 | brooksmtownsend       |  997
   99 | kapilt                |  994
  100 | natalieparellano      |  990
  101 | SuperQ                |  989
  102 | thockin               |  980
  103 | svrnm                 |  970
  104 | brandond              |  969
  105 | everettraven          |  969
  106 | luolanzone            |  969
  107 | giorio94              |  962
  108 | mboersma              |  942
  109 | phisco                |  939
  110 | qmonnet               |  932
  111 | kfox1111              |  927
  112 | alexzielenski         |  925
  113 | sanposhiho            |  913
  114 | sayboras              |  912
  115 | jon-whit              |  907
  116 | negz                  |  907
  117 | rhamzeh               |  900
  118 | sunjayBhatia          |  891
  119 | RealAnna              |  889
  120 | rootfs                |  889
  121 | andaaron              |  878
  122 | msfussell             |  875
  123 | kkourt                |  873
  124 | laurit                |  871
  125 | adriantam             |  866
  126 | lyarwood              |  864
  127 | ejona86               |  862
  128 | chrischdi             |  857
  129 | wzshiming             |  853
  130 | artursouza            |  851
  131 | sttts                 |  850
  132 | jpkrohling            |  845
  133 | CecileRobertMichon    |  843
  134 | haircommander         |  842
  135 | reyang                |  837
  136 | daipom                |  835
  137 | wbpcode               |  835
  138 | makkes                |  831
  139 | SergeyKanzhelev       |  831
  140 | shlomi-noach          |  831
  141 | vihangm               |  829
  142 | denis-tingaikin       |  824
  143 | neolit123             |  820
  144 | vmuzikar              |  817
  145 | XiShanYongYe-Chang    |  810
  146 | rleungx               |  808
  147 | ibolton336            |  806
  148 | trasc                 |  799
  149 | dfawley               |  794
  150 | EdDev                 |  793
  151 | lukaszgryglicki       |  793
  152 | squeed                |  788
  153 | zroubalik             |  787
  154 | miparnisari           |  781
  155 | wojtek-t              |  781
  156 | yaron2                |  781
  157 | yeya24                |  781
  158 | mattlord              |  776
  159 | yizha1                |  775
  160 | robscott              |  771
  161 | odubajDT              |  770
  162 | connorsmith256        |  769
  163 | Phoenix500526         |  768
  164 | mposolda              |  761
  165 | marcofranssen         |  756
  166 | lahabana              |  755
  167 | furkatgofurov7        |  750
  168 | matthyx               |  747
  169 | hhunter-ms            |  746
  170 | derekcollison         |  738
  171 | Jarema                |  735
  172 | Abhinandan-Purkait    |  731
  173 | christarazi           |  728
  174 | mhofstetter           |  724
  175 | thisthat              |  720
  176 | XinShuYang            |  719
  177 | bart0sh               |  718
  178 | kyessenov             |  717
  179 | pedroigor             |  710
  180 | cartermp              |  706
  181 | zasweq                |  704
  182 | lhy1024               |  699
  183 | alpeb                 |  695
  184 | jvanz                 |  694
  185 | marquiz               |  693
  186 | Cali0707              |  692
  187 | subhamkrai            |  692
  188 | iholder101            |  687
  189 | inteon                |  687
  190 | yanavlasov            |  687
  191 | reachjainrahul        |  685
  192 | jasondellaluce        |  684
  193 | skriss                |  682
  194 | chaunceyjiang         |  681
  195 | khanhtc1202           |  681
  196 | imeoer                |  679
  197 | ctiller               |  678
  198 | xmudrii               |  677
  199 | harshit-gangal        |  674
  200 | jmhbnz                |  669
  201 | sshveta               |  667
  202 | rvolosatovs           |  661
  203 | tuminoid              |  661
  204 | FeynmanZhou           |  657
  205 | chendave              |  656
  206 | incertum              |  656
  207 | fuweid                |  652
  208 | pichlermarc           |  652
  209 | jpbetz                |  648
  210 | carlory               |  643
  211 | Andreagit97           |  641
  212 | joelanford            |  641
  213 | drfloob               |  640
  214 | BenTheElder           |  637
  215 | bacherfl              |  635
  216 | mowies                |  633
  217 | jsturtevant           |  630
  218 | JoshVanL              |  625
  219 | awanlin               |  624
  220 | gildub                |  621
  221 | overvenus             |  618
  222 | dipesh-rawat          |  617
  223 | lmb                   |  613
  224 | wenyingd              |  613
  225 | creydr                |  612
  226 | Cyber-SiKu            |  610
  227 | markdroth             |  610
  228 | xpivarc               |  609
  229 | roidelapluie          |  608
  230 | moolen                |  607
  231 | brianmcarey           |  606
  232 | ffromani              |  606
  233 | ashutosh-narkar       |  605
  234 | shuo-wu               |  605
  235 | MartinForReal         |  604
  236 | bartsmykla            |  602
  237 | Oberon00              |  596
  238 | borkmann              |  595
  239 | jianjuns              |  593
  240 | ameukam               |  591
  241 | sergiitk              |  591
  242 | mtardy                |  584
  243 | ti-mo                 |  584
  244 | fabriziopandini       |  582
  245 | tomkerkhove           |  579
  246 | easwars               |  578
  247 | jaronoff97            |  578
  248 | stianst               |  576
  249 | nunnatsa              |  574
  250 | aryan9600             |  572
  251 | kenhys                |  571
  252 | akrejcir              |  569
  253 | maxsmythe             |  569
  254 | tigrannajaryan        |  569
  255 | Mossaka               |  563
(255 rows)

lukaszgryglicki commented 11 months ago

Thanks for looking into this! What is the way to check for devstats score at https://all.devstats.cncf.io/ then? The above link was the suggestion given to check the score, so if it filters out only top users, what would be the right mechanism to check the score for other users? I was thinking if I filter Repository to Istio atleast, I can see my github handle

please just check istio instance not all.

kfaseela commented 11 months ago

Thanks for looking into this! What is the way to check for devstats score at https://all.devstats.cncf.io/ then? The above link was the suggestion given to check the score, so if it filters out only top users, what would be the right mechanism to check the score for other users? I was thinking if I filter Repository to Istio atleast, I can see my github handle

please just check istio instance not all.

Okay, in that case, everything is working as expected, we can close the issue :)

lukaszgryglicki commented 11 months ago

Also I can see you there in All CNCF when I select Istio as a repository group: https://all.devstats.cncf.io/d/66/developer-activity-counts-by-companies?orgId=1&var-period_name=Last%20decade&var-metric=contributions&var-repogroup_name=Istio&var-country_name=All&var-companies=All

lukaszgryglicki commented 11 months ago

And even here: https://all.devstats.cncf.io/d/66/developer-activity-counts-by-companies?orgId=1&var-period_name=Last%20decade&var-metric=contributions&var-repogroup_name=All&var-country_name=All&var-companies=All (all projects) but you need to go to the 2nd page. Rank 1461th.

lukaszgryglicki commented 11 months ago

Also here on the 2nd page: https://all.devstats.cncf.io/d/9/developer-activity-counts-by-repository-group-table

You just don't get to the drop-down (only top 255 users) but you are listed in tabular dashboards, so you can assume that your DevStats rank is: