Add a second query for some transaction details

The LedgerModelOutput object can be easily converted to a Pandas' DataFrame from its .data property. .to_dataframe() is handy to convert to a DataFrame. This is usefuly to work with bulk results. We will write more queries in this part.

As usual, we will test and refine our query in the Cmf console. For two notes,

• In the Cmf console, block_number variable is auto-created with the current block, equals to self.block_number.

• As of now (Oct 2022), there are some duplicates in our ledger tables, we use .drop_duplicates to remove them before this issue is resolved.

with self.context.ledger.Transaction as q:
    df_txs = (q.select(
        columns=[q.HASH,
                 q.FROM_ADDRESS,
                 q.TO_ADDRESS,
                 q.GAS,
                 q.GAS_PRICE],
        where=q.BLOCK_NUMBER.eq(block_number),
        order_by=q.TRANSACTION_INDEX)
        .to_dataframe()
        .drop_duplicates())

with self.context.ledger.Receipt as q:
    df_rts=(q.select(
        columns=[q.TRANSACTION_HASH,
                 q.CUMULATIVE_GAS_USED,
                 q.EFFECTIVE_GAS_PRICE,
                 q.GAS_USED],
        where=q.BLOCK_NUMBER.eq(block_number),
        order_by=q.TRANSACTION_INDEX)
        .to_dataframe()
        .drop_duplicates())

The console is an environment that we can work with data interactively. Below are some simple exploration of the data.

# number of receipts == number of transactions in a block
In [66]: df_rts.shape[0] == df_txs.shape[0]
Out[66]: True

# max gas used (from receipts).
# Note: In Ethereum, transactions and receipts are two tries.
#       Dune's transaction table combines transaction and recipts
In [67]: df_rts.gas_used.max()
Out[67]: 190510

# Cost of gas
In [68]: df_rts.gas_used * df_rts.effective_gas_price
Out[68]: 
0     2223140000000000
2      273000000000000
4     1296523984347556
6      552513846731888
8     1842755972744908
10     552859064047992
12     553229596996180
14     624838077402448
16     251006190708000
18     604212551690944
20     375111241515558
22     548161368045930
24     234624105219000
26    2128487537393890
28    1467238600851675
30    1262749765091243
32     231000000000000
34     228354077931000
36     449008890365156
38    2024865170220696
40    1870415548105348
dtype: Int64

# Sum of cost of gas
In [69]: (df_rts.gas_used * df_rts.effective_gas_price).sum() / 1e18
Out[69]: 0.019594095589409413

# Number of unique from and to addresses in this block
In [70]: len(set(df_txs.from_address) | set(df_txs.to_address))
Out[70]: 38

After getting our desired output, we move the code from console to our model. Note, you could use %history (a function from IPython) to retrieve the history of commands you have input to the console.

        with self.context.ledger.Transaction as q:
            df_txs = (q.select(
                columns=[q.HASH,
                         q.FROM_ADDRESS,
                         q.TO_ADDRESS,
                         q.GAS,
                         q.GAS_PRICE],
                where=q.BLOCK_NUMBER.gt(self.context.block_number - input.block_number_count),
                order_by=q.TRANSACTION_INDEX)
                .to_dataframe()
                .drop_duplicates())

        with self.context.ledger.Receipt as q:
            df_rts = (q.select(
                columns=[q.TRANSACTION_HASH,
                         q.CUMULATIVE_GAS_USED,
                         q.EFFECTIVE_GAS_PRICE,
                         q.GAS_USED],
                where=q.BLOCK_NUMBER.gt(self.context.block_number - input.block_number_count),
                order_by=q.TRANSACTION_INDEX)
                .to_dataframe()
                .drop_duplicates())

        # Max gas
        max_gas = int(df_rts.gas.max())

        # Total gas cost
        total_gas_cost = (df_rts.gas_used * df_rts.effective_gas_price).sum() / 1e18

        # Unique address in both from and to
        count_address = len(set(df_txs.from_address) | set(df_txs.to_address))