avg(rune_usd) as rune_price

from thorchain.price.fact_prices

where block_timestamp >= '2024-01-01'

group by 1),

cex as (select label,

address

from thorchain.core.dim_labels

where label_type = 'cex'),

bots_1 as (select from_address,

block_timestamp,

date_trunc('hour', block_timestamp) as hours,

dateadd('hour', 2, hours) as two_hours

from thorchain.defi.fact_swaps),

bots_2 as (select

b.from_address,

hours,

two_hours,

count(distinct tx_id) as count_tx

from thorchain.defi.fact_swaps b

join bots_1 on b.block_timestamp >= hours and b.block_timestamp <= two_hours

group by 1,2,3

having count_tx >= 150),

table_1 as (select date_trunc('day', block_timestamp) as day,

sum(amount_e8/1e8) as rune_to_cex

from thorchain.core.fact_transfer_events a

where asset = 'THOR.RUNE'

and block_timestamp >= '2024-01-01'

and to_address in (select address from cex)

and from_address not in (select from_address from bots_2)

group by 1),