🧠 Methodology

In this dashboard, we use parameters. The Days parameter determines that you want to see data from the previous few days, including Last 90 days, Last 180 days, Last 360 days, All time and the Date_Trunc parameter determines that you want to see chart data on a daily, weekly or monthly basis. So to use the parameters:

First log in to flipsidecrypto, then select the desired parameters in the parameters section and finally click the Apply All Parameters button to have the results updated for you.

To deal with this dashboard, we use the optimism.core, optimism.velodrom and optimism.sushi schemas and the fact_blocks, ez_nft_sales, fact_event_logs, fact_hourly_token_prices, fact_transactions, fact_token_transfers, dim_labels, optimism.velodrom.ez_swaps, optimism.sushi.ez_swaps tables.

To analyze and review optimism, we check the following items:

Token Price

Daily ETH and OP Token Price

Transactions and Blocks

Total and Daily Transactions

Average and Daily Transaction per Second

Average and Daily Block per Day

Average and Daily Block Time

Daily Success and Failed Transaction

Daily Success and Failed Rate

Users

Daily Active User

Daily New User

NFTs

Total and Daily NFT Mints

Total Daily NFT Sales

CEX Transfers

Daily Transfer OP token From CEX

Daily Transfer OP token To CEX

Swaps

Total and Daily Swaps

Bridges

Total Bridges Inflow and Outflow Volume

Daily Bridges Inflow

Daily Bridges Outflow

Below we explain how to obtain and analyze each of the criteria that we want to check:

Transactions Per Second (TPS):

In the context of blockchains, transactions per second (TPS) refers to the number of transactions that a network is capable of processing each second.

To get this section, we use the fact_transactions table and do the following:

results as (
	select 
  		date_trunc('{{Date_Trunc}}', block_timestamp) as "Date",
  		count(distinct tx_hash) as num_tx,
  		num_tx / 86400 as "TPS"
		from optimism.core.fact_transactions
		where block_timestamp::date >= current_date - (select time_frame from fix_parameter)
  		and block_timestamp::date <= current_date - 1
		group by "Date"
)

select 
  	avg("TPS") as "TPS"
	from results

Block per Day:

The number of blocks created every Day

To get this section, we use the fact_blocks table and do the following:

select 
 	date_trunc('{{Date_Trunc}}', block_timestamp) as "Date",
  	count(distinct block_number) as "Blocks Per Day"
  	from optimism.core.fact_blocks
  	where block_timestamp::date >= current_date - (select time_frame from fix_parameter)
  	and block_timestamp::date <= current_date - 1
  	group by "Date"
  	order by "Date"

Block Time:

Block time is the average time needed to mine a new block. Blockchain networks have different block times. Block times range from a matter of seconds to a few minutes. With proof-of-work-based blockchains, the block time is maintained close to a constant value by re-evaluating the mining difficulty.

To do this, first, we divide the blocks into two sets, odd and even, based on the block number, and obtain the set of odd and even blocks.

- Odd Block Set: mod(block_number::int, 2) = 1 - Even Block Set: mod(block_number::int, 2) = 0

After obtaining the set of odd and even blocks, we join them together based on:

- (even.block_number::int) = ((odd.block_number::int) + 1) and time_odd <= time_even

Then we get the difference in creation time between two consecutive blocks based on seconds:

- datediff('second', time_odd, time_even) as block_time_difference

Finally, we calculate the Average/Daily of all the time differences between the two blocks obtained for each blockchain:

- round(avg(block_time_difference), 3) as avg_block_time_difference

Transfer OP token To and From CEX:

To obtain this section, we first use the dim_labels table to obtain the list of cex:

select address from optimism.core.dim_labels where label_type = 'cex'

Finally, we get the list of transfers related to the a cex transfer (from/to) for OP

🔍 Transfer to cex:

select 
  		*
  		from optimism.core.fact_token_transfers
  		where to_address in (select address from cex_address)
  		and from_address not in (select address from cex_address)
  		and block_timestamp::date >= current_date - (select time_frame from fix_parameter)
  		and block_timestamp::date <= current_date - 1
  		and raw_amount > 0 and raw_amount is not null
  		and contract_address = '0x4200000000000000000000000000000000000042'

🔍 Transfer from cex:

select 
  		*
  		from optimism.core.fact_token_transfers
  		where to_address not in (select address from cex_address)
  		and from_address in (select address from cex_address)
  		and block_timestamp::date >= current_date - (select time_frame from fix_parameter)
  		and block_timestamp::date <= current_date - 1
  		and raw_amount > 0 and raw_amount is not null
  		and contract_address = '0x4200000000000000000000000000000000000042'

Bridges Inflow/Outflow:

To get this section, we use the fact_event_logs table:

🔍 Bridges Outflow

select
  		*
  		from optimism.core.fact_event_logs
  		where event_inputs:to = '0x0000000000000000000000000000000000000000'
  		and event_name = 'Transfer'
  		and block_timestamp::date >= current_date - (select time_frame from fix_parameter)
  		and block_timestamp::date <= current_date - 1

🔍 Bridges Inflow

select
  		*
  		from optimism.core.fact_event_logs
  		where origin_from_address = '0x0000000000000000000000000000000000000000' 
  		and origin_to_address = '0x4200000000000000000000000000000000000007' 
  		and event_name = 'Transfer'
  		and block_timestamp::date >= current_date - (select time_frame from fix_parameter)
  		and block_timestamp::date <= current_date - 1

Ecosystem or Project Analysis - Optimism