Skip to content

Instantly share code, notes, and snippets.

@Arachnid
Last active November 6, 2023 04:26
Show Gist options
  • Save Arachnid/667178e854945abaecb6dfd3b6c0c279 to your computer and use it in GitHub Desktop.
Save Arachnid/667178e854945abaecb6dfd3b6c0c279 to your computer and use it in GitHub Desktop.
BigQuery queries for generating ENS airdrop data
--- Generate `ens-manager.airdrop.ownership_extents`, recording start time, end time, name, and owner tuples. ---
SELECT
transaction_hash,
event,
id,
block_timestamp AS start_time,
LEAST(LEAD(block_timestamp, 1, TIMESTAMP "9999-12-31 23:59:59.999999 UTC") OVER (PARTITION BY id ORDER BY block_timestamp, log_index), LAST_VALUE(TIMESTAMP_ADD(timestamp "1970-01-01 00:00:00+00", INTERVAL expires SECOND) IGNORE NULLS) OVER (PARTITION BY id ORDER BY block_timestamp, log_index)) AS end_time,
LAST_VALUE(owner IGNORE NULLS) OVER (PARTITION BY id ORDER BY block_timestamp, log_index) AS owner,
FROM (
SELECT
transaction_hash,
'registered' AS event,
`hash` AS id,
owner,
1588550400 AS expires,
block_timestamp,
log_index
FROM
`blockchain-etl.ethereum_ens.Registrar0_event_HashRegistered`
UNION ALL
SELECT
transaction_hash,
'transferred' AS event,
`ens-manager.airdrop.int_str_to_hash`(tokenId) AS id,
`to` AS owner,
NULL AS expires,
block_timestamp,
log_index
FROM
`blockchain-etl.ethereum_ens.BaseRegistrarImplementation_event_Transfer`
WHERE `from` != "0x0000000000000000000000000000000000000000"
UNION ALL
SELECT
transaction_hash,
'registered' AS event,
`ens-manager.airdrop.int_str_to_hash`(id) AS id,
owner,
CAST(expires AS int64) AS expires,
block_timestamp,
log_index
FROM
`blockchain-etl.ethereum_ens.BaseRegistrarImplementation_event_NameRegistered`
UNION ALL
SELECT
transaction_hash,
'renewed' AS event,
`ens-manager.airdrop.int_str_to_hash`(id) AS id,
NULL AS owner,
CAST(expires AS int64) AS expires,
block_timestamp,
log_index
FROM
`blockchain-etl.ethereum_ens.BaseRegistrarImplementation_event_NameRenewed`
UNION ALL
SELECT
transaction_hash,
'transferred' AS event,
`ens-manager.airdrop.int_str_to_hash`(tokenId) AS id,
`to` AS owner,
NULL AS expires,
block_timestamp,
log_index
FROM
`blockchain-etl.ethereum_ens.BaseRegistrarImplementation2_event_Transfer`
WHERE `from` != "0x0000000000000000000000000000000000000000"
UNION ALL
SELECT
transaction_hash,
'registered' AS event,
`ens-manager.airdrop.int_str_to_hash`(id) AS id,
owner,
CAST(expires AS int64) AS expires,
block_timestamp,
log_index
FROM
`blockchain-etl.ethereum_ens.BaseRegistrarImplementation2_event_NameRegistered`
UNION ALL
SELECT
transaction_hash,
'renewed' AS event,
`ens-manager.airdrop.int_str_to_hash`(id) AS id,
NULL AS owner,
CAST(expires AS int64) AS expires,
block_timestamp,
log_index
FROM
`blockchain-etl.ethereum_ens.BaseRegistrarImplementation2_event_NameRenewed`)
WHERE 1=1
QUALIFY end_time > start_time
ORDER BY id, start_time;
--- Generate `ens-manager.airdrop.reverse_accounts`, a list of accounts that have used the reverse registrar. ---
SELECT
DISTINCT from_address
FROM
`bigquery-public-data.crypto_ethereum.traces`
WHERE
to_address IN ('0x9062c0a6dbd6108336bcbe4593a3d1ce05512069',
'0x084b1c3c81545d370f3634392de611caabff8148');
--- Generate `ens-manager.airdrop.balances`, the original airdrop list. ---
SELECT
owner,
CAST(12500000 * 1e18 * ((past_duration * CASE WHEN has_reverse_record THEN 2 ELSE 1 END) / SUM(past_duration * CASE WHEN has_reverse_record THEN 2 ELSE 1 END) OVER ()) AS bignumeric) AS past_tokens,
longest_owned_name,
CAST(12500000 * 1e18 * ((future_duration * CASE WHEN has_reverse_record THEN 2 ELSE 1 END) / SUM(future_duration * CASE WHEN has_reverse_record THEN 2 ELSE 1 END) OVER ()) AS bignumeric) AS future_tokens,
last_expiring_name,
has_reverse_record
FROM (
SELECT
durations.owner,
LEAST(COALESCE(durations.total_duration, 0), 126230400) AS past_duration,
durations.longest_owned_name,
LEAST(COALESCE(TIMESTAMP_DIFF(renewals.last_expiry, timestamp '2021-11-01 00:00:00 UTC', SECOND), 0), 252460800) AS future_duration,
(
SELECT
ANY_VALUE(extents.id)
FROM
`ens-manager.airdrop.ownership_extents` AS extents
WHERE
extents.owner = renewals.owner
AND extents.end_time = renewals.last_expiry) AS last_expiring_name,
durations.owner IN (SELECT * FROM `ens-manager.airdrop.reverse_accounts`) AS has_reverse_record
FROM (
SELECT
owner,
ANY_VALUE(longest_owned_name) AS longest_owned_name,
SUM(duration) AS total_duration
FROM (
SELECT
LAST_VALUE(id) OVER (PARTITION BY owner ORDER BY end_time - start_time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS longest_owned_name,
TIMESTAMP_DIFF(LEAST(MAX(end_time) OVER (PARTITION BY owner ORDER BY start_time ROWS UNBOUNDED PRECEDING), LEAD(start_time, 1, TIMESTAMP '9999-12-31 23:59:59') OVER (PARTITION BY owner ORDER BY start_time), timestamp '2021-11-01 00:00:00 UTC'), start_time, SECOND) AS duration,
owner
FROM
`ens-manager.airdrop.ownership_extents`)
GROUP BY
owner) AS durations
LEFT JOIN (
SELECT
MAX(end_time) AS last_expiry,
owner
FROM
`ens-manager.airdrop.ownership_extents`
WHERE
end_time > timestamp '2021-11-01 00:00:00 UTC'
GROUP BY
owner ) AS renewals
ON
durations.owner = renewals.owner
WHERE
durations.owner NOT IN (SELECT address FROM `ens-manager.airdrop.blocklist`)
)
ORDER BY
past_tokens + future_tokens DESC
--- Generate `ens-manager.airdrop.resolver_ranges`, recording start and end time, name, and resolver tuples. ---
SELECT
node,
resolver,
block_timestamp AS start_time,
LEAD(block_timestamp, 1, timestamp '2021-11-01 00:00:00 UTC') OVER (PARTITION BY node ORDER BY block_timestamp, log_index) AS end_time
FROM (
SELECT
node,
resolver,
block_timestamp,
log_index,
0 AS deployment
FROM
`blockchain-etl.ethereum_ens.ENSRegistry_event_NewResolver`
UNION ALL
SELECT
node,
resolver,
block_timestamp,
log_index,
1 AS deployment
FROM
`blockchain-etl.ethereum_ens.ENSRegistryWithFallback_event_NewResolver`)
WHERE block_timestamp <= timestamp '2021-11-01 00:00:00 UTC'
QUALIFY deployment >= max(deployment) OVER (PARTITION BY node ORDER BY block_timestamp, log_index ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ORDER BY node, start_time;
--- Generate `ens-manager.airdrop.name_ranges`, recording start and end time, namehash, resolver, and resolved address. ---
SELECT
topics[ SAFE_OFFSET (1)] AS node,
address AS resolver,
CONCAT("0x", SUBSTR(DATA, 27)) AS addr,
block_timestamp AS start_time,
LEAD(block_timestamp, 1, timestamp '2021-11-01 00:00:00 UTC') OVER (PARTITION BY address, topics[SAFE_OFFSET(1)]
ORDER BY
block_timestamp,
log_index) AS end_time
FROM
`bigquery-public-data.crypto_ethereum.logs`
WHERE
topics[ SAFE_OFFSET (0)] = "0x52d7d861f09ab3d26239d492e8968629f95e9e318cf0b73bfddc441522a15fd2"
AND block_timestamp <= timestamp '2021-11-01 00:00:00 UTC'
ORDER BY
node,
resolver,
start_time
--- Generate `ens-manager.airdrop.name_ranges`, recording start and end time, namehash, resolver and primary name tuples. ---
CREATE TEMP FUNCTION
DECODE_NAME(data STRING)
RETURNS STRING
LANGUAGE js AS """ try { return ethers.utils.defaultAbiCoder.decode(['string'], data); } catch(e) { return null; } """ OPTIONS ( library="gs://blockchain-etl-bigquery/ethers.js" );
CREATE TEMP FUNCTION
DECODE_SET_NAME(data STRING)
RETURNS STRUCT<node STRING,
name STRING>
LANGUAGE js AS """ const abi = [ "function setName(bytes32 node, string name)" ]; const interface = new ethers.utils.Interface(abi); const args = interface.parseTransaction({data: data}).args; return {node: args[0], name: args[1]}; """ OPTIONS ( library="gs://blockchain-etl-bigquery/ethers.js" );
SELECT
node,
resolver,
name,
block_timestamp AS start_time,
LEAD(block_timestamp, 1, timestamp '2021-11-01 00:00:00 UTC') OVER (PARTITION BY node ORDER BY block_timestamp) AS end_time,
transaction_hash
FROM (
SELECT
transaction_hash,
address AS resolver,
topics[ SAFE_OFFSET (1)] AS node,
DECODE_NAME(DATA) AS name,
block_timestamp
FROM
`bigquery-public-data.crypto_ethereum.logs`
WHERE
topics[ SAFE_OFFSET (0)] = "0xb7d29e911041e8d9b843369e890bcb72c9388692ba48b65ac54e7214c4c348f7"
AND block_timestamp <= timestamp '2021-11-01 00:00:00 UTC'
UNION ALL
SELECT
transaction_hash,
resolver,
args.node AS node,
args.name AS name,
block_timestamp
FROM (
SELECT
transaction_hash,
to_address AS resolver,
DECODE_SET_NAME(input) AS args,
block_timestamp
FROM
`bigquery-public-data.crypto_ethereum.traces`
WHERE
to_address IN ('0x5fbb459c49bb06083c33109fa4f14810ec2cf358',
'0xa2c122be93b0074270ebee7f6b7292c7deb45047')
AND trace_type = 'call'
AND status = 1
AND block_timestamp <= timestamp '2021-11-01 00:00:00 UTC') AS trace_names ) names
ORDER BY node, start_time
--- Generate `ens-manager.airdrop.resolved_addrs`, combining `resolver_ranges` and `addr_ranges` to get time periods during which each name resolved to each address. ---
SELECT
resolvers.node AS node,
addr,
GREATEST(resolvers.start_time, addrs.start_time) AS start_time,
LEAST(resolvers.end_time, addrs.end_time) AS end_time
FROM
`ens-manager.airdrop.resolver_ranges` AS resolvers
INNER JOIN
`ens-manager.airdrop.addr_ranges` AS addrs
ON
resolvers.node = addrs.node
AND resolvers.resolver = addrs.resolver
AND GREATEST(resolvers.start_time, addrs.start_time) < LEAST(resolvers.end_time, addrs.end_time)
ORDER BY
node,
start_time;
--- Generate `ens-manager.airdrop.resolved_names`, combining `resolver_ranges` and `name_ranges` to get time periods during which a given reverse record resolved to a name. ---
SELECT
resolvers.node AS node,
name,
GREATEST(resolvers.start_time, names.start_time) AS start_time,
LEAST(resolvers.end_time, names.end_time) AS end_time
FROM
`ens-manager.airdrop.resolver_ranges` AS resolvers
INNER JOIN
`ens-manager.airdrop.name_ranges` AS names
ON
resolvers.node = names.node
AND resolvers.resolver = names.resolver
AND GREATEST(resolvers.start_time, names.start_time) < LEAST(resolvers.end_time, names.end_time)
--- Generate `ens-manager.airdrop.reverse_resolution_ranges`, combining `resolved_names` and `resolved_addrs` to get time periods during which a given account had a given primary name. ---
CREATE TEMP FUNCTION
NAMEHASH(data STRING)
RETURNS STRING
LANGUAGE js AS """ try { return ethers.utils.namehash(data); } catch(e) { return null; } """ OPTIONS ( library="gs://blockchain-etl-bigquery/ethers.js" );
SELECT
resolved_addrs.node AS node,
resolved_addrs.addr AS addr,
resolved_names.name AS name,
GREATEST(resolved_addrs.start_time, resolved_names.start_time) AS start_time,
LEAST(resolved_addrs.end_time, resolved_names.end_time) AS end_time
FROM
`ens-manager.airdrop.resolved_addrs` AS resolved_addrs
INNER JOIN
`ens-manager.airdrop.resolved_names` AS resolved_names
ON
resolved_names.node = namehash(CONCAT(SUBSTR(resolved_addrs.addr, 3), ".addr.reverse"))
AND resolved_addrs.node = namehash(resolved_names.name)
AND GREATEST(resolved_addrs.start_time, resolved_names.start_time) < LEAST(resolved_addrs.end_time, resolved_names.end_time)
ORDER BY
name,
start_time
--- Generate `ens-manager.airdrop.owners_with_reversed_accounts`, a list of airdrop-qualifying accounts that had a valid primary ENS name set at some point. ---
SELECT
DISTINCT owner
FROM
`ens-manager.airdrop.ownership_extents` AS ownership_extents
INNER JOIN
`ens-manager.airdrop.reverse_record_ranges` AS reverse_record_ranges
ON
reverse_record_ranges.node = `ens-manager.airdrop.make_subnode`("0x93cdeb708b7545dc668eb9280176169d1c33cfd8ed6f04690a0bcc88a93fc4ae", ownership_extents.id)
AND GREATEST(ownership_extents.start_time, reverse_record_ranges.start_time) < LEAST(ownership_extents.end_time, reverse_record_ranges.end_time)
--- Filter the original airdrop list for only accounts that didn't get the 2x multiplier, but would have with the new criteria. ---
--- Also include amounts for accidental transfers back to the token contract.
SELECT
owner,
SUM(past_tokens) AS past_tokens,
ANY_VALUE(longest_owned_name) AS longest_owned_name,
SUM(future_tokens) AS future_tokens,
ANY_VALUE(last_expiring_name) AS last_expiring_name,
ANY_VALUE(has_reverse_record) AS has_reverse_record
FROM (
SELECT
balances.*
FROM
`ens-manager.airdrop.balances` AS balances
INNER JOIN
`ens-manager.airdrop.owners_with_reversed_names` rn
ON
balances.owner = rn.owner
LEFT JOIN
`ens-manager.airdrop.reverse_accounts` ra
ON
balances.owner = ra.from_address
WHERE
ra.from_address IS NULL
UNION ALL
SELECT
from_address AS owner,
CAST(0 AS bignumeric) AS past_tokens,
NULL AS longest_owned_name,
CAST(value AS bignumeric) AS future_tokens,
NULL AS last_expiring_name,
NULL AS has_reverse_record
FROM
`bigquery-public-data.crypto_ethereum.token_transfers`
WHERE
token_address = "0xc18360217d8f7ab5e7c516566761ea12ce7f9d72"
AND to_address = "0xc18360217d8f7ab5e7c516566761ea12ce7f9d72"
AND from_address != "0x0000000000000000000000000000000000000000"
AND block_timestamp <= timestamp "2021-12-06 00:00:00 UTC")
GROUP BY
owner;
@amirkhan7javi
Copy link

Hello, I merge and confirm the ownership. Thank you for all your cooperation. I am considering transferring to a valid wallet.
It should take more time to check the profit now

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment