select

BLOCK_TIMESTAMP, DECODED_LOG['primaryAccount'] as account,

case

when EVENT_NAME = 'LogIncreasePosition' then abs(DECODED_LOG['increasedSize'])

when EVENT_NAME = 'LogDecreasePosition' then abs(DECODED_LOG['decreasedSize'])

end as vol

from blast.core.fact_decoded_event_logs

where CONTRACT_ADDRESS = lower('0x0a8D9c0A4a039dDe3Cb825fF4c2f063f8B54313A')

and BLOCK_TIMESTAMP <= timestamp '2024-04-24 00:00:01'

and EVENT_NAME in ('LogIncreasePosition', 'LogDecreasePosition')

),

liquidate as (

select

BLOCK_TIMESTAMP, DECODED_LOG['account'] as account,

abs(DECODED_LOG['size']) as vol

from blast.core.fact_decoded_event_logs

where CONTRACT_ADDRESS = lower('0x963Cbe4cFcDC58795869be74b80A328b022DE00C')

and BLOCK_TIMESTAMP <= timestamp '2024-04-24 00:00:01'

and EVENT_NAME = 'LogLiquidationPosition'

select account, vol as "Volume", concat(round(share * 100,2), '%') as share,

base_reward as "Base reward", bonus_reward as "Bonus reward",

base_reward + bonus_reward as "Total reward"

from (

select

*, vol / sum(vol) over () as share, 1 as base_reward,

count(account) over () as participant ,

round(vol / sum(vol) over () * (500 - count(account) over ()), 2) as bonus_reward,

from

(

select

account, sum(vol) / 1e30 as vol,

from (

select * from trade_log