WITH parsed_data AS ( SELECT dateadd(ms, market_caps.value[0], to_timestamp('1970-01-01')) AS date, 'FLOW' as token, avg(market_caps.value[1]) AS market_cap, avg(prices.value[1]) AS price, avg(total_volumes.value[1]) AS volume FROM ( SELECT livequery.live.udf_api( 'https://api.coingecko.com/api/v3/coins/flow/market_chart?vs_currency=usd&days=30&interval=daily') as resp ), lateral flatten(input => resp:data:market_caps) AS market_caps, lateral flatten(input => resp:data:prices) AS prices, lateral flatten(input => resp:data:total_volumes) AS total_volumes where date resp:data:market_caps) AS market_caps, lateral flatten(input => resp:data:prices) AS prices, lateral flatten(input => resp:data:total_volumes) AS total_volumes where date resp:data:market_caps) AS market_caps, lateral flatten(input => resp:data:prices) AS prices, lateral flatten(input => resp:data:total_volumes) AS total_volumes where date resp:data:market_caps) AS market_caps, lateral flatten(input => resp:data:prices) AS prices, lateral flatten(input => resp:data:total_volumes) AS total_volumes where date resp:data:market_caps) AS market_caps, lateral flatten(input => resp:data:prices) AS prices, lateral flatten(input => resp:data:total_volumes) AS total_volumes where date resp:data:prices) AS prices, lateral flatten(input => resp:data:total_volumes) AS total_volumes where date resp:data:prices) AS prices, lateral flatten(input => resp:data:total_volumes) AS total_volumes where date=current_date-30 and token_address ilike '%sloppy%' and (token_in ilike '%sloppy%' or token_out='%sloppy%') group by 1,2 order by 1 desc ), parsed_data_4 as ( SELECT trunc(hour,'day') AS date, 'PUSS' as token, avg(price)*1000000000 AS market_cap, avg(price) AS price, sum(case when token_in ilike '%beaver%' then amount_in*price else amount_out*price end) AS volume FROM flow.price.ez_prices_hourly x join flow.defi.ez_dex_swaps y on trunc(hour,'day')=trunc(block_timestamp,'day') where date=current_date-30 and token_address ilike '%beaver%' and (token_in ilike '%beaver%' or token_out='%beaver%') group by 1,2 order by 1 desc ), parsed_data_5 as ( SELECT trunc(hour,'day') AS date, 'BR' as token, avg(price)*63328 AS market_cap, avg(price) AS price, sum(case when token_in ilike '%brasil%' then amount_in*price else amount_out*price end) AS volume FROM flow.price.ez_prices_hourly x join flow.defi.ez_dex_swaps y on trunc(hour,'day')=trunc(block_timestamp,'day') where date=current_date-30 and token_address ilike '%brasil%' and (token_in ilike '%brasil%' or token_out='%brasil%') group by 1,2 order by 1 desc ), parsed_data_6 as ( SELECT trunc(hour,'day') AS date, 'GAME' as token, avg(price)*18934300 AS market_cap, avg(price) AS price, sum(case when token_in ilike '%ofthegame%' then amount_in*price else amount_out*price end) AS volume FROM flow.price.ez_prices_hourly x join flow.defi.ez_dex_swaps y on trunc(hour,'day')=trunc(block_timestamp,'day') where date=current_date-30 and token_address ilike '%ofthegame%' and (token_in ilike '%ofthegame%' or token_out='%ofthegame%') group by 1,2 order by 1 desc ), parsed_data_7 as ( SELECT trunc(hour,'day') AS date, 'UJLC' as token, avg(price)*10000 AS market_cap, avg(price) AS price, sum(case when token_in ilike '%utah%' then amount_in*price else amount_out*price end) AS volume FROM flow.price.ez_prices_hourly x join flow.defi.ez_dex_swaps y on trunc(hour,'day')=trunc(block_timestamp,'day') where date=current_date-30 and token_address ilike '%utah%' and (token_in ilike '%utah%' or token_out='%utah%') group by 1,2 order by 1 desc ), parsed_data_8 as ( SELECT trunc(hour,'day') AS date, 'DUCK' as token, avg(price)*20000 AS market_cap, avg(price) AS price, sum(case when token_in ilike '%duck%' then amount_in*price else amount_out*price end) AS volume FROM flow.price.ez_prices_hourly x join flow.defi.ez_dex_swaps y on trunc(hour,'day')=trunc(block_timestamp,'day') where date=current_date-30 and token_address ilike '%duck%' and (token_in ilike '%duck%' or token_out='%duck%') group by 1,2 order by 1 desc ), parsed_data_9 as ( SELECT trunc(hour,'day') AS date, 'GBTC' as token, avg(price)*2098000 AS market_cap, avg(price) AS price, sum(case when token_in ilike '%green%' then amount_in*price else amount_out*price end) AS volume FROM flow.price.ez_prices_hourly x join flow.defi.ez_dex_swaps y on trunc(hour,'day')=trunc(block_timestamp,'day') where date=current_date-30 and token_address ilike '%green%' and (token_in ilike '%green%' or token_out='%green%') group by 1,2 order by 1 desc ) SELECT distinct token, max(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 24h", AVG(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 24h", AVG(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 24h", max(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 7d", AVG(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 7d", SUM(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 7d", max(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 30d", AVG(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 30d", SUM(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 30d" FROM parsed_data GROUP BY 1 UNION select distinct token, max(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 24h", AVG(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 24h", AVG(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 24h", max(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 7d", AVG(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 7d", SUM(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 7d", max(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 30d", AVG(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 30d", SUM(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 30d" FROM parsed_data_2 GROUP BY 1 UNION select distinct token, max(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 24h", AVG(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 24h", AVG(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 24h", max(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 7d", AVG(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 7d", SUM(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 7d", max(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 30d", AVG(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 30d", SUM(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 30d" FROM parsed_data_3 GROUP BY 1 UNION select distinct token, max(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 24h", AVG(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 24h", AVG(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 24h", max(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 7d", AVG(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 7d", SUM(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 7d", max(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 30d", AVG(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 30d", SUM(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 30d" FROM parsed_data_4 GROUP BY 1 UNION select distinct token, max(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 24h", AVG(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 24h", AVG(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 24h", max(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 7d", AVG(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 7d", SUM(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 7d", max(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 30d", AVG(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 30d", SUM(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 30d" FROM parsed_data_5 GROUP BY 1 UNION select distinct token, max(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 24h", AVG(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 24h", AVG(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 24h", max(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 7d", AVG(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 7d", SUM(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 7d", max(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 30d", AVG(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 30d", SUM(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 30d" FROM parsed_data_6 GROUP BY 1 UNION select distinct token, max(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 24h", AVG(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 24h", AVG(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 24h", max(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 7d", AVG(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 7d", SUM(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 7d", max(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 30d", AVG(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 30d", SUM(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 30d" FROM parsed_data_7 GROUP BY 1 UNION select distinct token, max(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 24h", AVG(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 24h", AVG(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 24h", max(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 7d", AVG(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 7d", SUM(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 7d", max(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 30d", AVG(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 30d", SUM(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 30d" FROM parsed_data_8 GROUP BY 1 UNION select distinct token, max(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 24h", AVG(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 24h", AVG(CASE WHEN date >= DATEADD(DAY, -1, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 24h", max(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 7d", AVG(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 7d", SUM(CASE WHEN date >= DATEADD(DAY, -7, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 7d", max(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN market_cap ELSE 0 END) AS "MarketCap 30d", AVG(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN price ELSE NULL END) AS "Price 30d", SUM(CASE WHEN date >= DATEADD(DAY, -30, CURRENT_DATE()) THEN volume ELSE 0 END) AS "Trading volume 30d" FROM parsed_data_9 GROUP BY 1 ORDER BY 2 DESC

Flow Ecosystem Megadashboard