select

tx_hash

,block_timestamp

,iff(topic_0 = '0xdcbc1c05240f31ff3ad067ef1ee35ce4997762752e3a095284754544f4c709d7', 'staking', 'unstaking') as action

,utils.udf_hex_to_int(right(data, 64))::int / 1e18 as LRON_amount

,origin_from_address as user

,iff(origin_to_address = '0xcad9e7aa2c3ef07bad0a7b69f97d059d8f36edd2', 'Scrow contract', origin_to_address) as origin_to_address

from ronin.core.fact_event_logs

where 1=1

and tx_succeeded = 'TRUE'

and block_timestamp > '2025-03-19'

--and tx_hash = '0x67dac46a1c1ad81bf77958a46cc8f35ec90f7f04efad84ec98ef9f78310bb687' --first deposit

and contract_address = '0xcad9e7aa2c3ef07bad0a7b69f97d059d8f36edd2' --LRON contract

and topic_0 in ('0xdcbc1c05240f31ff3ad067ef1ee35ce4997762752e3a095284754544f4c709d7', '0xfbde797d201c681b91056529119e0b02407c7bb96a4a2c75c01fc9667232c8db')

),

users as (

select

user

,sum(case when action = 'staking' then lron_amount else 0 end) as total_deposits_volume

,sum(case when action = 'unstaking' then lron_amount else 0 end) as total_withdrawals_volume

,sum(case when action = 'staking' then 1 else 0 end) as deposits

,sum(case when action = 'unstaking' then 1 else 0 end) as withdrawals

from core_txs

group by user

),

with_atypical_case as (

select

*

,sum(coalesce(total_deposits_volume, 0) - coalesce(total_withdrawals_volume, 0)) as almost_corrected_except -- there is a scenario where an user can redeem (withdraw) more than deposited, thats when an user receives LRON through other methods.

from users

group by all