<jupyter_start><jupyter_text>Masters of the Great Web and Cyberpunks. ERC721 Analysis<jupyter_code>import pandas as pd
from tqdm.notebook import tqdm

from config import ETHERSCAN_NFT_CSV_NAME, PROJECT_ID, INITIAL_TS, SNAPSHOT_TS, \
    ERC721_ANALYSIS_DATASET_NAME, ERC721_AMOUNT_TABLE_NAME, ERC721_ANALYSIS_DISTRIBUTION_TABLE_NAME, \
    ERC721_NFT_TOKEN_TABLE_NAME, ERC721_TOKEN_TABLE_NAME, MASTERS_AUDIENCE, \
    CYBERPUNKS_AUDIENCE
from src.utils_bigquery import drop_table, create_table, get_df, create_table_from_df
from src.utils_charts import grade_boundaries_analysis
from src.extractor_nft_token_list import extract_nft_tokens
from src.utils_eth import get_contract_decimals

EXTRACT_NFT = False
DROP_TABLES = True
CREATE_TABLES = True<jupyter_output><empty_output><jupyter_text>Distribution Rule. Masters of the Great Web                                    Grade                            1            2            3                                     Amount of ERC721 tokens              > 0 NFT              > 7 NFT              > 185 NFT                              Owners of the Selected ERC721 tokens              -              ENS              Gitcoin Kudos or LAND                              Fee spending to contracts1, by contract creators, ETH              > 0 ETH              > 0.005 ETH              > 0.755 ETH             1 including contracts created by factories only Distribution Rule. Cyberpunks                                    Grade                            1            2            3                                     Owners of the Selected ERC721 tokens              -              -              Unicorns, DRAGON or Cryptopunks                              Urbit Owners              -              -              here<jupyter_code>min_number_of_tokens = 0

erc721_tokens_manual_grade_2_dict = {
    'ENS': '0x57f1887a8bf19b14fc0df6fd9b2acc9af147ea85'}
erc721_tokens_manual_grade_3_dict = {
    'Gitcoin Kudos': '0x2aea4add166ebf38b63d09a75de1a7b94aa24163',
    'LAND': '0xf87e31492faf9a91b02ee0deaad50d51d56d5d4d'}
erc721_tokens_manual_cyberpunks_dict = {
    'Unicorns': '0x89205a3a3b2a69de6dbf7f01ed13b2108b2c43e7',
    'DRAGON': '0x960f401aed58668ef476ef02b2a2d43b83c261d8',
    'Cryptopunks': '0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb'}

ENS_NAMEREGISTERED_EVENT_HASH = '0xb3d987963d01b2f68493b4bdb130988f157ea43070d4ad840fee0466ed9370d9'
LAND_TRANSFER_1_EVENT_HASH = '0x8988d59efc2c4547ef86c88f6543963bab0cea94f8e486e619c7c3a790db93be'
LAND_TRANSFER_2_EVENT_HASH = '0xd5c97f2e041b2046be3b4337472f05720760a198f4d7d84980b7155eec7cca6f'
CRYPTOPUNKS_ASSIGN_EVENT_HASH = '0x8a0e37b73a0d9c82e205d4d1a3ff3d0b57ce5f4d7bccf6bac03336dc101cb7ba'
MINT_EVENT_HASH = '0x0f6798a560793a54c3bcfe86a93cde1e73087d944c0ea20544137d4121396885'
BURN_EVENT_HASH = '0xcc16f5dbb4873280815c1ee09dbd06736cffcc184412cf7a71a0fdb75d397ca5'
CARD_CARDSMINTED_EVENT_HASH = '0x9c681932e4a9582af05182ce765050b6b731e429b839bcdf8463177531afdae5'
CK_BIRTH_EVENT_HASH = '0x0a5311bd2a6608f08a180df2ee7c5946819a649b204b554bb8e39825b2c50ad5'

