0xHaM-dtotal contracts-transactions
    Updated 2023-04-12
    with pool_tb as (
    select
    date_trunc('day', block_timestamp) as date,
    FROM_CURRENCY,
    TO_CURRENCY,
    tx_id,
    'Pool_Id:' || b.value::string as "Pool ID"
    from osmosis.core.fact_swaps, lateral flatten(input => pool_ids) b
    )
    ,label_tb1 as (
    select date , LABEL as from_label, TO_CURRENCY , tx_id , "Pool ID"
    from pool_tb a join osmosis.core.dim_labels b on a.from_currency = b.ADDRESS
    )
    ,label_tb2 as (
    select date, from_label, label as to_label, tx_id, "Pool ID"
    from label_tb1 a join osmosis.core.dim_labels b on a.TO_CURRENCY = b.ADDRESS
    )
    ,lst_tb as (
    select date, "Pool ID",tx_id, from_label || ' to ' || to_label as pair
    from label_tb2
    )
    select
    "Pool ID",
    pair,
    count(DISTINCT pair) as pair_count
    from lst_tb
    where date >= '2022-05-01'
    group by 1, 2
    -- ORDER by 3 DESC
    -- limit 10


    Run a query to Download Data