datavortexcomparable-peach
Updated 2024-10-18Copy Reference Fork
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 sol_price AS (
SELECT
symbol,
price,
hour
FROM solana.price.ez_prices_hourly
WHERE symbol = 'SOL'
AND hour = (SELECT MAX(hour) FROM solana.price.ez_prices_hourly WHERE symbol = 'SOL')
AND price > 1
ORDER BY price DESC
LIMIT 1
)
SELECT
symbol,
{{usd_amount}} / price AS sol_amount,
price AS sol_price_in_usd,
hour
FROM sol_price;
*/
WITH latest_prices AS (
SELECT
symbol,
price,
hour,
ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY hour DESC) AS rn -- Get the latest price per symbol
FROM solana.price.ez_prices_hourly
WHERE price > 0.01 -- Filter for tokens with a price greater than 0.01 USD
)
SELECT
symbol,
{{usd_amount}} / price AS token_amount, -- Calculate the amount of token for the input USD amount
price AS token_price_in_usd,
hour
FROM latest_prices
WHERE rn = 1 -- Keep only the latest price per symbol
QueryRunArchived: QueryRun has been archived