peteer15Terradash
    Updated 2022-12-17
    with stablecoins as (select address from terra.core.dim_address_labels where LABEL like '%ust%'
    and LABEL_SUBTYPE in ('pool', 'token_contract'))

    , stats as
    (
    select count(*) , sum(from_amount/pow(10,from_decimal)) as from_amount , sum(to_amount/pow(10,to_decimal)) as to_amount
    from terra.core.ez_swaps
    where to_currency in (select address from stablecoins) or from_currency in (select address from stablecoins) or pool_id in (select address from stablecoins)
    )
    , weekly as
    (
    select count(*) , sum(from_amount/pow(10,from_decimal)) as from_amount , sum(to_amount/pow(10,to_decimal)) as to_amount ,date_trunc('week', block_timestamp) as cdate
    from terra.core.ez_swaps
    where to_currency in (select address from stablecoins) or from_currency in (select address from stablecoins) or pool_id in (select address from stablecoins)
    group by cdate
    )

    , monthly as
    (
    select count(*) , sum(from_amount/pow(10,from_decimal)) as from_amount , sum(to_amount/pow(10,to_decimal)) as to_amount ,date_trunc('month', block_timestamp) as cdate
    from terra.core.ez_swaps
    where to_currency in (select address from stablecoins) or from_currency in (select address from stablecoins) or pool_id in (select address from stablecoins)
    group by cdate
    )

    select * from stats
    --select * from weekly
    --select * from monthly