SELECT DISTINCT

tx_hash,

block_timestamp,

origin_from_address,

decoded_log:weight / 1e18 AS Vote_Power

FROM avalanche.core.ez_decoded_event_logs

WHERE origin_function_signature = '0x7ac09bf7'

and origin_to_address = '0xaaaf3d9cdd3602d117c67d80eec37a160c8d9869'

AND tx_status = 'SUCCESS'

-- AND event_name = 'Voted'

),

pooltable AS (

SELECT

t2.*,

REGEXP_SUBSTR_ALL(SUBSTR(input_data, 11), '.{64}') AS segmented_data

FROM avalanche.core.fact_transactions t1

JOIN votetable t2 ON t1.tx_hash = t2.tx_hash

),

flattened_pools AS (

SELECT block_timestamp,

tx_hash,

origin_from_address,

Vote_Power,

'0x' || SUBSTR(VALUE, 25, 40) AS pool_address

FROM pooltable,

LATERAL FLATTEN(input => segmented_data)

select date_Trunc ('week',block_timestamp) as date_week,

SPLIT_PART(pool_name, ' ', 1) AS Liquidity_Pool,

count (distinct tx_hash) as Votes,

count (distinct origin_from_address) as Voters,