SELECT

decoded_log['accountOwner'] AS address,

event_name,

decoded_log['assetId'] AS asset_id,

CASE

WHEN SUBSTRING(decoded_log['assetId'], 5, 4) < 2050 THEN 1

WHEN SUBSTRING(decoded_log['assetId'], 5, 4) < 2056 THEN 2

WHEN SUBSTRING(decoded_log['assetId'], 5, 4) = 2056 THEN 3

WHEN SUBSTRING(decoded_log['assetId'], 5, 4) > 2056 THEN 5

ELSE 1

END AS power,

block_timestamp

FROM blast.core.ez_decoded_event_logs

WHERE contract_address = '0xc48f6213a1a4d3ead79db812c2b91817980532c3'

AND event_name IN ('WithdrawAsset', 'DepositAsset')

AND decoded_log['assetAddress'] = '0xddd29c8b9ef72a11fcd3c97357eb3786bfa3d214'

),

deposited_nft AS (

SELECT

address,

SUM(power) AS total_power,

COUNT(*) AS total_nft

FROM nft_info n1

WHERE event_name = 'DepositAsset'

AND NOT EXISTS (

SELECT 1

FROM nft_info n2

WHERE n2.asset_id = n1.asset_id

AND n2.event_name = 'WithdrawAsset'

AND n2.block_timestamp > n1.block_timestamp

)

GROUP BY address

SELECT

SUM(DECODED_LOG['damage'] + COALESCE(dn.total_power, 0)) AS total_damage