keshanwETH & wBTC On- and Off- Ramps
    Updated 2022-06-07
    -- Pool ID 712 = wBTC = ibc/D1542AA8762DB13087D8364F3EA6509FD6F009A34F00426AF9E4F9FA85CBBF1F
    -- Pool ID 704 = wETH = ibc/EA1D43981D5C9A1C4AAEA9C23BB1D4FA126BA9BC7020A25E0AE4AA841EA25DC5
    with prices as (select date_trunc('hour', block_timestamp) as hour,
    (case when to_currency='Osmosis' then from_currency when from_currency='Osmosis' then to_currency else null end) as token,
    avg(price) as price
    from (select block_timestamp,
    (case when from_currency='uosmo' then (from_amount/pow(10, from_decimal)) / (to_amount/pow(10, to_decimal)) when to_currency='uosmo' then (to_amount/pow(10, to_decimal)) / (from_amount/pow(10, from_decimal)) else null end) as price,
    (case when t.label is null then to_currency else t.label end) as to_currency,
    (case when f.label is null then from_currency else f.label end) as from_currency
    from osmosis.core.fact_swaps
    left join osmosis.core.dim_labels t on t.address=to_currency
    left join osmosis.core.dim_labels f on f.address=from_currency)
    where price is not null and token != 'Osmosis' and (token='Wrapped Ether' or token='Wrapped Bitcoin')
    group by hour, token), -- For getting prices, Reusing one of my previous queries which I wrote for a different bounty.
    btc as (select date_trunc('hour', block_timestamp) as date,
    sum(case when action='pool_joined' then case when currency='ibc/D1542AA8762DB13087D8364F3EA6509FD6F009A34F00426AF9E4F9FA85CBBF1F' then price * amount/pow(10, DECIMAL) else (amount/pow(10, DECIMAL)) end end) as added_liquidity_to_wbtc,
    sum(case when action='pool_exited' then case when currency='ibc/D1542AA8762DB13087D8364F3EA6509FD6F009A34F00426AF9E4F9FA85CBBF1F' then price * (-1 * amount/pow(10, DECIMAL)) else (-1 * amount/pow(10, DECIMAL)) end end) as withdraw_liquidity_from_wbtc,
    case when currency='ibc/D1542AA8762DB13087D8364F3EA6509FD6F009A34F00426AF9E4F9FA85CBBF1F' then 'wBTC' else 'OSMO' end as b_currency
    from osmosis.core.fact_liquidity_provider_actions left join prices on date_trunc('hour', block_timestamp) = hour and token='Wrapped Bitcoin'
    where pool_id=712
    group by date, b_currency),
    eth as (select date_trunc('hour', block_timestamp) as date,
    sum(case when action='pool_joined' then case when currency='ibc/EA1D43981D5C9A1C4AAEA9C23BB1D4FA126BA9BC7020A25E0AE4AA841EA25DC5' then price * amount/pow(10, DECIMAL) else (amount/pow(10, DECIMAL)) end end) as added_liquidity_to_weth,
    sum(case when action='pool_exited' then case when currency='ibc/EA1D43981D5C9A1C4AAEA9C23BB1D4FA126BA9BC7020A25E0AE4AA841EA25DC5' then price * (-1 * amount/pow(10, DECIMAL)) else (-1 * amount/pow(10, DECIMAL)) end end) as withdraw_liquidity_from_weth,
    case when currency='ibc/EA1D43981D5C9A1C4AAEA9C23BB1D4FA126BA9BC7020A25E0AE4AA841EA25DC5' then 'wETH' else 'OSMO' end as e_currency
    from osmosis.core.fact_liquidity_provider_actions left join prices on date_trunc('hour', block_timestamp) = hour and token='Wrapped Ether'
    where pool_id=704
    group by date, e_currency)

    select e.date, added_liquidity_to_wbtc as "WBTC/OSMO provided", withdraw_liquidity_from_wbtc as "WBTC/OSMO removed",
    added_liquidity_to_weth as "WETH/OSMO provided", withdraw_liquidity_from_weth as "WETH/OSMO removed", b_currency, e_currency
    from eth e left join btc using(date)


    -- select * from osmosis.core.fact_msg_attributes where msg_type='coinbase' limit 100

    Run a query to Download Data