with bero_staked as (

select block_timestamp as time, (utils.udf_hex_to_int(substr(data,3,66))::int)/1e18 as staked from berachain.testnet.fact_event_logs

where topics[0]='0x9e71bc8eea02a63969f509818f2dafb9254532904319f9dbda79b67bd34a5f3d'

and contract_address=lower('0xA0E4748E68b6d73234711197e96171B896B796f4')

and concat('0x',substr(TOPICS[1], 27,64))=lower('{{address}}')

),

bgt_claims as (

select sum((utils.udf_hex_to_int(substr(data,3,66))::int)/1e18) as bgt_claimed from berachain.testnet.fact_event_logs

where topics[0]='0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'

and ORIGIN_TO_ADDRESS=lower('0xA0E4748E68b6d73234711197e96171B896B796f4')

and concat('0x',substr(TOPICS[2], 27,64))=lower('{{address}}')

),

claim_bribes as (

select sum((utils.udf_hex_to_int(substr(data,3,66))::int)/1e18) as bera_claimed from berachain.testnet.fact_event_logs

where topics[0]='0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'

and ORIGIN_TO_ADDRESS=lower('0x1f9505Ae18755915DcD2a95f38c7560Cab149d9C')

and concat('0x',substr(TOPICS[2], 27,64))=lower('{{address}}')

),

multiplier as (

select staked/2 as mult from bero_staked

select DATEDIFF('day',time,CURRENT_TIMESTAMP) as duration, staked, bgt_claimed, bera_claimed,

staked/mult as staked_bero, bgt_claimed/mult as claimed_bgt, bera_claimed/mult as claimed_bera

from multiplier,bero_staked, bgt_claims, claim_bribes