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