with bins as (
select
floor(diff_seconds/{{bin_size}})*{{bin_size}} as bin_floor,
count(*) as count_size
FROM (
SELECT
TIMESTAMPDIFF(SECOND,LAG(block_timestamp, 1) OVER (ORDER BY block_timestamp ASC),block_timestamp) AS diff_seconds
FROM polygon.core.fact_transactions
WHERE to_address = '0x2ab0e9e4ee70fff1fb9d67031e44f6410170d00e'
AND block_timestamp::date < CURRENT_DATE
ORDER BY block_timestamp ASC
) as core
group by bin_floor
HAVING COUNT(*) > 9 -- at least 10 transactions per bin
order by bin_floor
)
select
bin_floor,
bin_floor || ' - ' || (bin_floor + {{bin_size}}) as bin_range,
count_size
-- count_size/(SELECT COUNT(DISTINCT TX_HASH) FROM arbitrum.core.fact_token_transfers WHERE contract_address = '0xfc5a1a6eb076a2c7ad06ed22c90d7e710e35ad0a') * 100 as pct_size,
from bins
WHERE bin_floor < 10 -- within 10 seconds
order by bin_floor