INCOMING_EVENT_HASHES_DICT = {
    ENS_NAMEREGISTERED_EVENT_HASH: 2,
    LAND_TRANSFER_1_EVENT_HASH: 2,
    LAND_TRANSFER_2_EVENT_HASH: 2,
    CRYPTOPUNKS_ASSIGN_EVENT_HASH: 1,
    MINT_EVENT_HASH: 1,
    CARD_CARDSMINTED_EVENT_HASH: 2,
    CK_BIRTH_EVENT_HASH: 1
}
OUTGOING_EVENT_HASHES_DICT = {
    LAND_TRANSFER_1_EVENT_HASH: 1,
    LAND_TRANSFER_2_EVENT_HASH: 1,
    BURN_EVENT_HASH: 1
}
INCOMING_EVENT_HASHES_TUPLE = tuple(INCOMING_EVENT_HASHES_DICT.keys())
OUTGOING_EVENT_HASHES_TUPLE = tuple(OUTGOING_EVENT_HASHES_DICT.keys())
EVENT_HASHES_TUPLE = INCOMING_EVENT_HASHES_TUPLE + OUTGOING_EVENT_HASHES_TUPLE<jupyter_output><empty_output><jupyter_text>Set Token Data<jupyter_code># Get NFT list from Etherscan
if EXTRACT_NFT:
    extract_nft_tokens()

nft_tokens_df = pd.read_csv(ETHERSCAN_NFT_CSV_NAME, index_col=0)

erc721_tokens_manual_grade_2_tuple_str = str(tuple(erc721_tokens_manual_grade_2_dict.values())).replace(',)', ')')
erc721_tokens_manual_grade_3_tuple_str = str(tuple(erc721_tokens_manual_grade_3_dict.values())).replace(',)', ')')
erc721_tokens_manual_cyberpunks_tuple_str = str(tuple(erc721_tokens_manual_cyberpunks_dict.values())).replace(',)', ')')<jupyter_output><empty_output><jupyter_text>Create NFT Token Table<jupyter_code>if CREATE_TABLES:
    create_table_from_df(source_df=nft_tokens_df,
                         table_name=ERC721_NFT_TOKEN_TABLE_NAME,
                         dataset_name=ERC721_ANALYSIS_DATASET_NAME,
                         drop_existing_table=DROP_TABLES)<jupyter_output>Table erc721_analysis:nft_tokens has been deleted.
Table erc721_analysis:nft_tokens has been created.<jupyter_text>Create Aggregated ERC721 Token DataFrame<jupyter_code>query_1 = f'''
    WITH etherscan_nft_tokens AS (
        SELECT
            token_name AS name,
            token_address AS address,
            description,
            'etherscan' AS source
        FROM `{PROJECT_ID}.{ERC721_ANALYSIS_DATASET_NAME}.{ERC721_NFT_TOKEN_TABLE_NAME}`),
    erc721_manual_tokens AS (
        SELECT
            token.name AS name,
            token.address AS address,
            'manual' AS source
        FROM UNNEST(
            [{''.join(f"STRUCT('{k}' AS name,'{v}' AS address), " for k,v in {**erc721_tokens_manual_grade_2_dict, **erc721_tokens_manual_grade_3_dict, **erc721_tokens_manual_cyberpunks_dict}.items())[:-2]}]
            ) as token),
    erc721_ethereum_etl_tokens AS (
        SELECT
            address,
            'ethereum_etl' AS source
        FROM `bigquery-public-data.crypto_ethereum.contracts`
        WHERE is_erc721=True)

    SELECT
        address,
        symbol,
        COALESCE(token_names.name, etherscan_and_manual.name) as name,
        CASE
            WHEN ethereum_etl.source is null THEN etherscan_and_manual.source
            WHEN etherscan_and_manual.source is null THEN [ethereum_etl.source]
            ELSE array_concat(etherscan_and_manual.source, [ethereum_etl.source])
        END as source
    FROM (
        SELECT
            address,
            COALESCE(etherscan.name, manual.name) AS name,
            IF(COALESCE(etherscan.source, manual.source) = COALESCE(manual.source, etherscan.source),
               [COALESCE(etherscan.source, manual.source)],
               [COALESCE(etherscan.source, manual.source), COALESCE(manual.source, etherscan.source)]) AS source
        FROM etherscan_nft_tokens AS etherscan
        FULL OUTER JOIN erc721_manual_tokens as manual
        USING (address)
        ) as etherscan_and_manual
    FULL OUTER JOIN erc721_ethereum_etl_tokens as ethereum_etl
    USING (address)
    LEFT JOIN (
        SELECT
            address,
            ANY_VALUE(symbol) as symbol,
            ANY_VALUE(name) as name
        FROM `bigquery-public-data.crypto_ethereum.tokens`
        GROUP BY address) as token_names
    USING (address)
'''

