[Report] Stats.gallery development Jan 1 - 31

Bexanderthebex · January 31, 2022, 1:42pm

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

Fix statement timeout when running top nft queries

opened 02:35AM - 29 Jan 22 UTC

closed 07:39AM - 29 Jan 22 UTC

Bexanderthebex

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

Topic		Replies	Views
[Report] Development of stats.gallery Fellowships	0	159	January 1, 2022
NEAR Data Project: stats.gallery Data community , opportunities , data	2	654	March 18, 2022
[Report] Development of Stats.gallery (February 1 - 28, 2022) Fellowships	0	86	March 2, 2022
Introducing stats.gallery Education	5	732	September 12, 2021
Stats.gallery September Update Education	6	869	February 24, 2022

[Report] Stats.gallery development Jan 1 - 31

Related topics