Mityblocto-in
Updated 2022-11-08
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
›
⌄
with act1 as (
with tab1 as(
select BLOCK_TIMESTAMP::date as date,TOKEN_CONTRACT,sum(AMOUNT)as bridge_amount
from flow.core.fact_bridge_transactions
where BRIDGE='blocto'
and DIRECTION='inbound'
group by 1,2),
tab2 as (
select EVENT_CONTRACT,CONTRACT_NAME
from flow.core.dim_contract_labels)
select date,CONTRACT_NAME,bridge_amount
from tab1
left join tab2
on tab1.TOKEN_CONTRACT=tab2.EVENT_CONTRACT
group by 1,2,3),
act2 as (
select date_trunc('day',TIMESTAMP)as day,TOKEN,avg(PRICE_USD)as price_usd
from flow.core.fact_prices
where TOKEN='REVV' or TOKEN='Flow' or TOKEN='Starly'
or TOKEN='Blocto' or TOKEN='Sportium' or TOKEN='Rally'
group by 1,2)
select day,TOKEN,(bridge_amount*price_usd)as bridge_usd
from act1
left join act2
on act1.date=act2.DAY
where token is not null
group by 1,2,3
Run a query to Download Data