saeedmzn[Ethereum bridge destinations] - by platform over time
Updated 2023-11-16
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
36
›
⌄
-- forked from [Ethereum bridge destinations] - by destination chain over time @ https://flipsidecrypto.xyz/edit/queries/ccbcfebc-58df-40ce-8a76-033a6ab94ca2
with prices as (
select date_trunc(day,HOUR) ::date date ,
TOKEN_ADDRESS ,
SYMBOL,
avg(price) USD
from ethereum.price.ez_hourly_token_prices
where date_trunc(day,HOUR) ::date >= '2023-01-01'
group by 1,2,3
),
daily as (
select date_trunc(day,BLOCK_TIMESTAMP)::date day ,
TX_HASH ,
ORIGIN_FROM_ADDRESS ,
PLATFORM
-- (zeroifnull(AMOUNT) * zeroifnull(USD) ) price_USD
from ethereum.defi.ez_bridge_activity b
-- join prices p
-- on (date = date_trunc(week,BLOCK_TIMESTAMP)::date and b.TOKEN_ADDRESS =p.TOKEN_ADDRESS )
where BLOCK_TIMESTAMP >= '2023-01-01'
and EVENT_NAME ilike '%Transfer%'
and TX_HASH not in ('0x1f2b9f44ca381a6c2ca01edaafe4919dd1fedba0b13b5bfbb853b7ca84a0975a','0xe44000ed43e311bb9acbf3f811f066514bef3972fdbb176f5308641a5416ad14','0x3cc0c33249df6ec7ee1122aede9ce17f16694165fe0dde7a110386817087975c','0x31ee8f7428d2ba890a45e8a63cd82cb7cf79d74bb5123b3b0ea29f96a8fe9249','0xaa0137f81c9ac52badbfd41dd38bd81e74e7c687ad40eca0e8fd1b46f468f8e5','0xc1927d65efff0f081de66eac3aaa2bdf90378084e55b73e82667cb0e0571e6bb','0x9874dca6b8b825d4a12c7cda1b7d00f13a0e3c6a59001943328a216605fee308','0xad25f450f481bb5f5d7b7007c187404631a1f2a3ea24d2c86d74b683352b1ce6','0xbe8743633e9446640cb8eece20f8914f836f2f631c85095ce9657853f3d83a7f','0xe7bac983d7c4513d0e89102b9e4552e1ab96b229e9beff293996d68770267d7a')
and PLATFORM is not NULL
)
select date_trunc(week,day)::date weekly ,
PLATFORM ,
count (DISTINCT TX_HASH ) num_bridges ,
count (DISTINCT ORIGIN_FROM_ADDRESS) num_bridgers ,
sum (num_bridges) over (partition by PLATFORM order by weekly ) cum_bridges
from daily
group by 1 ,2
Run a query to Download Data