Maditotal
Updated 2023-01-18Copy Reference Fork
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
›
⌄
with prices AS
(
SELECT date_trunc('day', RECORDED_AT) as date_price, symbol, avg(price) as price
FROM osmosis.core.dim_prices
GROUP BY 1,2
),
df as (
select date_trunc('day', BLOCK_TIMESTAMP) as date,
TRANSFER_TYPE, tx_id, SENDER, RECEIVER,
amount, amount/pow(10,DECIMAL) as amount_usd, UPPER(split(currency,'-')[0]) AS CURRENCY
from axelar.core.fact_transfers
where TRANSFER_TYPE in ('IBC_TRANSFER_IN', 'IBC_TRANSFER_OUT') and TX_SUCCEEDED = 'TRUE' and date >= '2022-01-26' AND not CURRENCY ilike any ('factory%', 'gravity%') and amount is not null and currency is not null)
select date, TRANSFER_TYPE, count(DISTINCT tx_id) as "Number of Bridges", sum(amount_usd) as "Total Amount, USD", Currency as Asset,
sum("Number of Bridges") over (order by date asc rows between unbounded preceding and current row) as "Cumulative number of Bridges"
from(
select df.* from df left join prices on df.date = prices.date_price and df.currency = prices.symbol)
group by 1,2, 5
Run a query to Download Data