FlippppppaIBC Volume copy
Updated 2023-09-28
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
›
⌄
-- forked from Playwo / IBC Volume @ https://flipsidecrypto.xyz/Playwo/q/fKlPG7s1tGLw/ibc-volume
WITH timeframe AS (
SELECT date_day AS date
FROM crosschain.core.dim_dates
WHERE date_day >= TO_DATE('2023-05-07') AND date_day <= TO_DATE('2023-05-22')
)
SELECT t.date,
NVL(sum(IFF(transfer_type = 'IBC_TRANSFER_IN', amount, 0)) / POW(10, 18), 0) AS "IBC_IN $ARB",
NVL(sum(IFF(transfer_type = 'IBC_TRANSFER_IN', amount * p.price, 0)) / POW(10, 18), 0) AS "IBC_IN $USD",
NVL(sum(IFF(transfer_type = 'IBC_TRANSFER_OUT', amount, 0)) / POW(10, 18), 0) AS "IBC_OUT $ARB",
NVL(sum(IFF(transfer_type = 'IBC_TRANSFER_OUT', amount * p.price, 0)) / POW(10, 18), 0) AS "IBC_OUT $USD",
sum("IBC_IN $ARB" - "IBC_OUT $ARB") OVER (ORDER BY t.date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "$ARB Supply",
"$ARB Supply" * p2.price AS "$ARB Supply in $USD"
FROM timeframe t
LEFT JOIN osmosis.core.fact_transfers ON transfer_type IN ('IBC_TRANSFER_IN', 'IBC_TRANSFER_OUT')
AND currency = 'ibc/10E5E5B06D78FFBB61FD9F89209DEE5FD4446ED0550CBB8E3747DA79E10D9DC6'
AND t.date = TRUNC(block_timestamp, 'day')
LEFT JOIN osmosis.core.ez_prices p ON p.recorded_hour = TRUNC(block_timestamp, 'hour')
AND p.currency = 'ibc/10E5E5B06D78FFBB61FD9F89209DEE5FD4446ED0550CBB8E3747DA79E10D9DC6'
LEFT JOIN osmosis.core.ez_prices p2 ON p2.recorded_hour = t.date
AND p2.currency = 'ibc/10E5E5B06D78FFBB61FD9F89209DEE5FD4446ED0550CBB8E3747DA79E10D9DC6'
GROUP BY t.date, p2.price
Run a query to Download Data