with Gaps AS (
SELECT
ORIGIN_FROM_ADDRESS as per,
block_timestamp as Swap_date,
LAG(block_timestamp) OVER (
PARTITION BY per
ORDER BY
block_timestamp
) AS prev_Swap_date
FROM
base.defi.ez_dex_swaps
where
platform = '{{DEX}}'
),
Swap_time as (
SELECT
per,
AVG(
DATEDIFF(hour, prev_Swap_date, Swap_date)
) AS avg_Swap_gap
FROM
Gaps
WHERE
prev_Swap_date IS NOT NULL
GROUP BY
per
ORDER BY
per
)
select
case
when avg_Swap_gap < 12 then 'A: < 12 Hours'
when avg_Swap_gap between 12
and 24 then 'B: 12-24 Hours'
when avg_Swap_gap between 24
and 72 then 'C: 1-3 Days'