AlferedRoseTotal Value Drained : 4th Wallet
    Updated 2022-06-10

    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