Methodology

Dashboard Objective

Show Top Smart Contract by Gas Usage and Gas Fees on Avalanche, and the Top dApps by Gas Usage and Gas fees, explain accuracy of the results and additional metrics!.

Dashboard Structure

Introduction & Data Accuracy
Avalanche Overview
- Total Metrics in the Timeframe.
- Daily Metrics in the Timeframe.
- Gas Fees $AVAX in the Timeframe.
Contracts
- Daily Top 3 Contracts by Network Fees and Gas Usage in the Timeframe.
- Top 10 Contracts by Network Fees in the Timeframe.
- The Top 10 from before - Additional Metrics.
dApps
- Daily Activity by Label Type in the Timeframe.
- Daily Top 5 dApps by Network Fees and Gas Usage in the Timeframe.
- Top 10 Contracts by Network Fees in the Timeframe.
- The Top 10 from before - Additional Metrics.
Conclusion
Methodology

Dashboard Method

Using Flipside Tables of Avalanche I'll track the activity of Smart Contracts and dApps Using:

--Table Used to get the contract addresses and their transaction hash.

avalanche.core.fact_event_logs & avalanche.core.fact_transactions

-- Methodology Used to get the correct Method:

with cte1 as (select 
distinct contract_address,
a.tx_hash as tx_hash
from avalanche.core.fact_transactions a join avalanche.core.fact_event_logs b on a.tx_hash = b.tx_hash
where a.block_timestamp >= current_date() - {{Days}}
)

--Now cte1 considers a contract_address once per transaction hash and we can join using:

Select
...
from avalanche.core.fact_transactions a join cte1 b on a.tx_hash = b.tx_hash

--With that we can operate normally, select our results and finally adding a name using:

left join avalanche.core.dim_labels c on a.contract_address = c.address

--With that I'll add the label table and now I can get names for the contract addresses, but for the dApps I need to use another cte:

cte2 as (
select 
distinct project_name,
tx_hash
from cte1 left join avalanche.core.dim_labels on contract_address = address
where project_name != 'wrapped avax'
having project_name is not null
)

--With cte1 being the same as before, now I had to make an extra cte to consider all the transactions hash per dApp (Project_name) and not per contract_address, additionally I discard the null values and the wrapped avax because their activity is already showed.

Dashboard Sources

Avax Logo used in the Tabs and Introduction by Cryptologos

Avalanche Ecosystem by The Tie

Avalanche by Wikipedia

MetricsDAO Bounty Question

Create a dashboard to analyze gas usage on the Avalanche network.

The dashboard should have two tabs: one for contracts and one for dApps. Each tab should show which ones generate the most network fees and use the most gas over a user-selectable time period.

Include any other metrics or charts that reveal contracts and dApps with growing usage based on data such as network fees, number of unique users, or other relevant metric.

Avalanche Gas Fees