🐻 Bear Market → Source

Economists define a bear market as a decline of 20% or more of a major stock market index, for a sustained period. A bear market is the opposite of a bull market, a period marked by market gains of 20% or more.

• Image Source

🔴 Optimism

Optimism is a fast, stable, and scalable L2 blockchain built by Ethereum developers, for Ethereum developers. Built as a minimal extension to existing Ethereum software, Optimism's EVM-equivalent architecture scales your Ethereum apps without surprises.

• Website, Twitter, Discord

✍️ Description of Work

In this dashboard we want to analyze Optimism during the month of July as markets were get turbulent. for Optimism analysis in July, we check two case:

• Market performance
• Network performance

For each case, we check several criteria, which include the following:

→ Network Performance

• Daily Transaction per ==Second/Minutes/Hour during the month of July

• Daily Block per Day/Hour/Minutes during the month of July

• Average Block per ==Day/Hour/Minutes during the month of July

• Daily Block Time

• Average Block Time

• Daily Success and Failed Transaction during the month of July

• Daily Success and Failed Rate during the month of July

• Daily Active User

  \

→ Market Performance

• Daily ETH and OP Token Price during the month of July
• Daily Transactions during the month of July
• Daily NFT Mints and NFT Sales during the month of July
• Daily Transfer ETH and OP token to/from cex during the month of July
• Daily Swaps during the month of July
• Daily New User during the month of July
• Daily Bridges Inflow/Outflow during the month of July

🧠 Methodology

To deal with this bounty, 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, ez_swaps, ez_swaps tables.

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

Transaction per Second/Minutes/Hour:

• Transactions Per Second/Minutes/Hour (TPS/TPM/TPH): 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:

  select block_timestamp::date as "Days", count(distinct tx_hash) / 24 as "TPH", count(distinct tx_hash) / 1440 as "TPM",
  	count(distinct tx_hash) / 86400 as "TPS" from optimism.core.fact_transactions
  	where block_timestamp::date >= '2022-07-01' and block_timestamp::date <= '2022-07-31' group by "Days"
  

Daily Block per Hour/Day/Minutes: The number of blocks created every Hour/Day/Minutes

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

  select date_trunc('hour', block_timestamp) as "Hours", count(distinct block_number) as "Blocks Per Hour" from optimism.core.fact_blocks
    	where block_timestamp::date >= '2022-07-01' and block_timestamp::date <= '2022-07-31' group by "Hours" order by "Hours"
  

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 during the month of July
  • 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

Daily Success and Failed Transaction:

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

  select block_timestamp::date as "Days",	status as "Status",	count(tx_hash) as "Number of transactions"
    	from transactions group by "Days", "Status"
  

Transfer ETH and OP token to/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 and ETH:

🔍 Transfer to 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 >= '2022-07-01' and block_timestamp::date <= '2022-07-31' and raw_amount > 0 and raw_amount is not null
  		and contract_address = '0x4200000000000000000000000000000000000006' or contract_address = '0x42000000000000000000000000000000000000042'

🔍 Transfer from 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 >= '2022-07-01' and block_timestamp::date <= '2022-07-31' and raw_amount > 0 and raw_amount is not null
  		and contract_address = '0x4200000000000000000000000000000000000006' or contract_address = '0x42000000000000000000000000000000000000042'

Daily 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 >= '2022-07-01' and block_timestamp::date <= '2022-07-31'
  

  \

  🔍 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 >= '2022-07-01' and block_timestamp::date <= '2022-07-31'
  

Daily NFT Mints:

• For this section, we obtain the list of NFT Mint transactions using table fact_event_logs according to the following query:

  select * from optimism.core.fact_event_logs where event_inputs:from = '0x0000000000000000000000000000000000000000'
    		and event_inputs:tokenId is not null and event_name = 'Transfer'
  

In this bounty, based on the question title and the fact that the market crash was very strong in ==July==, we only check for the month of ==July==.

In this bounty, we compare market performance metrics with ==OP== and ==ETH== token prices

🔴 Optimistic Bears → Network Performance

1 → 🔴 Optimistic Network Performance: Analyze ==Blocks

✅ Observations

• The average number of blocks created is 110.9K== Per Day, 4620 Per Hour and 77 Per Minute.
• The number of blocks created at the Beginning of July until July 12 shows a constant trend.
• From July 13 onwards until the end of July you can see the process of creating incremental blocks and notice that more transactions are being made.
• The average time to create each block is 0.77 seconds, and this shows that each block processes a small number of transactions, so with the uptrend of the market starting in the second half of July, this number has been decreasing because more transactions are being made, as a result more blocks are created

2 → 🔴 Optimistic Network Performance: Analyze ==Transactions

✅ Observations

