binhachon8. [Hard] Trading by time - Base query
    Updated 2021-10-27
    -- select
    -- substr(pool_name, 1, position('.',pool_name,1) - 1) as chain,
    -- case when position('-', pool_name, 1) = 0 then substr(pool_name, position('.',pool_name,1) + 1) else
    -- substr(pool_name, position('.',pool_name,1) + 1, position('-', pool_name, 1) - position('.',pool_name,1) - 1) end as token
    -- from(
    with swap_table as(
    select block_timestamp,
    case when position('-', from_asset, 1) = 0 then substr(from_asset, position('.',from_asset,1) + 1) else
    substr(from_asset, position('.',from_asset,1) + 1, position('-', from_asset, 1) - position('.',from_asset,1) - 1) end as asset,
    from_amount_usd as amount_usd from thorchain.swaps
    union all
    select block_timestamp,
    case when position('-', to_asset, 1) = 0 then substr(to_asset, position('.',to_asset,1) + 1) else
    substr(to_asset, position('.',to_asset,1) + 1, position('-', to_asset, 1) - position('.',to_asset,1) - 1) end as asset,
    to_amount_min_usd as amount_usd from thorchain.swaps
    ),
    lp_action as(
    select block_timestamp, lp_action, 'RUNE' as asset, rune_amount_usd as amount_usd from thorchain.liquidity_actions
    union all
    select block_timestamp, lp_action,
    case when position('-', pool_name, 1) = 0 then substr(pool_name, position('.',pool_name,1) + 1) else
    substr(pool_name, position('.',pool_name,1) + 1, position('-', pool_name, 1) - position('.',pool_name,1) - 1) end as asset,
    asset_amount_usd as amount_usd
    from thorchain.liquidity_actions
    ),
    base_query as(
    select hour(block_timestamp::timestamp) as block_hour, asset, sum(amount_usd) as amount_usd from (
    select block_timestamp, asset, amount_usd from swap_table
    union all
    select block_timestamp, asset, amount_usd from lp_action
    )
    where block_timestamp > getdate() - interval'30 days'
    group by block_hour, asset
    ),
    index_table as(
    select block_hour, asset, amount_usd, row_number() over (partition by block_hour order by amount_usd desc) as row_number from base_query
    Run a query to Download Data