# \[Report\] Stats.gallery development Jan 1 - 31

**URL:** <https://gov.near.org/t/report-stats-gallery-development-jan-1-31/14182>\
**Category:** Fellowships\
**Created:** [January 31, 2022, 1:42pm UTC](https://gov.near.org/t/report-stats-gallery-development-jan-1-31/14182 "2022-01-31T13:42:33Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![Bexanderthebex](https://dub1.discourse-cdn.com/flex005/user_avatar/gov.near.org/bexanderthebex/32/6930_2.png) [@Bexanderthebex](https://gov.near.org/u/Bexanderthebex)\
**Post date:** [January 31, 2022, 1:42pm UTC](https://gov.near.org/t/report-stats-gallery-development-jan-1-31/14182/1 "2022-01-31T13:42:33Z")

</div>

**Project title** : Stats.gallery

**Project Summary** : Provides usable stats/insights for NEAR ecosystem users in a gamified way

**Period** : Jan 1 - 31

The following are the results done:

- Integrated leaderboard endpoints to the frontend namely `weekly top nfts`, `weekly top dapps`, and `weekly top active`

 ![Fellowship_update_leaderboard](https://europe1.discourse-cdn.com/flex005/uploads/gov_near/original/2X/7/756e42688c3cc48dbc72145378e6d9be61fb0458.png)

- Continously monitored and scaled the cloud resources of stats.gallery based on the traffic/demand
- Scaled the query that processed weekly top nft leaderboard from processing ~2M records to process ~15M records as the number of transactions in the near network grew:

> <https://github.com/NEAR-Edu/stats.gallery-server/issues/48>
>
> When this statement is ran 
> 
> \`\`\` sql
> explain analyze select
> receipt\_receiver…\_account\_id as account\_id,
> count(1) as number\_of\_transactions
> from action\_receipt\_action
> where action\_receipt\_action.receipt\_included\_in\_block\_timestamp \>= 1642820620833000000
> and args -\>\> 'action\_kind' = 'FUNCTION\_CALL' and args -\>\> 'method\_name' like 'nft\_%'
> group by
> receipt\_receiver\_account\_id
> order by number\_of\_transactions desc
> limit 10;
> \`\`\`
> 
> We get the following result which shows that the query is not hitting indexes and takes a long time to finish
> 
> \`\`\`
> -\> Finalize GroupAggregate (cost=3017679.15..3017819.09 rows=1102 width=22) (actual time=109570.988..109608.459 rows=0 loops=1)
> Group Key: receipt\_receiver\_account\_id
> -\> Gather Merge (cost=3017679.15..3017803.08 rows=998 width=22) (actual time=109570.987..109608.455 rows=0 loops=1)
> Workers Planned: 2
> Workers Launched: 2
> -\> Partial GroupAggregate (cost=3016679.13..3016687.86 rows=499 width=22) (actual time=109550.517..109550.518 rows=0 loops=3)
> Group Key: receipt\_receiver\_account\_id
> -\> Sort (cost=3016679.13..3016680.38 rows=499 width=14) (actual time=109550.514..109550.515 rows=0 loops=3)
> Worker 1: Sort Method: quicksort Memory: 25kB
> Worker 0: Sort Method: quicksort Memory: 25kB
> Sort Method: quicksort Memory: 25kB
> Sort Key: receipt\_receiver\_account\_id
> -\> Parallel Seq Scan on action\_receipt\_action (cost=0.00..3016656.77 rows=499 width=14) (actual time=109550.443..109550.444 rows=0 loops=3)
> Rows Removed by Filter: 17019884
> Filter: ((receipt\_included\_in\_block\_timestamp \>= '1642820620833000000'::numeric) AND ((args -\>\> 'method\_name'::text) ~~\* 'nft\_%'::text) AND ((args -\>\> 'action\_kind'::text) = 'FUNCTION\_CALL'::text))
> 
> Planning Time: 0.164 ms
> Execution Time: 109608.527 ms
> \`\`\`

- Updated node.js version from 14 to 16 for better security and feature support
- Updated dockerfile environments to eliminate errors and warnings for better developer experience and attract more developers to the project
- Database schema updates in order to support the upcoming sponsored badges
- Ideation and implementation of backfilling user badges into the database in order for us to speed up the queries that analyze user eligibility for badges and also help us expand to add more badges
- Implemented the backfilling mechanism for “One-of-a-kind” badge that will serve as the basis for the upcoming backfilling of user badges.
- Bug fix for solving ordering guarantees when saving on chain transactions to stats.gallery database