CryptoIcicleBridger Destinations (redux) - Top Events Over Time
Updated 2022-10-31
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
›
⌄
-- Let’s take a closer look at DEXs on Optimism. Make a dashboard that compares at least three DEXs based on:
-- Pay by Quality Your score determines your final payout.
-- Grand Prize 187.5 USDC (A score of 11 or 12 earns you a Grand Prize title)
-- Payout 125 USDC
-- Score Multiplier 0-7 : 0% 8 : 50% 9 : 75% 10 : 100% 11 : 125% 12 : 150%
-- Payout Network Ethereum
-- Level Advanced
-- Difficulty Elite
-- Where do people go when they bridge to Optimism from Ethereum?
-- What are the 10 most popular first destinations for users that have just bridged from Ethereum?
-- What has this been for each day in the past month? How has this changed over the last three months? Last six months?
-- BONUS: Post your dashboard on Twitter and tag @flipsidecrypto and any relevant accounts!
-- SQL Credit: https://app.flipsidecrypto.com/velocity/queries/4547c25a-efc5-44dc-bf3d-961705b6eb90
with tab1 as (
select block_timestamp,origin_from_address,tx_hash
from ethereum.core.fact_event_logs
where origin_to_address = '0x99c9fc46f92e8a1c0dec1b1747d010903e884be1'),
tab2 as (select block_timestamp, origin_from_address,origin_to_address,tx_hash, event_name, contract_name
from optimism.core.fact_event_logs),
tab3 as(
select a.block_timestamp,b.origin_from_address,a.origin_to_address,a.tx_hash,a.event_name,a.contract_name
from tab1 b inner join tab2 a on b.origin_from_address = a.origin_from_address where a.block_timestamp > b.block_timestamp),
tab4 as (
select date_trunc('day', block_timestamp) as daily, event_name as event, count(distinct tx_hash) as tx_count, rank() OVER (PARTITION by daily order by tx_count desc) as rank
from tab3 group by 1,2 qualify rank <=10)
select event , sum(tx_count) as txs
from tab4
where event is not null
and daily >= '{{start_date}}'
group by 1
order by 2 desc
limit 10
Run a query to Download Data