erc721_tokens_df = get_df(query_1)<jupyter_output><empty_output><jupyter_text>Exclude ERC20 contracts<jupyter_code>token_addresses = erc721_tokens_df['address'].unique()

excluding_token_addresses = []
for token_address in tqdm(token_addresses):
    try:
        decimal = get_contract_decimals(
            token_address=token_address,
            print_messages=False)
        if decimal > 0:
            excluding_token_addresses.append(token_address)
    except ValueError:
        pass<jupyter_output><empty_output><jupyter_text>Create Aggregated ERC721 Token Table<jupyter_code>erc721_tokens_filtered_df = erc721_tokens_df[~erc721_tokens_df.address.isin(excluding_token_addresses)]
if CREATE_TABLES:
    create_table_from_df(source_df=erc721_tokens_filtered_df,
                         table_name=ERC721_TOKEN_TABLE_NAME,
                         dataset_name=ERC721_ANALYSIS_DATASET_NAME,
                         drop_existing_table=DROP_TABLES)<jupyter_output>Table erc721_analysis:erc721_tokens has been deleted.<jupyter_text>Get Balances<jupyter_code>query_2 = f'''
    WITH transfers AS (
      SELECT
          transaction_hash,
          token_address,
          from_address,
          to_address
      FROM `bigquery-public-data.crypto_ethereum.token_transfers`
      WHERE block_timestamp >= '{INITIAL_TS}'
        AND block_timestamp <= '{SNAPSHOT_TS}'
        AND token_address IN (
                SELECT address
                FROM `{PROJECT_ID}.{ERC721_ANALYSIS_DATASET_NAME}.{ERC721_TOKEN_TABLE_NAME}`
                )
      ),
    logs AS (
      SELECT
          transaction_hash,
          address as token_address,
          topics[ORDINAL(1)] as event_hash,
          topics
      FROM `bigquery-public-data.crypto_ethereum.logs`
      WHERE block_timestamp >= '{INITIAL_TS}'
        AND block_timestamp <= '{SNAPSHOT_TS}'
        AND address IN (
                SELECT address
                FROM `{PROJECT_ID}.{ERC721_ANALYSIS_DATASET_NAME}.{ERC721_TOKEN_TABLE_NAME}`
                )
      ),
    incoming_logs AS (
        SELECT
            transaction_hash,
            token_address,
            REPLACE(topics[ORDINAL(argument_number + 1)], '0x000000000000000000000000', '0x') as to_address,
            count(topics) as value
        FROM (
            SELECT
                transaction_hash,
                token_address,
                argument_number,
                topics,
            FROM logs
            INNER JOIN (
                SELECT
                    event_hash,
                    argument_number
                FROM UNNEST([{''.join(f"STRUCT('{k}' AS event_hash, {v} AS argument_number), " for k,v in INCOMING_EVENT_HASHES_DICT.items())[:-2]}])
                ) AS event_hashes
            USING (event_hash)
            WHERE ARRAY_LENGTH(topics) >= 2 )
        WHERE ARRAY_LENGTH(topics) >= argument_number + 1
        GROUP BY transaction_hash, token_address,to_address
      ),
    outgoing_logs AS (
        SELECT
            transaction_hash,
            token_address,
            REPLACE(topics[ORDINAL(argument_number + 1)], '0x000000000000000000000000', '0x') as from_address,
            count(topics) as value
        FROM (
            SELECT
                transaction_hash,
                token_address,
                argument_number,
                topics,
            FROM logs
            INNER JOIN (
                SELECT
                    event_hash,
                    argument_number
                FROM UNNEST([{''.join(f"STRUCT('{k}' AS event_hash, {v} AS argument_number), " for k,v in OUTGOING_EVENT_HASHES_DICT.items())[:-2]}])
                ) AS event_hashes
            USING (event_hash)
            WHERE ARRAY_LENGTH(topics) >= 2 )
        GROUP BY transaction_hash, token_address, from_address
      ),
    incoming_transfers AS (
        SELECT
            transaction_hash,
            token_address,
            to_address as address,
            incoming_logs.value as incoming_logs_value,
            incoming_transfers.value as incoming_transfers_value,
            IF(COALESCE(incoming_logs.value, 0) != COALESCE(incoming_transfers.value, 0), COALESCE(incoming_logs.value, 0) + COALESCE(incoming_transfers.value, 0), COALESCE(incoming_logs.value, 0)) as value
        FROM (
            SELECT
                transaction_hash,
                token_address,
                to_address,
                count(from_address) as value
            FROM transfers
            GROUP BY transaction_hash, token_address, to_address) AS incoming_transfers
        FULL OUTER JOIN incoming_logs
        USING (transaction_hash, token_address, to_address)
    ),
    outgoing_transfers AS (
        SELECT
            transaction_hash,
            token_address,
            from_address as address,
            outgoing_logs.value as outgoing_logs_value,
            outgoing_transfers.value as outgoing_transfers_value,
            IF(COALESCE(outgoing_logs.value, 0) != COALESCE(outgoing_transfers.value, 0), COALESCE(outgoing_logs.value, 0) + COALESCE(outgoing_transfers.value, 0), COALESCE(outgoing_logs.value, 0)) as value
        FROM (
            SELECT
                transaction_hash,
                token_address,
                from_address,
                count(from_address) as value
            FROM transfers
            GROUP BY transaction_hash, token_address, from_address) AS outgoing_transfers
        FULL OUTER JOIN outgoing_logs
        USING (transaction_hash, token_address, from_address)
    )

SELECT
    token_address,
    address,
    SUM(incoming_logs_value) as incoming_logs_value,
    SUM(incoming_transfers_value) as incoming_transfers_value,
    SUM(outgoing_logs_value) as outgoing_logs_value,
    SUM(outgoing_transfers_value) as outgoing_transfers_value,
    SUM(value) AS amount
FROM (
    SELECT
        token_address,
        address,
        incoming_logs_value,
        incoming_transfers_value,
        0 AS outgoing_logs_value,
        0 AS outgoing_transfers_value,
        value
    FROM incoming_transfers

    UNION ALL

    SELECT
        token_address,
        address,
        0 AS incoming_logs_value,
        0 AS incoming_transfers_value,
        - outgoing_logs_value,
        - outgoing_transfers_value,
        - value
    FROM outgoing_transfers)
GROUP BY token_address, address
'''

