AlferedRoseRemoved Assets
Updated 2022-06-10Copy Reference Fork
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 MainData as
(select Count(tx_id) as Counted_transaction ,
ACTION, Sum(( amount / ( power(10 , decimal ) ) )) as Amount , Currency
from osmosis.core.fact_liquidity_provider_actions
where Block_Id >=4707300 and TX_STATUS = 'SUCCEEDED'
and ACTION = 'pool_exited'
group by 2,4),
CurrencyName as (
select
distinct y.PROJECT_NAME as AssetName , y.address
from MainData x INNER join osmosis.core.dim_labels y on x.Currency = y.ADDRESS
),
Prices as (
select
y.SYMBOL as Asset , avg(y.PRICE) As Price
from CurrencyName x INNER JOIN osmosis.core.dim_prices y on x.AssetName = y.SYMBOL
where RECORDED_AT >= '2022-06-07 19:59:59' and RECORDED_AT <= '2022-06-08 19:59:59'
group by 1
)
Select
x.Counted_transaction ,x.ACTION,y.AssetName,x.Amount, (x.amount * z.price) as Value_usd
from MainData x INNER JOIN CurrencyName y on x.Currency = y.ADDRESS INNER JOIN Prices z on y.AssetName = z.Asset
Run a query to Download Data