Flipside TeamRef Finance Revenue
    Updated 2024-11-14
    with tb1 as ( select trunc(hour,'day') as day,
    'aurora' as token_address,
    avg(price) as avg_price
    from near.price.ez_prices_hourly
    where symbol = 'ETH'
    group by 1,2
    UNION
    select trunc(hour,'day') as day,
    '3.contract.portalbridge.near' as token_address,
    1 as avg_price
    from near.price.ez_prices_hourly
    where symbol = 'ETH'
    UNION
    Select trunc(TO_TIMESTAMP(value[0]::string),'day') as day,
    'usmeme.tg' as token_address,
    avg(value[1]) as avg_price
    from (
    SELECT livequery.live.udf_api(
    'https://api.coingecko.com/api/v3/coins/usmeme/market_chart?vs_currency=usd') as resp
    )
    ,LATERAL FLATTEN (input => resp:data:prices)
    group by all
    UNION
    Select trunc(TO_TIMESTAMP(value[0]::string),'day') as day,
    'token.jumbo_exchange.near' as token_address,
    avg(value[1]) as avg_price
    from (
    SELECT livequery.live.udf_api(
    'https://api.coingecko.com/api/v3/coins/jumbo-exchange/market_chart?vs_currency=usd') as resp
    )
    ,LATERAL FLATTEN (input => resp:data:prices)
    group by all
    UNION
    Select trunc(TO_TIMESTAMP(value[0]::string),'day') as day,
    'celo.token.a11bd.near' as token_address,
    avg(value[1]) as avg_price
    Last run: 3 months ago
    Swappers
    Swaps
    Volume
    PROTOCOL_FEES
    PROTOCOL_REVENUE
    1
    948597992077911500706935750353.4651150070.693
    1
    53B
    125s