DiamondPools Overeiw copy
    Updated 2023-06-04
    -- forked from HBD / Pools Overeiw @ https://flipsidecrypto.xyz/HBD/q/2023-05-31-02-26-am-OWUAY4

    with pairs as (select
    EVENT_DATA:numPairs::number as pool_id,
    EVENT_DATA:pairAddress as pairAddress,
    SWAP_CONTRACT,
    CASE
    when substr(EVENT_DATA:token0Key,20,21) = 'FlowToken' then 'FLOW'
    when substr(EVENT_DATA:token0Key,20,21) = 'FiatToken' then 'USDC'
    when substr(EVENT_DATA:token0Key,20,21) = 'SloppyStakes' then 'Sloppy'
    else substr(EVENT_DATA:token0Key,20,21)
    end as token0,
    CASE
    when substr(EVENT_DATA:token0Key,20,21) = 'FlowToken' then 'FLOW'
    when substr(EVENT_DATA:token0Key,20,21) = 'FiatToken' then 'USDC'
    when substr(EVENT_DATA:token0Key,20,21) = 'SloppyStakes' then 'Sloppy'
    else substr(EVENT_DATA:token0Key,20,21)
    end as token1
    from flow.core.fact_events a
    join flow.core.dim_swap_pool_labels b on VAULT_ADDRESS = EVENT_DATA:pairAddress
    where EVENT_CONTRACT = 'A.b063c16cac85dbd1.SwapFactory'
    and EVENT_TYPE = 'PairCreated'
    and TX_SUCCEEDED = 'true'
    order by 1),

    main as (
    select
    a.BLOCK_TIMESTAMP::date as date,
    a.TX_ID,
    pool_id,
    SWAP_CONTRACT,
    token0 || '/' || token1 as pair_name,
    case
    when EVENT_DATA:direction = '1' then token0
    when EVENT_DATA:direction = '0' then token1
    end as token_in,
    Run a query to Download Data