SELECT

decoded_log:from::STRING AS from_address,

decoded_log:to::STRING AS to_address,

(decoded_log:value::FLOAT) / 1e18 AS amount

FROM swell.core.ez_decoded_event_logs

WHERE event_name = 'Transfer'

AND contract_address = LOWER('0x18d33689AE5d02649a859A1CF16c9f0563975258')

),

received AS (

SELECT

to_address AS wallet,

SUM(amount) AS total_received

FROM transfers

GROUP BY to_address

),

sent AS (

SELECT

from_address AS wallet,

SUM(amount) AS total_sent

FROM transfers

GROUP BY from_address

)

SELECT

CASE

WHEN r.wallet = '0xaaaaaaaa82812f0a1f274016514ba2ca933bf24d' THEN 'CrocSwapDex'

WHEN r.wallet = '0x1773002742a2bcc7666e38454f761ce8fe613de5' THEN 'Evault'

WHEN r.wallet = '0x9ed15383940cc380faef0a75edace507cc775f22' THEN 'BoringVault'

ELSE r.wallet

END AS wallet_name,

COALESCE(r.total_received, 0) AS received,

COALESCE(s.total_sent, 0) AS sent,

COALESCE(r.total_received, 0) - COALESCE(s.total_sent, 0) AS current_balance