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 id ilike '%sloppy%' and (token_in_contract ilike '%sloppy%' or token_out_contract='%sloppy%') group by 1,2 order by 1 desc ), parsed_data_4 as ( SELECT trunc(recorded_hour,'day') AS date, 'PUSS' as token, avg(close)*1000000000 AS market_cap, avg(close) AS price, sum(case when token_in_contract ilike '%beaver%' then token_in_amount*close else token_out_amount*close end) AS volume FROM flow.price.fact_hourly_prices x join flow.defi.ez_swaps y on trunc(recorded_hour,'day')=trunc(block_timestamp,'day') where date=current_date-30 and id ilike '%beaver%' and (token_in_contract ilike '%beaver%' or token_out_contract='%beaver%') group by 1,2 order by 1 desc ), parsed_data_5 as ( SELECT trunc(recorded_hour,'day') AS date, 'BR' as token, avg(close)*63328 AS market_cap, avg(close) AS price, sum(case when token_in_contract ilike '%brasil%' then token_in_amount*close else token_out_amount*close end) AS volume FROM flow.price.fact_hourly_prices x join flow.defi.ez_swaps y on trunc(recorded_hour,'day')=trunc(block_timestamp,'day') where date=current_date-30 and id ilike '%brasil%' and (token_in_contract ilike '%brasil%' or token_out_contract='%brasil%') group by 1,2 order by 1 desc ), parsed_data_6 as ( SELECT trunc(recorded_hour,'day') AS date, 'GAME' as token, avg(close)*18934300 AS market_cap, avg(close) AS price, sum(case when token_in_contract ilike '%ofthegame%' then token_in_amount*close else token_out_amount*close end) AS volume FROM flow.price.fact_hourly_prices x join flow.defi.ez_swaps y on trunc(recorded_hour,'day')=trunc(block_timestamp,'day') where date=current_date-30 and id ilike '%ofthegame%' and (token_in_contract ilike '%ofthegame%' or token_out_contract='%ofthegame%') group by 1,2 order by 1 desc ), parsed_data_7 as ( SELECT trunc(recorded_hour,'day') AS date, 'UJLC' as token, avg(close)*10000 AS market_cap, avg(close) AS price, sum(case when token_in_contract ilike '%utah%' then token_in_amount*close else token_out_amount*close end) AS volume FROM flow.price.fact_hourly_prices x join flow.defi.ez_swaps y on trunc(recorded_hour,'day')=trunc(block_timestamp,'day') where date=current_date-30 and id ilike '%utah%' and (token_in_contract ilike '%utah%' or token_out_contract='%utah%') group by 1,2 order by 1 desc ), parsed_data_8 as ( SELECT trunc(recorded_hour,'day') AS date, 'DUCK' as token, avg(close)*20000 AS market_cap, avg(close) AS price, sum(case when token_in_contract ilike '%duck%' then token_in_amount*close else token_out_amount*close end) AS volume FROM flow.price.fact_hourly_prices x join flow.defi.ez_swaps y on trunc(recorded_hour,'day')=trunc(block_timestamp,'day') where date=current_date-30 and id ilike '%duck%' and (token_in_contract ilike '%duck%' or token_out_contract='%duck%') group by 1,2 order by 1 desc ), parsed_data_9 as ( SELECT trunc(recorded_hour,'day') AS date, 'GBTC' as token, avg(close)*2098000 AS market_cap, avg(close) AS price, sum(case when token_in_contract ilike '%green%' then token_in_amount*close else token_out_amount*close end) AS volume FROM flow.price.fact_hourly_prices x join flow.defi.ez_swaps y on trunc(recorded_hour,'day')=trunc(block_timestamp,'day') where date=current_date-30 and id ilike '%green%' and (token_in_contract ilike '%green%' or token_out_contract='%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