SELECT

ORIGIN_FROM_ADDRESS,

MIN(BLOCK_TIMESTAMP) AS FirstTransaction

FROM

blast.core.ez_decoded_event_logs

GROUP BY

ORIGIN_FROM_ADDRESS

HAVING

MIN(BLOCK_TIMESTAMP) >= CURRENT_DATE - INTERVAL '60 DAY'

,

contract as ( Select VALUE:"ADDRESS"::string as address,

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 '%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'