origin_from_address,

sum(ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[0]) / pow(10,18)) as staked

from flow.core_evm.fact_event_logs

where contract_address = '0xfe8189a3016cb6a3668b8ccdac520ce572d4287a'

and topics[0] = '0x3df45cb339f96ae4bdb793efcb6e22100dd0dc4fd739a4ee2033fe67ea35af96'

group by 1

),

unstake as (select

origin_from_address,

sum(ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[0]) / pow(10,18)) as unstaked

from flow.core_evm.fact_event_logs

WHERE topics[0] = '0xad145f1b26afda04058ac140badd5b2ae9369e46bf2e2a519fcdb40b65289c4a'

and contract_address = '0xfe8189a3016cb6a3668b8ccdac520ce572d4287a'

group by 1

),

current_staked_data AS (

SELECT

COALESCE(a.origin_from_address, b.origin_from_address) AS "User",

COALESCE(staked, 0) AS staked,

COALESCE(unstaked, 0) AS unstaked,

COALESCE(staked, 0) - COALESCE(unstaked, 0) AS current_staked

FROM stake a

FULL JOIN unstake b

ON a.origin_from_address = b.origin_from_address

SELECT

CASE

WHEN current_staked < 10 then '< 10'

WHEN current_staked >= 10 AND current_staked < 100 THEN '10 - 100'

WHEN current_staked >= 100 AND current_staked < 1000 THEN '100 - 1000'

ELSE '> 1000'

END AS staked_range,