rank()over(partition by payer ORDER BY block_timestamp) as rank

FROM flow.core.fact_transactions

WHERE block_timestamp::date >= '2022-04-20'

),

address as (SELECT *,

rank()over(partition by from_address ORDER BY block_timestamp) as rank

FROM ethereum.core.fact_transactions

WHERE block_timestamp::date >= '2022-04-20'

)

SELECT x.date,

x.period as flow,

y.period as eth

(

SELECT date_trunc('{{interval}}',b.block_timestamp) as date,

avg(datediff (hour,a.block_timestamp, b.block_timestamp)) as period

FROM payers a, payers b

WHERE a.payer = b.payer AND a.rank = b.rank -1

GROUP BY date

LEFT JOIN (

SELECT date_trunc('{{interval}}',b.block_timestamp) as date,

avg(datediff (hour,a.block_timestamp, b.block_timestamp)) as period

FROM address a, address b

WHERE a.from_address = b.from_address AND a.rank = b.rank -1

GROUP BY date)y

ON x.date = y.date