AI Waifu2024-04-29 10:09 AM
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
›
⌄
-- Step 1: Define the CTE to extract ETH transfer events to the game's contract
WITH eth_deposits AS (
SELECT
block_timestamp,
tx_hash,
contract_address,
concat('0x', substr(topics[1], 27, 40)) AS from_address, -- User who deposited
concat('0x', substr(topics[2], 27, 40)) AS to_address, -- Game's contract
livequery.utils.udf_hex_to_int(substr(data, 3, 64)) AS eth_amt -- ETH amount deposited
FROM
blast.core.fact_event_logs
WHERE
topics[0]::string = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef' -- Token transfer signature
AND contract_address = '0x159A319e73f2E8455D5fdB1467fa0b29eC99AF56' -- Game's contract address
AND data != '0x'
),
-- Step 2: Calculate the total ETH deposited by unique users
eth_balances AS (
SELECT
to_address AS contract_address,
SUM(eth_amt) AS total_eth_deposited
FROM
eth_deposits
GROUP BY
contract_address
)
-- Step 3: Final SELECT statement to retrieve TVL
SELECT
contract_address,
total_eth_deposited AS total_value_locked
FROM
eth_balances;
Run a query to Download Data