Receipt p95 is a p95 of p95s

A percentile does not survive being percentiled again. Xatu's hourly receipt-size table has a column labelled p95_receipt_bytes_per_transaction, but on July 10 it never matched the p95 across transactions. The table value was 1.50x to 3.90x higher, with a median ratio of 2.67x across the 24 hourly buckets.

The data was not stale and the transaction denominator was not drifting. Both paths covered the same 2,823,931 canonical transactions. They were simply calculating different statistics under the same name.

The blue line is the ordinary question: put every transaction receipt in the hour into one distribution and take its 95th percentile. The orange line is what mainnet.fct_execution_receipt_size_hourly currently stores. At 23:00 UTC, the exact transaction p95 was 1,087 bytes; the table said 4,240 bytes.

That gap comes from the aggregation path. mainnet.int_transaction_receipt_size starts with one exact receipt size per transaction. The next table groups those transactions by block and computes a p95 inside each block. So far, fine. The hourly transformation then does this:

-- fct_execution_receipt_size_hourly
quantile(0.95)(p95_receipt_bytes_per_transaction)
    AS p95_receipt_bytes_per_transaction

The input field already is a block-level p95. The second quantile(0.95) takes the 95th percentile of roughly 300 block p95 values, giving a quiet block with 80 transactions the same weight as a busy block with 500. That is a valid statistic if the question is, "how ugly was the tail block?" It is not the p95 receipt size across transactions in the hour.

I checked the table against the exact transaction rows rather than trying to reverse-engineer the number from another aggregate:

WITH
blocks AS (
  SELECT
    block_number,
    toStartOfHour(block_date_time) AS hour
  FROM mainnet.int_execution_block_by_date FINAL
  WHERE block_date_time >= toDateTime('2026-07-10 00:00:00')
    AND block_date_time <  toDateTime('2026-07-11 00:00:00')
),
exact AS (
  SELECT
    b.hour,
    count() AS transactions,
    quantileExact(0.95)(t.receipt_bytes) AS exact_p95
  FROM mainnet.int_transaction_receipt_size AS t FINAL
  GLOBAL INNER JOIN blocks b ON t.block_number = b.block_number
  WHERE t.block_number BETWEEN 25498468 AND 25505640
  GROUP BY b.hour
)
SELECT
  e.hour,
  e.transactions,
  e.exact_p95,
  f.p95_receipt_bytes_per_transaction AS table_p95,
  round(table_p95 / exact_p95, 3) AS ratio
FROM exact e
GLOBAL INNER JOIN mainnet.fct_execution_receipt_size_hourly AS f FINAL
  ON e.hour = f.hour_start_date_time
WHERE f.hour_start_date_time >= toDateTime('2026-07-10 00:00:00')
  AND f.hour_start_date_time <  toDateTime('2026-07-11 00:00:00')
ORDER BY e.hour

The transaction count matched in all 24 hours, and the weighted hourly average receipt size matched too. The p95 matched in zero hours. Recomputing the hourly field from int_block_receipt_size as a p95 of block p95s reproduced 22 hours exactly; the other two differed by four and five bytes, the sort of tiny wobble an approximate quantile can produce when a bucket is recomputed.

I also compared ClickHouse's approximate quantile(0.95) directly over transactions with quantileExact(0.95). The largest hourly difference was 162 bytes, and the average absolute difference was 55 bytes. Approximation noise cannot explain a table value running 1,600 to 3,200 bytes high.

The raw canonical transaction table provided the last denominator check. Blocks 25,498,468 through 25,505,640 contained 2,823,931 rows and 2,823,931 unique transaction hashes, exactly matching the refined transaction-size path. This was not a duplicate-row story hiding under a percentile problem.

The transformation source does leave a warning in its header: the transaction percentile fields are "approximations derived from block-level percentile summaries." The public schema comment only says "95th percentile of receipt bytes per transaction," which drops the part that matters. An approximate quantile over the right rows would still answer the transaction question. A percentile over block percentiles answers another one.

The average fields in this table are safe because they are weighted back by each block's transaction count. For a real hourly transaction p95, query int_transaction_receipt_size over the bounded block range. If the block-tail distribution is what you want, use int_block_receipt_size and name it that way.

This does not change the old December receipt storm finding. That post used total receipt bytes and average log counts, not either percentile field. It does change how I would read any dashboard or query built on the hourly or daily transaction-p95 columns: the number describes the tail of blocks, not the tail of transactions.