LittlerData2024-04-11 02:50 AM
Updated 2024-04-19
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
›
⌄
with bridge_transactions as (
select BLOCK_TIMESTAMP,
SENDER,
SOURCE_CHAIN,
DESTINATION_CHAIN,
TOKEN_ADDRESS,
TOKEN_SYMBOL,
AMOUNT,
TX_HASH
from axelar.defi.ez_bridge_satellite
where BLOCK_TIMESTAMP::date between '2023-08-21' and '2023-08-23'
),
token_prices as (
select date_trunc(day,HOUR)::date date ,
TOKEN_ADDRESS,
SYMBOL,
avg(PRICE) usd_price
from crosschain.price.ez_hourly_token_prices
where date_trunc(day,HOUR)::date between '2023-08-21' and '2023-08-23'
group by 1,2,3
),
transactions_prices as (
select
BLOCK_TIMESTAMP as date,
SENDER,
SOURCE_CHAIN,
DESTINATION_CHAIN,
b.TOKEN_ADDRESS,
b.TOKEN_SYMBOL,
AMOUNT,
AMOUNT * zeroifnull(usd_price) amount_usd ,
TX_HASH
from bridge_transactions b left join token_prices p
on (BLOCK_TIMESTAMP::date =date and b.TOKEN_ADDRESS = p.TOKEN_ADDRESS )
where block_timestamp between '2023-08-21' and '2023-08-23'
),