eth_price as (

SELECT

DATE_TRUNC({{ duration }}, hour) AS time,

AVG(price) AS price

FROM

crosschain.price.ez_hourly_token_prices

WHERE

SYMBOL = 'WETH'

AND BLOCKCHAIN = 'ethereum'

GROUP BY

time

),

rake AS (

SELECT

DATE_TRUNC({{ duration }}, block_timestamp) AS time,

AVG(raw_amount) / POW(10, 18) AS total,

CASE

WHEN to_address = '0x77ceea82e4362dd3b2e0d7f76d0a71a628cad300' THEN 'maiden pool'

WHEN to_address = '0x6e45cfba3fedec1693e72783c1179f3c73253fc1' THEN 'zed cut'

END AS to_address

FROM

polygon.core.fact_token_transfers

WHERE

to_address IN (

'0x6e45cfba3fedec1693e72783c1179f3c73253fc1', -- Breeding collector 1

'0x77ceea82e4362dd3b2e0d7f76d0a71a628cad300' -- Breeding collector 2

)

-- ignore self-dealing

AND from_address NOT IN (

'0x6e45cfba3fedec1693e72783c1179f3c73253fc1', -- Breeding collector 1

'0x77ceea82e4362dd3b2e0d7f76d0a71a628cad300', -- Breeding collector 2

'0x45dda9cb7c25131df268515131f647d726f50608', -- USDT/WETH swap 1

'0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad', -- USDT/WETH swap 2