flipside

    11. Gas Guzzler (Top 500 Addresses)
    hess11. Gas Guzzler (Top 500 Addresses)
    Updated 2025-05-01
    with base as (
    select
    block_timestamp,
    tx_hash,
    from_address,
    to_address,
    value_precise,
    tx_fee
    from mezo.testnet.fact_transactions
    where block_timestamp >= '2025-02-19'
    ),

    fees_last_hours as (
    select
    to_address,
    sum(case when block_timestamp >= dateadd(hour, -24, current_timestamp) then tx_fee else 0 end) as total_fees_24h,
    sum(case when block_timestamp >= dateadd(hour, -12, current_timestamp) then tx_fee else 0 end) as total_fees_12h,
    sum(case when block_timestamp >= dateadd(hour, -1, current_timestamp) then tx_fee else 0 end) as total_fees_1h,
    count(case when block_timestamp >= dateadd(hour, -24, current_timestamp) then tx_hash else null end) as total_tx_24h
    from base
    group by to_address
    ),

    total_fees as (
    select sum(tx_fee) as total_network_fees from base
    )

    select
    b.to_address as "address",
    coalesce(d.name, d.symbol, c.name, c.symbol, 'Unknown') as label,
    count(distinct date_trunc('hour', b.block_timestamp)) as "active hours",
    count(distinct b.tx_hash) as "total txns",
    cast(f.total_tx_24h as string) as "total txns last 24 hours",
    cast(sum(b.value_precise) as string) as "BTC amount",
    cast(sum(b.tx_fee) as string) as "total fee (BTC)",
    cast(sum(b.tx_fee) / nullif(tf.total_network_fees, 0) * 100 as string) as "fee share (%)",
    Last run: about 1 month ago
    address
    LABEL
    active hours
    total txns
    total txns last 24 hours
    BTC amount
    total fee (BTC)
    fee share (%)
    total fees last 24 hours
    total fees last 12 hours
    total fees last 1 hour
    fee ratio
    avg fee (BTC)
    median fee (BTC)
    max fee (BTC)
    txns per active hour
    1
    		0x9c55214751472788a06797d88f38af1e79eee018Unknown55967101620000.053993829966.4645370871.223973403e-058.15386061e-069.639812e-128.046650566e-072.387966e-120.0006609960031120.037567
    2
    		0x20faea18b6a1d0fcdbccfffe3d164314744baf30BorrowerOperations78227902321620.5640739980.045851289195.4896524233.770637284e-053.016419885e-052.9787405e-110.0022296792551.643297584e-063.963505e-120.00086030550435.680307
    3
    		0xf74a1b73f615b41822fb4b2b875ace52dacdf5fbUnknown5532055031461100.021217450992.5403087520.00027078251311.722644428e-061.3439384e-111.032464294e-073.5077e-130.0001008150005371.614828
    4
    		0xd6626dcca8aa760b227e02b2391aaaecf9371c5dUnknown76021426180300.010147585981.2149433746.887269717e-066.100643852e-068.2581e-124.736108459e-071.3398e-120.000287100001328.192105
    5
    		0x637e22a1ebbca50ea2d34027c238317fd10003ebMezo USD78530591233900.0093582259461.1204354042.417886542e-062.184576877e-062.36898e-123.059143521e-073.25164e-137.012800033e-0538.969427
    6
    		0x9fbc2fdfbc6b5bbca9895e28e55eb8cc77c9cf9cUnknown45327791960.82885118620.0080234845380.96063037942.9063627e-101.82777002e-1000.0096802473982.887184073e-061.648624e-120.00030911700146.134658
    7
    		0x08c6f456724a8fe9c48c20430819c07a2a4b186eveBTC545000.0046485721370.55656112940000.00010330160313.101896e-120.00066469200319.000000
    8
    		0x7ec27d448c6437b4dc3561b3b33512680a179c3ftBTC540747644100.0025632836150.30689510283.417249646e-081.07391602e-102.05576e-133.428683273e-073.25276e-136.970200033e-0513.844444
    9
    		0xe6cfddb6592408663b1e8490c663693ad79523cbUnknown218000.001804098010.21599983740000.00010022766725.86068e-130.00063309000159.000000
    10
    		0x41c919d87382c9c14a3a1f6b378439abeda9f253veBTC1122000.0015387300520.18422804040006.994227508e-053.034724e-120.00088845000412.000000
    11
    		0x5eaa741552d6af40c7bf46467eaf79eea08eea9fWBTC519415423800.0012561894670.15040021068.7791808e-115.3929167e-112.04883e-133.024047825e-073.24583e-136.955350032e-058.003854
    12
    		0xd68308efbe8330a7bf58276edef51d91316e90b4USDT517418031000.0010635506880.1273360849.304707477e-089.301491128e-082.05576e-132.544379636e-073.25276e-136.988200033e-058.085106
    13
    		0xf08085ed33c0619113ee706fdb4b1b2c96137beeUSDC503347527900.0007088171210.084864781229.303450933e-089.300515909e-0802.039761499e-073.25276e-136.970200033e-056.908549
    14
    		0x7b7c000000000000000000000000000000000000BTC1971000.00034489801280.041293718130004.85771849e-061.78864e-133.832800018e-053.736842
    15
    		0xee05f70df64ddda6f9a13da060cee07e5c00bb05Unknown62195200.0021012899910.00017739168440.02123863274.582617e-124.127778e-1200.084420372799.097009455e-072.27633e-134.877850023e-053.145161
    16
    		0x66a471a1206d79e3e2975fca336cab57cf135f1dUnknown61100.0070696.30008417e-050.0075429225520000.0089122707175.727349246e-062.10147e-103.150000015e-051.833333
    17
    		0x46b7d79dd3dda7f2fd5e919f292cee4f6b009be0Unknown101700.01453956.300047353e-050.0075428784720000.00433305643.705910208e-062.247e-123.150000015e-051.700000
    18
    		0x07f52bf1eeae78d3203aca028b95aa9c32ed5555Unknown328100.00853194.208978907e-060.00050392980570000.00049332257845.196270256e-082.10147e-102.10000147e-072.531250
    19
    		0x4d88fb5a92040f3052798ba72d10668f7b081c9aUnknown21700.0071415383.150002499e-060.00037714138810000.00044108180881.852942646e-072.10000147e-072.10000147e-078.500000
    20
    		0x1b4f78e139b2742fd09c1a10c487d5f61883f2f7Unknown31200.0007339581.911001764e-060.00022879913840000.0026036936231.59250147e-072.10000147e-072.10000147e-074.000000
    ...
    500
    97KB
    38s