• The average number of Transactions per Hour is 143.3 K
• The average number of Transactions per Minutes is 2390
• The average number of Transactions per Second is 39.8
• The trend of the number of transactions from July 1 to July 12 shows a constant trend, but from mid-July onwards, the number of transactions has been increasing along with the growth of the market.
• Until mid-July, the transaction success rate is above 90%, but from mid-July, sometimes it is lower than 90%, because with the growth of the market, the number of transactions has increased, and therefore For that reason, the success rate of transactions has decreased a bit.
• The number of daily active users shows a constant trend due to the declining trend of the market from the beginning of July to July 25, but then it shows significant growth and the correlation between the price of the OP token and ETH can be seen somewhat with this trend.

✔️ Optimistic Network Performance Conclusion

> * The trend of the Number of Transactions and Number of Block from July 1 to July 12 shows a constant trend, but from mid-July onwards, the Number of Transactions and Number of Block has been increasing along with the growth of the market. Also the block time from the beginning of July to the middle of July is more than a second, and after that, due to the growth of the market and the increase in the number of transactions and blocks, the block time has decreased. > * Until mid-July, the transaction success rate is above 90%, but from mid-July, sometimes it is lower than 90%, because with the growth of the market, the number of transactions has increased, and therefore For that reason, the success rate of transactions has decreased a bit. The number of daily active users shows a constant trend due to the declining trend of the market from the beginning of July to July 25, but then it shows significant growth and the correlation between the price of the OP token and ETH can be seen somewhat with this trend.

🔴 Optimistic Bears → Market Performance

1 → 🔴 Optimistic Market Performance: OP and ETH ==Price

2 → 🔴 Optimistic Market Performance: Analyze ==Transactions

✅ Observations

• The trend of the number of transactions from July 1 to July 12 shows a constant trend, but from mid-July onwards, the number of transactions has been increasing along with the growth of the market.
• You can see the correlation ==between the number of transactions and the price of OP and ETH tokens==. For this reason, this correlation exists because with the increase in the price of tokens, users try to sell and exchange their tokens at higher prices, while they may have bought their tokens at prices lower or higher than the price of the token.

3 → 🔴 Optimistic Market Performance: Analyze Transfer ==To/From CEX

✅ Observations

• At the beginning of July, when the downward trend of the market started, until July 12, the volume and number of transfers of OP and ETH tokens From/To CEX shows a decreasing trend.
• From mid-July onwards, the process of transferring OP and ETH tokens From/To CEX has increased because the price of these tokens has been increasing and for this reason, more users are trading these tokens.
• Correlation between the price of OP and ETH tokens can be seen by transferring these tokens From/To CEX

3 → 🔴 Optimistic Market Performance: Analyze ==Swaps

✅ Observations

• At the beginning of July, when the downward trend of the market started, until July 12, the Volume (USD) and Number of Swaps shows a steady trend.
• From mid-July onwards, the process of swapping has increased because the price of OP and ETH has been increasing and for this reason, more users are swapping tokens.
• Correlation between the price of OP and ETH tokens can be seen by Swapping (Volume (USD), Number of Swaps and Swappers)

4 → 🔴 Optimistic Market Performance: Analyze ==Bridges Inflow/Outflow

✅ Observations

• The volume (USD) and number of Bridges Inflow transactions show a steady trend from the beginning of July to the middle of that month.
• From mid-July onwards, the number and volume of Bridges Inflow transactions have increased because the market trend has been up and Bridges Inflow has also increased.
• The correlation between Bridges Inflow and the price of OP and ETH tokens is clearly visible.
• There is no correlation between Bridges Outflow and OP Token price or ETH or other metrics

5 → 🔴 Optimistic Market Performance: Analyze ==New Users

✅ Observations

• Until July 25, the number of New Users in Optimism shows a steady trend, but after that we see a significant increase in new users in Optimism.
• Correlation between OP token price and ETH and New Users is observed to some extent

6 → 🔴 Optimistic Market Performance: Analyze ==NFT Mints and Sales

✅ Observations

• Until July 25, the Number of NFT Mints and Minters shows a steady trend, but after that we see a significant increase in Number of NFT Mints and Minters in Optimism.
• Until July 25, the Number of NFT Sales, Sales Volume(USD) and Number of Buyers shows a steady trend, but after that we see a significant increase in Number of NFT Sales, Sales Volume(USD) and Number of Buyers in Optimism.
• Correlation between OP and ETH price with NFT Sales and NFT Mints is observed to some extent

✔️ Optimistic Market Performance Conclusion

> After examining the parameters related to the performance of the market, we concluded that after the market crash in early July through the middle of that month, the activities related to these parameters had a declining or stable trend, but then with increasing. in the price of OP and ETH tokens and the growth of the market The activity trend of these criteria also increased. It was also observed that there is a correlation between the price of OP and ETH and all the criteria examined except Bridges Outflow

✅ Observations

The price of OP and ETH tokens shows a decreasing trend from July 1st to the 12th of July, and then this trend increases until the 21st and until the 26th, there is a decreasing trend, and after that, there is a significant growth in the price of OP and ETH tokens at the end of the month