SELECT

LOWER(coin.value:symbol::string) AS "Coin Symbol Lower",

LOWER(coin.value:name::string) AS "Coin Name Lower"

FROM (

SELECT livequery.live.udf_api(

'https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&category=gaming&order=market_cap_desc&per_page=10&page=1'

) AS coin_data

),

LATERAL FLATTEN(input => coin_data:data) coin

l."Coin Symbol Lower" AS "Coin Symbol",

COUNT(DISTINCT s.tx_hash) AS "Distinct Tx Hash Count"

FROM live_data l

JOIN crosschain.defi.ez_dex_swaps s

ON l."Coin Symbol Lower" = LOWER(s.symbol_in)

GROUP BY l."Coin Symbol Lower"

ORDER BY "Distinct Tx Hash Count" DESC;