Near Analytics: Cheatsheet

Ali

Dec 27, 2021

Cheat Code for Near Protocol Analytics.

Did you know you can access Near Protocol's mainnet analytics using SQL to query things like:

> Daily Active Accounts

> Daily Active Contracts Count

> Daily Gas Used

> Deployed Contracts

> *Near Ecosystem Entities*

*Data as of 12/25/21

Connecting to the Near Analytics DB:

If you do not have pgAdmin or another SQL database query tool - you will need to download one (Dbeaver)
You will need the credentials from the Near Github which can be found here:
https://github.com/near/near-analytics
We will be using the mainnet credentials
Using pgAdmin - click on “Servers” > Create > Server
General:
Connection:
Username: public_readonly
Password: nearprotocol
Host: 34.78.19.198
Maintenance DB: indexer_analytics_mainnet
Save - your connection should be successful

Tables Available:

Example SQL Queries

Let us explore some sample SQL queries you can run on the tables:

Daily Active Accounts Count & Weekly Active Accounts

select * from daily_active_accounts_count
order by collected_for_day desc

This query will show the date, and active accounts count over time from most recent to oldest:

You can see here that on 12/25/21 the active accounts count was 25,503 - these numbers might change depending on when you pull the data.

select 
* 
from daily_active_accounts_count
where collected_for_day >= '2021-01-01'
order by active_accounts_count desc
limit 1

This query will tell you the date on which there were the most active accounts, from this dataset we can see that it was on 9/15/21: 53,307

Top 3 Active Accounts by week: 9/6/2021, 9/13/2021, 12/20/2021

Some notable Near Protocol Tweets from September 2021:

Daily Gas Used

SELECT 
DATE_PART('week', collected_for_day) as week,
sum(gas_used) as sum_gas
FROM DAILY_GAS_USED
where collected_for_day >= '2021-01-01'
group by 1
order by sum_gas desc

Suppose you want to sum the gas used by week in 2021, you can use the query above to sum the gas by week for 2021, and then order by the sum of gas, sorted in descending order. This will show you the weeks in which most gas was used.

We’ve seen some of the highest gas usages in the past few days as well:

Daily Ingoing Transactions Per Account Count

select * 
from
daily_ingoing_transactions_per_account_count
where collected_for_day = '2021-12-25'
order by ingoing_transactions_count desc

Daily Outgoing Transactions Per Account Count:

Near Ecosystem Entities Table

Probably one of the most interesting tables. This table has information on Near Ecosystem entities and breaks each entity into various categories: App, NFT, Guild, Defi, DAO. Some entities may have multiple labels but this does help you, filter through entities you are interested in. Let’s check out entities that fall under “Defi”:

SELECT 
count(*)
from near_ecosystem_entities 
where is_defi is True

We can see that there are 38 entities as of today that have registered under “Defi” for the Near Ecosystem.

Full list of entities:

SELECT 
title
from near_ecosystem_entities 
where is_defi is True

Onomy Protocol, Stake.gg, Ref Finance, Amplify Art, Conceal Protocol, CheddarFi, Taker Protocol, Tip Jar Live, Open DeFi, Rainbow Bridge, SushiSwap, Burrow, Pulse, Vaporwave Finance, Cannabis Genome DAO, DePocket, Spin, Berry Club, AntiMatter, Skyward Finance, Picipo, NearPad, Hapi, ChainScore, Meta Pool, IndexZoo, SmartPad, Wizards Guild, iBetYou, Moo Pay, Angle Protocol, Solace.Fi, MTVRS, Marnotaur, TiTi Protocol, Emiswap, NEARswap, Patchwork

Some additional insights into Near Ecosystem Entities:

Top 10 Categories of Near Ecosystem Entities: