Skip to content

Instantly share code, notes, and snippets.

@Andrei-Dolgolev

Andrei-Dolgolev/contract_stats.md Secret

Last active January 26, 2023 16:33
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save Andrei-Dolgolev/5694d4b1cfa98bb70faaebc0ca3a94ba to your computer and use it in GitHub Desktop.
Save Andrei-Dolgolev/5694d4b1cfa98bb70faaebc0ca3a94ba to your computer and use it in GitHub Desktop.
Download ZIP
Raw
contract_stats.md

Terminus: '0x99A558BDBdE247C2B2716f0D4cFb0E246DFB697D'

All events per month:

query:

SELECT TO_CHAR(TO_TIMESTAMP(polygon_labels.block_timestamp), 'YYYY-MM') as block_month,
    count(polygon_labels.transaction_hash) as eventful_transactions
    FROM polygon_labels
WHERE polygon_labels.address = '0x99A558BDBdE247C2B2716f0D4cFb0E246DFB697D'
    AND polygon_labels.label = 'moonworm-alpha'
    AND polygon_labels.label_data->>'type' = 'event'
GROUP BY
    TO_CHAR(TO_TIMESTAMP(polygon_labels.block_timestamp), 'YYYY-MM');

result:

block_month | eventful_transactions
-------------+-----------------------
 2022-01     |                    94
 2022-02     |                  6195
 2022-03     |                 60679
 2022-04     |                  8460
 2022-05     |                 24407
 2022-06     |                 17306
 2022-07     |                  9992
 2022-08     |                 61872
 2022-09     |                 17091
 2022-10     |                 20038
 2022-11     |                 33316
 2022-12     |                 21426
 2023-01     |                 42346

Total number of transactions per month.

query:

SELECT TO_CHAR(TO_TIMESTAMP(polygon_labels.block_timestamp), 'YYYY-MM') as block_month,
    count(*) as transactions,
    sum(CASE
        WHEN polygon_labels.label_data->>'status' = '1' then 1 else 0 end
    ) as SUCCESS_TRANSACTIONS,
    ROUND((sum(CASE
        WHEN polygon_labels.label_data->>'status' = '1' then 1 else 0 end
    ) / cast(count(*) as float) * 100)::numeric, 2) as SUCCESS_RATE
    FROM polygon_labels
WHERE polygon_labels.address = '0x99A558BDBdE247C2B2716f0D4cFb0E246DFB697D'
    AND polygon_labels.label = 'moonworm-alpha'
    AND polygon_labels.label_data->>'type' = 'tx_call'
GROUP BY
    TO_CHAR(TO_TIMESTAMP(polygon_labels.block_timestamp), 'YYYY-MM');

results:

 block_month | transactions | success_transactions | success_rate
-------------+--------------+----------------------+--------------
 2022-01     |           11 |                   11 |       100.00
 2022-02     |          861 |                  852 |        98.95
 2022-03     |         6549 |                 6396 |        97.66
 2022-04     |         2033 |                 2006 |        98.67
 2022-05     |         4334 |                 4291 |        99.01
 2022-06     |         1251 |                 1242 |        99.28
 2022-07     |         1029 |                 1025 |        99.61
 2022-08     |         4348 |                 4328 |        99.54
 2022-09     |         1576 |                 1574 |        99.87
 2022-10     |         1937 |                 1933 |        99.79
 2022-11     |         3723 |                 3707 |        99.57
 2022-12     |         1809 |                 1751 |        96.79
 2023-01     |         2327 |                 2293 |        98.54

Total transfer like events:

query:

SELECT TO_CHAR(TO_TIMESTAMP(polygon_labels.block_timestamp), 'YYYY-MM') as block_month,
    count(polygon_labels.transaction_hash) as eventful_transactions
    FROM polygon_labels
WHERE polygon_labels.address = '0x99A558BDBdE247C2B2716f0D4cFb0E246DFB697D'
    AND polygon_labels.label = 'moonworm-alpha'
    AND polygon_labels.label_data->>'type' = 'event'
    AND polygon_labels.label_data->>'name' LIKE '%Transfer%'
GROUP BY
    TO_CHAR(TO_TIMESTAMP(polygon_labels.block_timestamp), 'YYYY-MM');

result:

 block_month | eventful_transactions
-------------+-----------------------
 2022-01     |                    84
 2022-02     |                  5806
 2022-03     |                 57264
 2022-04     |                  7221
 2022-05     |                 23368
 2022-06     |                 17067
 2022-07     |                  9898
 2022-08     |                 61565
 2022-09     |                 16696
 2022-10     |                 19747
 2022-11     |                 32892
 2022-12     |                 21160
 2023-01     |                 42106

Dark forest: 0x8d528e98A69FE27b11bb02Ac264516c4818C3942

All events per month:

query:

SELECT TO_CHAR(TO_TIMESTAMP(polygon_labels.block_timestamp), 'YYYY-MM') as block_month,
    count(polygon_labels.transaction_hash) as eventful_transactions
    FROM polygon_labels
WHERE polygon_labels.address = '0x8d528e98A69FE27b11bb02Ac264516c4818C3942'
    AND polygon_labels.label = 'moonworm-alpha'
    AND polygon_labels.label_data->>'type' = 'event'
GROUP BY
    TO_CHAR(TO_TIMESTAMP(polygon_labels.block_timestamp), 'YYYY-MM');

result:

 block_month | eventful_transactions
-------------+-----------------------
 2022-01     |                 32486
 2022-02     |                406781
 2022-03     |                353938
 2022-04     |                  5589
 2022-05     |                   125
 2022-06     |                    24
 2022-11     |                     5

Total number of transactions per month.

query:

SELECT TO_CHAR(TO_TIMESTAMP(polygon_labels.block_timestamp), 'YYYY-MM') as block_month,
    count(*) as transactions,
    sum(CASE
        WHEN polygon_labels.label_data->>'status' = '1' then 1 else 0 end
    ) as SUCCESS_TRANSACTIONS,
    ROUND((sum(CASE
        WHEN polygon_labels.label_data->>'status' = '1' then 1 else 0 end
    ) / cast(count(*) as float) * 100)::numeric, 2) as SUCCESS_RATE
    FROM polygon_labels
WHERE polygon_labels.address = '0x8d528e98A69FE27b11bb02Ac264516c4818C3942'
    AND polygon_labels.label = 'moonworm-alpha'
    AND polygon_labels.label_data->>'type' = 'tx_call'
GROUP BY
    TO_CHAR(TO_TIMESTAMP(polygon_labels.block_timestamp), 'YYYY-MM');

results:

 block_month | transactions | success_transactions | success_rate
-------------+--------------+----------------------+--------------
 2022-01     |        13685 |                12785 |        93.42
 2022-02     |       198396 |               191733 |        96.64
 2022-03     |       167026 |               163301 |        97.77
 2022-04     |          156 |                  151 |        96.79
 2022-05     |          130 |                  124 |        95.38
 2022-06     |           24 |                   24 |       100.00
 2022-11     |            5 |                    5 |       100.00

Transfer to darkforests events:

query:

SELECT TO_CHAR(TO_TIMESTAMP(polygon_labels.block_timestamp), 'YYYY-MM') as block_month,
    count(polygon_labels.transaction_hash) as eventful_transactions
    FROM polygon_labels
WHERE polygon_labels.label = 'moonworm-alpha'
    AND polygon_labels.label_data->>'type' = 'event'
    AND polygon_labels.label_data->>'name' LIKE '%Transfer%'
    AND polygon_labels.label_data->'args'->>'to' = '0x8d528e98A69FE27b11bb02Ac264516c4818C3942'
    or polygon_labels.label_data->'args'->>'from' = '0x8d528e98A69FE27b11bb02Ac264516c4818C3942'
GROUP BY
    TO_CHAR(TO_TIMESTAMP(polygon_labels.block_timestamp), 'YYYY-MM');

results:

 block_month | eventful_transactions
-------------+-----------------------
 2022-01     |                 57569
 2022-02     |                792406
 2022-03     |                715089
 2022-04     |                  8647
 2022-05     |                   248
 2022-06     |                    48
 2022-11     |                    10

