date_trunc('week', date_day) as date,

case

when date_part('month', date_day) <= 6 then 'First Half 2024'

else 'Second Half 2024'

end as half_year

from crosschain.core.dim_dates

where date_part('year', date_day) = 2024

group by date_trunc('week', date_day), half_year

),

mints as (

date_trunc('week', e.block_timestamp) as date,

count(distinct e.tx_hash) as mint_count,

count(distinct e.origin_from_address) as stakers_count, -- Counting distinct staker addresses

sum(utils.udf_hex_to_int(substr(e.data, 3, 64)) / pow(10, 18)) as core_staked,

sum(utils.udf_hex_to_int(substr(e.data, 67, 64)) / pow(10, 18)) as stcore_minted,

sum(t.tx_fee) as total_tx_fees

from core.core.fact_event_logs e

join core.core.fact_transactions t on e.tx_hash = t.tx_hash

where e.tx_succeeded

and date_part('year', e.block_timestamp) = 2024

and e.contract_address = '0xf5fa1728babc3f8d2a617397fac2696c958c3409'

and e.origin_to_address = '0xf5fa1728babc3f8d2a617397fac2696c958c3409'

and e.origin_function_signature = '0x6a627842'

and e.topics[0] = '0x4c209b5fc8ad50758f13e2e1088ba56a560dff690a1c6fef26394f4c03821c4f'

group by date_trunc('week', e.block_timestamp)

),

redeem as (

date_trunc('week', e.block_timestamp) as date,

count(distinct e.tx_hash) as redeem_count,

count(distinct e.origin_from_address) as redeemers_count, -- Counting distinct redeemer addresses

sum(utils.udf_hex_to_int(substr(e.data, 3, 64)) / pow(10, 18)) as stcore_burned,

sum(utils.udf_hex_to_int(substr(e.data, 67, 64)) / pow(10, 18)) as core_to_receive,