binhachon47. DOGE Debut - DOGE pool in comparison with other pools
    Updated 2022-01-29
    with first_liquidity_action as (
    select
    distinct
    date_trunc('hour', block_timestamp) as blocktime,
    pool_name,
    row_number() over (partition by pool_name order by blocktime) as rownumber
    from thorchain.liquidity_actions
    qualify rownumber = 1
    ),
    swap_volume as (
    select
    date_trunc('hour', block_timestamp) as blocktime,
    pool_name,
    sum(from_amount_usd) as volume
    from thorchain.swaps
    group by blocktime, pool_name
    ),
    final_table as (
    select
    swap_volume.blocktime,
    first_liquidity_action.blocktime as first_time,
    case when position('-', swap_volume.pool_name, 1) = 0 then swap_volume.pool_name else
    substr(swap_volume.pool_name, 1, position('-', swap_volume.pool_name, 1) - 1) end as pool_name,
    volume,
    datediff('hour', first_liquidity_action.blocktime, swap_volume.blocktime) as hours_from_beginning
    from swap_volume
    left join first_liquidity_action on (first_liquidity_action.pool_name = swap_volume.pool_name)
    )
    select
    pool_name,
    sum(volume) as first_24h_volume,
    row_number() over (order by first_24h_volume desc) as rank
    from final_table
    where hours_from_beginning < 24
    group by pool_name
    Run a query to Download Data