nsa2000Inbound Bridging
Updated 2022-11-22
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
›
⌄
--this code is thankfully burrowed from 0xHaM☰d: https://app.flipsidecrypto.com/dashboard/fHME6k
with priceTb as (
select
date_trunc('{{Frequency}}', timestamp) as p_date,
TOKEN_CONTRACT,
symbol,
avg(price_usd) as "PRICE($)"
from flow.core.fact_prices
WHERE timestamp::date >= CURRENT_DATE - INTERVAL '{{Past_Weeks}} WEEK'
AND token != 'Blocto'
group by 1, 2, 3
order by 1
)
SELECT
date_trunc('{{Frequency}}', block_timestamp) as date,
BLOCKCHAIN,
BRIDGE,
symbol,
COUNT(DISTINCT tx_id) as tx_cnt,
COUNT(DISTINCT FLOW_WALLET_ADDRESS) as bridger_cnt,
sum(AMOUNT*"PRICE($)") as Vol_usd,
sum(tx_cnt) over (partition by BLOCKCHAIN order by date) as cum_tx_cnt,
sum(Vol_usd) over (partition by BLOCKCHAIN order by date) as cum_Vol_usd,
sum(tx_cnt) over (partition by symbol order by date) as cum_tx_cnt_symbol,
sum(Vol_usd) over (partition by symbol order by date) as cum_Vol_usd_symbol
FROM flow.core.ez_bridge_transactions a JOIN priceTb b on a.TOKEN_CONTRACT = b.TOKEN_CONTRACT and a.BLOCK_TIMESTAMP::date = b.p_date
where block_timestamp::date >= CURRENT_DATE - INTERVAL '{{Past_Weeks}} WEEK'
and DIRECTION ilike ('inbound')
GROUP by 1,2,3,4
ORDER by 1
Run a query to Download Data