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
- 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:
github.com/NEAR-Edu/stats.gallery-server
opened 02:35AM - 29 Jan 22 UTC
closed 07:39AM - 29 Jan 22 UTC
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
Bexanderthebex