AlferedRoseTotal Value Drained : 4th Wallet
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
28
29
30
31
32
33
34
35
36
›
⌄
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 Block_id<= 4713064 and TX_STATUS = 'SUCCEEDED'
and LIQUIDITY_PROVIDER_ADDRESS = 'osmo19l9wsymdh3mp7munzrlk8rs03ttnu2uaujyktu'
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
),
Last_table as (
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 )
select
sum(Value_usd) AS Total_USD ,
(case
when ACTION = 'pool_joined' then 'Joined'
when action = 'pool_exited' then 'Exited'
end)
As Action
from Last_table
Run a query to Download Data