ETH Balance (Starknet) copy

10x05-ethETH Balance (Starknet) copy

Updated 2024-02-16

-- forked from mamad-5XN3k3 / ETH Balance (Starknet) @ https://flipsidecrypto.xyz/mamad-5XN3k3/q/_aHVGauDG3Lr/eth-balance-starknet

-- forked from arabianhorses-jZtVNa / MAIN @ https://flipsidecrypto.xyz/arabianhorses-jZtVNa/q/Ew5-UbZsYWoV/main

WITH address_edit AS (

SELECT

CASE

WHEN LOWER('{{Account_address}}') LIKE '0x0000%' THEN CONCAT('0x', SUBSTR(LOWER('{{Account_address}}'), 7, LENGTH('{{Account_address}}')))

WHEN LOWER('{{Account_address}}') LIKE '0x000%' THEN CONCAT('0x', SUBSTR(LOWER('{{Account_address}}'), 6, LENGTH('{{Account_address}}')))

WHEN LOWER('{{Account_address}}') LIKE '0x00%' THEN CONCAT('0x', SUBSTR(LOWER('{{Account_address}}'), 5, LENGTH('{{Account_address}}')))

WHEN LOWER('{{Account_address}}') LIKE '0x0%' THEN CONCAT('0x', SUBSTR(LOWER('{{Account_address}}'), 4, LENGTH('{{Account_address}}')))

ELSE LOWER('{{Account_address}}')

END AS editedAddress

LIMIT 1

)

SELECT

dates,

sum(volume) over (order by dates asc) as "ETH Balance"

from

(

SELECT

TIMESTAMP as dates,

editedAddress,

case

when PARAMETERS[0]:value = editedAddress then -1 * (PARAMETERS[2]:value[0]:value/POW(10,18))

when PARAMETERS[1]:value = editedAddress then (PARAMETERS[2]:value[0]:value/POW(10,18))

end as volume

FROM external.tokenflow_starknet.decoded_events e join address_edit a on (PARAMETERS[0]:value = editedAddress or PARAMETERS[1]:value = editedAddress)

WHERE CONTRACT = '0x049d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7'

AND CHAIN_ID = 'mainnet'

AND NAME = 'Transfer'

--AND (PARAMETERS[0]:value = (SELECT editedAddress FROM address_edit) OR PARAMETERS[1]:value = (SELECT editedAddress FROM address_edit))

)

where dates::date <= '2023-11-15'

--group by 1

Last run: about 1 year ago

...

161

7KB

20s