SELECT

from_address as address,

tx_hash,

block_timestamp,

LAG(block_timestamp) OVER (PARTITION BY from_address ORDER BY block_timestamp) AS prev_timestamp,

DATEDIFF(second,

prev_timestamp,

block_timestamp) AS seconds_diff

FROM {{chain}}.core.fact_transactions

WHERE block_timestamp > current_date() - {{daysago}}

),

HoursActive AS (

select address,

count(distinct(hour)) as n_hours_active

from (select address,

date_part(hour, block_timestamp) as hour,

count(tx_hash) as n_txns

from AddressStats

group by 1,2)

group by address

),

ThreshStats as (

SELECT

ha.address,

ha.n_hours_active,

txtime.txdiff_percentile_25,

txtime.txdiff_avg,

txtime.n_txns,

coalesce(cexaddys.type,'other') as type,

CASE

WHEN n_txns > {{tx_threshold}}

OR txdiff_percentile_25 < {{timediff_threshold}}

OR n_hours_active > {{hours_threshold}}

OR type = 'cex'