Dropper: 0x6bc613A25aFe159b70610b64783cA51C9258b92e

All events per month:

query:

SELECT TO_CHAR(TO_TIMESTAMP(polygon_labels.block_timestamp), 'YYYY-MM') as block_month,
    count(polygon_labels.transaction_hash) as eventful_transactions
    FROM polygon_labels
WHERE polygon_labels.address = '0x6bc613A25aFe159b70610b64783cA51C9258b92e'
    AND polygon_labels.label = 'moonworm-alpha'
    AND polygon_labels.label_data->>'type' = 'event'
GROUP BY
    TO_CHAR(TO_TIMESTAMP(polygon_labels.block_timestamp), 'YYYY-MM');

query:

 block_month | eventful_transactions
-------------+-----------------------
 2022-04     |                  7074
 2022-05     |                 30827
 2022-06     |                 47433
 2022-07     |                 15379
 2022-08     |                 24542
 2022-09     |                 11801
 2022-10     |                  9017
 2022-11     |                 11619
 2022-12     |                  7434
 2023-01     |                 18861

Total number of transactions per month.

query:

SELECT TO_CHAR(TO_TIMESTAMP(polygon_labels.block_timestamp), 'YYYY-MM') as block_month,
    count(*) as transactions,
    sum(CASE
        WHEN polygon_labels.label_data->>'status' = '1' then 1 else 0 end
    ) as SUCCESS_TRANSACTIONS,
    ROUND((sum(CASE
        WHEN polygon_labels.label_data->>'status' = '1' then 1 else 0 end
    ) / cast(count(*) as float) * 100)::numeric, 2) as SUCCESS_RATE
    FROM polygon_labels
WHERE polygon_labels.address = '0x6bc613A25aFe159b70610b64783cA51C9258b92e'
    AND polygon_labels.label = 'moonworm-alpha'
    AND polygon_labels.label_data->>'type' = 'tx_call'
GROUP BY
    TO_CHAR(TO_TIMESTAMP(polygon_labels.block_timestamp), 'YYYY-MM');

results:

 block_month | transactions | success_transactions | success_rate
-------------+--------------+----------------------+--------------
 2022-04     |         7228 |                 7078 |        97.92
 2022-05     |        31464 |                30827 |        97.98
 2022-06     |        48441 |                47433 |        97.92
 2022-07     |        15620 |                15379 |        98.46
 2022-08     |        24961 |                24553 |        98.37
 2022-09     |        11927 |                11801 |        98.94
 2022-10     |         9395 |                 9016 |        95.97
 2022-11     |        11967 |                11619 |        97.09
 2022-12     |         7685 |                 7435 |        96.75
 2023-01     |        19243 |                18862 |        98.02

Transfer like events per month:

query:

SELECT TO_CHAR(TO_TIMESTAMP(polygon_labels.block_timestamp), 'YYYY-MM') as block_month,
    count(*) as transactions,
    sum(CASE
        WHEN polygon_labels.label_data->>'status' = '1' then 1 else 0 end

    ) as SUCCESS_TRANSACTIONS
    FROM polygon_labels
WHERE polygon_labels.address = '0x6bc613A25aFe159b70610b64783cA51C9258b92e'
    AND polygon_labels.label = 'moonworm-alpha'
    AND polygon_labels.label_data->>'type' = 'tx_call'
GROUP BY
    TO_CHAR(TO_TIMESTAMP(polygon_labels.block_timestamp), 'YYYY-MM');

results:

 block_month | transactions | success_transactions
-------------+--------------+----------------------
 2022-04     |         7228 |                 7078
 2022-05     |        31464 |                30827
 2022-06     |        48441 |                47433
 2022-07     |        15620 |                15379
 2022-08     |        24961 |                24553
 2022-09     |        11927 |                11801
 2022-10     |         9395 |                 9016
 2022-11     |        11967 |                11619
 2022-12     |         7685 |                 7435
 2023-01     |        19243 |                18862

Transfers events

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