if DROP_TABLES:
    drop_table(table_name=ERC721_AMOUNT_TABLE_NAME,
               dataset_name=ERC721_ANALYSIS_DATASET_NAME)

if CREATE_TABLES:
    create_table(query=query_2,
                 table_name=ERC721_AMOUNT_TABLE_NAME,
                 dataset_name=ERC721_ANALYSIS_DATASET_NAME)<jupyter_output>Table erc721_analysis:erc721_amounts has been deleted.
Table erc721_analysis:erc721_amounts has been created and filled 1,315,862 rows.<jupyter_text>Analysis of Grade Boundaries. Amount of ERC721 tokens<jupyter_code>query_3 = f'''
    SELECT
        sum_amount,
        count(address) as number_of_addresses
    FROM (
        SELECT
            address,
            count(distinct token_address) as number_of_tokens,
            sum(amount) as sum_amount
        FROM `{PROJECT_ID}.{ERC721_ANALYSIS_DATASET_NAME}.{ERC721_AMOUNT_TABLE_NAME}`
        WHERE amount > 0
          AND address != '0x0000000000000000000000000000000000000000'
          AND address != token_address
        GROUP BY address
        HAVING number_of_tokens > {min_number_of_tokens})
    GROUP BY sum_amount
'''

address_agg_by_sum_amount_of_tokens_df = get_df(query_3)
boundary_erc721_amount = \
    grade_boundaries_analysis(
        distribution_df=address_agg_by_sum_amount_of_tokens_df,
        value_column = 'sum_amount',
        value_chart_label = 'Amount of ERC721 tokens by address, Log10',
        value_name = 'Amount of ERC721 tokens',
        chart_title = 'Distribution of Addresses by Amount of ERC721 Tokens',
        max_show_value = 5000)<jupyter_output>Suggestion of Rules:<jupyter_text>Analysis of Grade Boundaries. Fee spending to contractsDescribed in the [Extraordinary Hackers and Masters of the Great Web. Gas Analysis](gas__hackers_and_masters.ipynb) Jupyter notebook  Create Distribution Table for ERC721 Tokens<jupyter_code>query_4 = f'''
    WITH erc721_amount AS (
        SELECT
            address,
            count(distinct token_address) as number_of_tokens,
            ARRAY_AGG(DISTINCT token_address) as token_list,
            sum(amount) as sum_amount
        FROM `{PROJECT_ID}.{ERC721_ANALYSIS_DATASET_NAME}.{ERC721_AMOUNT_TABLE_NAME}`
        WHERE amount > 0
          AND address != '0x0000000000000000000000000000000000000000'
          AND address != token_address
        GROUP BY address),
    urbit_owners AS (
        SELECT
            owner,
            count(point) as sum_amount
        FROM `cosmic-keep-223223.erc721_analysis.azimuth_points`
        GROUP BY owner)
    SELECT
        '{MASTERS_AUDIENCE}' as audience,
        CASE
            WHEN (SELECT COUNT(1) FROM UNNEST(token_list) el WHERE el IN {erc721_tokens_manual_grade_3_tuple_str}) > 0 THEN 'Owners of the Selected ERC721 tokens'
            WHEN (SELECT COUNT(1) FROM UNNEST(token_list) el WHERE el IN {erc721_tokens_manual_grade_2_tuple_str}) > 0 AND sum_amount <= {boundary_erc721_amount[2]} THEN 'Owners of the Selected ERC721 tokens'
            ELSE 'Owners of ERC721 tokens'
        END
        AS segment,
        address,
        CASE
            WHEN sum_amount > {boundary_erc721_amount[2]} OR (SELECT COUNT(1) FROM UNNEST(token_list) el WHERE el IN {erc721_tokens_manual_grade_3_tuple_str}) > 0 THEN 3
            WHEN sum_amount > {boundary_erc721_amount[1]} OR (SELECT COUNT(1) FROM UNNEST(token_list) el WHERE el IN {erc721_tokens_manual_grade_2_tuple_str}) > 0 THEN 2
            WHEN sum_amount > {boundary_erc721_amount[0]} THEN 1
            ELSE null
        END
        AS grade,
        sum_amount,
        number_of_tokens
    FROM erc721_amount
    WHERE number_of_tokens > {min_number_of_tokens}

    UNION ALL

    SELECT
        '{CYBERPUNKS_AUDIENCE}' as audience,
        'Owners of the Selected ERC721 tokens' as segment,
        address,
        3 AS grade,
        sum_amount,
        number_of_tokens
    FROM erc721_amount
    WHERE (SELECT COUNT(1) FROM UNNEST(token_list) el WHERE el IN {erc721_tokens_manual_cyberpunks_tuple_str}) > 0

    UNION ALL

    SELECT
        '{CYBERPUNKS_AUDIENCE}' as audience,
        'Urbit Owners' as segment,
        owner as address,
        3 AS grade,
        sum_amount,
        1 as number_of_tokens
    FROM urbit_owners
'''

if DROP_TABLES:
    drop_table(table_name=ERC721_ANALYSIS_DISTRIBUTION_TABLE_NAME,
               dataset_name=ERC721_ANALYSIS_DATASET_NAME)

if CREATE_TABLES:
    create_table(query=query_4,
                 table_name=ERC721_ANALYSIS_DISTRIBUTION_TABLE_NAME,
                 dataset_name=ERC721_ANALYSIS_DATASET_NAME)<jupyter_output>Table erc721_analysis:erc721_analysis_distribution has been deleted.
Table erc721_analysis:erc721_analysis_distribution has been created and filled 805,995 rows.
{'fraction_non_alphanumeric': 0.06399198709031217, 'fraction_numerical': 0.056591174670302155, 'mean_word_length': 2.704803133374562, 'pattern_counts': {'":': 0, '<': 39, '<?xml version=': 0, '>': 60, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 4, 'word_list_counts': {'cursed_substrings.json': 20, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '443653', 'license': 'MIT', 'n_tokens_mistral': 6683, 'n_tokens_neox': 5926, 'n_words': 1065, 'path': 'erc721__masters_and_cyberpunks.ipynb', 'repo_name': 'maxim-uvarov/cybergift', 'size': '17971'}