value:"PROJECT_NAME"::string as project_name,

value:"LABEL_TYPE"::string as label_types

from (

SELECT livequery.live.udf_api(

'https://flipsidecrypto.xyz/api/v1/queries/9c85ebe3-1be9-4e0d-8972-6b3647dcad8e/data/latest') as resp

,LATERAL FLATTEN (input => resp:data)

UNION all

select address,

project_name,

case when label_type ilike '%nft%' then 'NFT'

when label_type ilike '%dex%' then 'DEX'

when label_type ilike '%token%' then 'TOKEN'

when label_type ilike '%blast%' then 'BRIDGE'

when label_type ilike '%defi%' then 'DEFI'

when label_type ilike '%game%' then 'GAME'

when label_type ilike '%dapp%' then 'DAPP' else label_type end as label_types

from blast.core.dim_labels

UNION ALL

select address,

project_name,

case when label_type ilike '%nft%' then 'NFT'

when label_type ilike '%dex%' then 'DEX'

when label_type ilike '%token%' then 'TOKEN'

when label_type ilike '%blast%' then 'BRIDGE'

when label_type ilike '%defi%' then 'DEFI'

when label_type ilike '%game%' then 'GAME'

when label_type ilike '%dapp%' then 'DAPP' else label_type end as label_types

from crosschain.core.dim_labels)

,

new as ( select min(block_timestamp::date) as day,

label_types,

from blast.core.ez_decoded_event_logs a join contract b on a.contract_address = b.address

group by 2,3)