Hodlers labelled

SandeshHodlers labelled

Updated 2025-01-27

-- why we cant use owner address in farcaster data Ex: 0xd22eae9404a1d99e75661e939a07e1992270f99c. An owner might have multiple adddresses.

-- we could filter out expired ens domain but we chose not to, bcuz the address will always be owned by that indiviudal and we can do with the last name. It's highly unlikely that another wolves user will buy the same ens name and become a member

WITH holders AS (

SELECT date_trunc('week',block_timestamp) as member_since,nft_to_address as member, tokenId

FROM polygon.nft.ez_nft_transfers

WHERE block_number >= '44071817' -- From a specific block onwards

AND nft_address = LOWER('0x0433882c60ada1077a9f652ca2d1d36422c62c6c') -- Target NFT contract address

QUALIFY ROW_NUMBER() OVER (PARTITION BY tokenId ORDER BY block_number DESC) = 1

)

, labelled_data as

(

select h.*, ens.ens_domain, coalesce(ens.EXPIRED, 'FALSE') as ens_status, parse_json(farc.verified_addresses)[0] as address, farc.display_name from holders h

left join ethereum.ens.ez_ens_domains ens

on h.member=ens.set_address

left join external.farcaster.dim_profile_with_addresses farc

on h.member=address

where 1=1

and member!='0x0000000000000000000000000000000000000000'

qualify(row_number() over (partition by h.member order by LAST_REGISTERED_BLOCK desc)=1)

)

select

coalesce(display_name, ens_domain, 'Name unavailable') as Wolf_name,

member as Wolf_address,

datediff('month',member_since, current_date) as wolf_age_in_months,

tokenId as badge_id,

case when Wolf_name ='Name unavailable' then 10

else 1

end as label_number

from labelled_data

order by wolf_age_in_months desc

Last run: 3 months ago

	WOLF_NAME	WOLF_ADDRESS	WOLF_AGE_IN_MONTHS	BADGE_ID	LABEL_NUMBER
1	Name unavailable	0x6e330ea9fbe7edea34a2c48394f4e840d9826d17	19	0	10
2	Name unavailable	0x48a206ffbd8b0bfde9f59a7f7a6668ae9f04de84	18	32	10
3	blockpal	0x221e438e4a8fc6569457bae62cbccdb8b5b02a93	18	27	1
4	Name unavailable	0xc1fd37ac279d984dc2641eb15a578d891ab22811	18	54	10
5	GreenGeorge 🎩	0x61c4a9107de33e75ed6fe29550ae2cc29ced2264	18	28	1
6	Name unavailable	0xeabec1f0890b68fae41353881ed0eeda8464b846	18	60	10
7	Name unavailable	0x83378c70b12be0431a36416622efd2e430b04628	18	98	10
8	otterbear	0xdf5416a43ee3ed5fb34503778a9b4d6dfb2f2449	18	95	1
9	Name unavailable	0x45e9d8b49693f6dad20010c1cd625bcaf3090dbb	18	80	10
10	Name unavailable	0x2411200bc60e5521c7e9f7d9aeba1af6cd575380	18	33	10
11	jpegss	0xd67d34abf19c075f23ed44b0f25d2761e43bc368	18	43	1
12	brownjob	0x69a1c8baf95ee4a36dcd6e9bcdaae42b055d4e82	18	61	1
13	Name unavailable	0x3096ce52ba75902240e73310af47264e22f23593	18	76	10
14	Name unavailable	0xb20b0a99562da14cb1acf910c42d879398a755ea	18	37	10
15	Name unavailable	0xd171e53f2b06be6a30f8854228c76d5424c587c4	18	72	10
16	sumerfyre	0xe50695a8ea96c5a6ecdb207f9642e1aaf80a004b	18	51	1
17	Name unavailable	0x4bdc28d8e216a9b9dc1c5b421d2475500ec707fb	18	22	10
18	Name unavailable	0x5a2ce6f1a5db43744dec8bede653b8c4da99daf3	18	41	10
19	Name unavailable	0x64830f69c98097fdda37498061658009f89908ee	18	3	10
20	Name unavailable	0xae4ee4e7cf0214fbb2d46468dede953aa8f500f2	18	8	10

...

184

13KB

flipside

flipside