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 toself.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))