prices AS (

SELECT

trunc(block_timestamp, 'month') AS date,

avg(asset_usd) AS price

FROM thorchain.price.fact_prices

WHERE block_timestamp >= trunc(current_date, 'month') - interval '1 year'

AND pool_name = 'ETH.THOR-0XA5F2211B9B8170F694421F2046281775E8468044'

GROUP BY 1

),

table1 AS (

SELECT DISTINCT tx_hash

FROM ethereum.core.fact_decoded_event_logs

WHERE block_timestamp >= '2022-05-01'

AND contract_address = LOWER('0xa5f2211B9b8170F694421F2046281775E8468044')

),

table2 AS (

SELECT DISTINCT e.tx_hash

FROM ethereum.core.fact_decoded_event_logs e

JOIN table1 a ON a.tx_hash = e.tx_hash

WHERE e.block_timestamp >= '2022-05-01'

AND decoded_log:to = LOWER('0x815c23eca83261b6ec689b60cc4a58b54bc24d8d')

AND event_name = 'Transfer'

),

table3 AS (

SELECT DISTINCT e.tx_hash

FROM ethereum.core.fact_decoded_event_logs e

JOIN table2 b ON b.tx_hash = e.tx_hash

WHERE e.block_timestamp >= '2022-05-01'

AND event_name = 'DelegateVotesChanged'

),

table4 AS (

SELECT block_timestamp AS times,

CASE

WHEN e.event_name = 'Transfer'

AND contract_address = '0xa5f2211b9b8170f694421f2046281775e8468044'