larry_langprice vs balance
Updated 2024-08-16
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
36
›
⌄
WITH RunningBalance AS (
SELECT
DATE_TRUNC('hour', BLOCK_TIMESTAMP) AS hour,
SUM(CASE
WHEN tx_to = '3eGdjMdQqFUNc4G64BZb7YCALDEaturLUWcixjJfKxEh' THEN amount
WHEN tx_from = '3eGdjMdQqFUNc4G64BZb7YCALDEaturLUWcixjJfKxEh' THEN -amount
END) OVER (ORDER BY BLOCK_TIMESTAMP) AS running_balance
FROM
solana.core.fact_transfers
WHERE
mint = '{{contract_ca}}'
AND (tx_from = '3eGdjMdQqFUNc4G64BZb7YCALDEaturLUWcixjJfKxEh'
OR tx_to = '3eGdjMdQqFUNc4G64BZb7YCALDEaturLUWcixjJfKxEh')
),
PriceData AS (
SELECT
DATE_TRUNC('hour', hour) AS hour,
price * 1000 AS price
FROM
solana.price.ez_prices_hourly
WHERE
token_address = '{{contract_ca}}'
),
MaxValues AS (
SELECT
(SELECT MAX(running_balance) FROM RunningBalance) AS max_running_balance,
(SELECT MAX(price) FROM PriceData) AS max_price
)
SELECT
rb.hour,
(rb.running_balance / mv.max_running_balance) * 10000 AS normalized_balance,
(pd.price / mv.max_price) * 10000 AS normalized_price
FROM
RunningBalance rb
LEFT JOIN
PriceData pd ON rb.hour = pd.hour
QueryRunArchived: QueryRun has been archived