BigQuery queries for generating ENS airdrop data

airdrop.sql

--- 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;

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