-- select * from ethereum.nft_events
-- where contract_address = '0x57f1887a8bf19b14fc0df6fd9b2acc9af147ea85'
-- limit 10
with tx_id_with_ID as (
select
block_timestamp as blocktime,
tx_id,
event_inputs:id as id
from
ethereum.events_emitted
where event_name = 'NameRegistered'
and id is not null
),
tx_id_with_name as (
select
tx_id,
event_inputs:name as name
from
ethereum.events_emitted
where event_name = 'NameRegistered'
and name is not null
),
id_with_name as (
select
blocktime,
id,
name,
row_number() over (partition by id order by blocktime desc) as rownumber,
case
when name is not null then name
else id end
as corrected_name
from
tx_id_with_ID
left join tx_id_with_name on tx_id_with_ID.tx_id = tx_id_with_name.tx_id
qualify rownumber = 1