-- 1 - Meteora Top pools by TVL

select

livequery.live.udf_api('https://flipsidecrypto.xyz/api/v1/queries/bd3781ff-e6eb-475f-b3b3-c0c39bd838d3/data/latest'):data as response

union all

-- 2 - Orca Top pools by TVL

select

livequery.live.udf_api('https://flipsidecrypto.xyz/api/v1/queries/65621270-0ee7-4252-b866-de745b09c1e8/data/latest'):data as response

union all

-- 3 - Raydium CLMM top pools

select

livequery.live.udf_api('https://flipsidecrypto.xyz/api/v1/queries/a1769be3-906a-4988-990a-9befc7db2c31/data/latest'):data as response

union all

-- 4 - Raydium DLMM

select

livequery.live.udf_api('https://flipsidecrypto.xyz/api/v1/queries/e81086ee-1d65-4bd6-a11e-ccb08cbe7f3e/data/latest'):data as response

value:WEEK_::date as week_

, to_varchar(week_, 'YYYY-MM-DD') as week_char

--, value:WEEKLY_RANK as weekly_rank

, value:PROGRAM_NAME as program_name

, value:POOL_SLUG as pool_slug

, value:POOL_ADDRESS as pool_address

, value:POOL_TVL::numeric as tvl

, to_varchar(tvl, '999,999,999,999,999') as tvl_char

, row_number() over (partition by week_ order by tvl desc) as weekly_rank

from get_data