基于Redshift中ethereum_transaction_receipts表的事务数据窗口函数查询

Got it, let's dive into some actionable window function queries tailored to your Ethereum transaction receipts table in Redshift. These examples tackle common on-chain data analysis needs, using your table's existing columns:

1. Track cumulative transactions & interval per sender address #

This query lets you see how many times each address has sent transactions up to each individual tx, plus the time gap between consecutive transactions from the same sender.

SELECT
  hash AS transaction_hash,
  token_from AS sender_address,
  tx_time,
  block_number,
  -- Assign a running count of transactions per sender, ordered by time
  ROW_NUMBER() OVER (PARTITION BY token_from ORDER BY tx_time) AS tx_count_for_sender,
  -- Calculate seconds since the sender's last transaction
  EXTRACT(EPOCH FROM tx_time - LAG(tx_time) OVER (PARTITION BY token_from ORDER BY tx_time)) AS seconds_since_last_tx
FROM crypto_blockchains.ethereum_transaction_receipts
ORDER BY sender_address, tx_time;

• PARTITION BY token_from groups data by each sending address
• ROW_NUMBER() sequences transactions chronologically per sender
• LAG() pulls the previous transaction's timestamp to compute time gaps

2. Rank transactions within each block #

Use this to see the order of transactions inside each block, plus the total number of transactions in that block.

SELECT
  block_number,
  hash AS transaction_hash,
  tx_time,
  event_generator_address AS contract_address,
  -- Rank transactions by time within each block (ties get same rank)
  RANK() OVER (PARTITION BY block_number ORDER BY tx_time) AS tx_rank_in_block,
  -- Total transactions in the current block (no GROUP BY needed!)
  COUNT(*) OVER (PARTITION BY block_number) AS total_tx_in_block
FROM crypto_blockchains.ethereum_transaction_receipts
ORDER BY block_number, tx_rank_in_block;

• RANK() handles ties (e.g., transactions with identical timestamps) better than ROW_NUMBER()
• The windowed COUNT(*) returns the block's total tx count without collapsing rows

3. Rolling 7-day transaction count per event type #

This helps analyze trends in event activity by calculating a 7-day rolling total for each event hash.

SELECT
  tx_time::DATE AS transaction_date,
  event_hash,
  COUNT(hash) AS daily_tx_count,
  -- Sum daily counts over the past 7 days (current day + 6 prior days)
  SUM(COUNT(hash)) OVER (
    PARTITION BY event_hash
    ORDER BY tx_time::DATE
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS rolling_7day_tx_count
FROM crypto_blockchains.ethereum_transaction_receipts
WHERE event_hash IS NOT NULL
GROUP BY tx_time::DATE, event_hash
ORDER BY event_hash, transaction_date;

• First we aggregate daily tx counts per event, then apply the rolling window
• RANGE BETWEEN INTERVAL defines the 7-day time frame (Redshift supports this syntax)

4. Get first/last transaction details per address #

This query combines sender and recipient addresses to find the first, last, and total transactions each address was involved in.

WITH all_addresses AS (
  -- Combine senders and recipients into a single list of addresses
  SELECT token_from AS address, hash, tx_time, block_number FROM crypto_blockchains.ethereum_transaction_receipts
  UNION ALL
  SELECT token_to AS address, hash, tx_time, block_number FROM crypto_blockchains.ethereum_transaction_receipts
)
SELECT
  address,
  FIRST_VALUE(hash) OVER (PARTITION BY address ORDER BY tx_time) AS first_tx_hash,
  FIRST_VALUE(tx_time) OVER (PARTITION BY address ORDER BY tx_time) AS first_tx_time,
  -- Need to define full window for LAST_VALUE to work correctly
  LAST_VALUE(hash) OVER (
    PARTITION BY address
    ORDER BY tx_time
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_tx_hash,
  LAST_VALUE(tx_time) OVER (
    PARTITION BY address
    ORDER BY tx_time
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_tx_time,
  COUNT(*) OVER (PARTITION BY address) AS total_tx_involved
FROM all_addresses
GROUP BY address, hash, tx_time, block_number
ORDER BY total_tx_involved DESC;

• The CTE all_addresses unifies senders and recipients to capture every address's activity
• LAST_VALUE() requires explicit window bounds (otherwise it only looks up to the current row)

内容的提问来源于stack exchange，提问作者rajat