keshanwETH & wBTC On- and Off- Ramps - 2
    Updated 2022-06-07
    with hourly_price 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
    group by hour, token),
    burns as (select day, label, sum(txs) as txs, sum(amount_osmo) as amount_osmo, sum(amount) as amount from (select date_trunc('day', block_timestamp) as day,
    count(tx_id) as txs,
    sum(regexp_substr(attribute_value, '^[0-9]+')/pow(10, raw_metadata[1]:exponent)) as amount,
    sum(regexp_substr(attribute_value, '^[0-9]+')/pow(10, raw_metadata[1]:exponent) * price) as amount_osmo,
    case when label like 'Wrapped Ethe%' then 'Wrapped Ethereum' else label end as label -- there are two token names in price table (derived from swaps)
    from osmosis.core.fact_msg_attributes
    left join osmosis.core.dim_labels on split(attribute_value, regexp_substr(attribute_value, '^[0-9]+'))[1] = address
    left join hourly_price on hour=date_trunc('hour', block_timestamp) and label=token
    where msg_type='burn' and attribute_key='amount' and (label='Wrapped Bitcoin' or label like 'Wrapped Ether%')
    group by day, label, attribute_value)
    where label is not null
    group by day, label),
    mints as (select day, label, sum(txs) as txs, sum(amount_osmo) as amount_osmo, sum(amount) as amount from (select date_trunc('day', block_timestamp) as day,
    count(tx_id) as txs,
    sum(regexp_substr(attribute_value, '^[0-9]+')/pow(10, raw_metadata[1]:exponent)) as amount,
    sum(regexp_substr(attribute_value, '^[0-9]+')/pow(10, raw_metadata[1]:exponent) * price) as amount_osmo,
    case when label like 'Wrapped Ethe%' then 'Wrapped Ethereum' else label end as label -- there are two token names in price table (derived from swaps)
    from osmosis.core.fact_msg_attributes
    left join osmosis.core.dim_labels on split(attribute_value, regexp_substr(attribute_value, '^[0-9]+'))[1] = address
    left join hourly_price on hour=date_trunc('hour', block_timestamp) and label=token
    where msg_type='coinbase' and attribute_key='amount' and (label='Wrapped Bitcoin' or label like 'Wrapped Ether%')
    group by day, label, attribute_value)
    where label is not null
    group by day, label)
    Run a query to Download Data