Skip to main content

SQL Friday – May 5th

· 2 min read
Nick Vernij

Time for another fun SQL query!

While it's $pepe szn, and gas is at an ATH, you wanna be the first to catch new contracts that are all the hype.

We're here to help, and created a SQL query that shows you recently deployed contracts, guzzling a lot of gas.

WITH deployed_contracts AS (
SELECT
contract_address
FROM
transaction
WHERE
block_number >= {{block_number}} AND contract_address IS NOT NULL
)
SELECT
dc.contract_address, SUM(t.gas_used) AS total_gas_used
FROM
transaction as t
JOIN
deployed_contracts as dc ON t.to = dc.contract_address
WHERE
block_number >= {{block_number}}
GROUP BY
dc.contract_address
ORDER BY
total_gas_used DESC
LIMIT 50

The query takes a list of newly deployed contracts after the given block_number. It then sums up all the gas of transactions going to these contracts. We took a snapshot of the past month, and at the top of the guzzlers, we find $pepe!

The snapshot below is taken with a block number 17010000. At the top we can find $pepe!

contract_addresstotal_gas_used
0x6982508145454ce325ddbe47a25d4ec3d23119336860242906
0xb3d03c68a4077366195510effee9f4ad90e84af45504474840
0x83ef3d446bd1220c8261251f83ac5cc51311d6003353246957
0x4d18548932c7564ec40889e9fd7bd44203c995582597457018
0x2805516b62af69c04368496588950cc4a75c6f342047324571
0x85c4209f7965d36742291f9222bd2cdb104df75c1820183819
0x4bd25d58869327446ee3a73d6021f51a4eb055dd1781123128
0xd4315668aa1d88b4c581ec6fa902e131286dd0ab1716085878
0x5026f006b85729a8b14553fae6af249ad16c9aab1398788492
0x9c22d5e2482bc5a753e508da75b5bb0f23682e361360187942

➡️ Create a Basement account to write and deploy queries yourself https://dashboard.basement.dev/auth/signup

See you next Friday folks ✌️