crypto_gostalone-yellow
Updated 2024-10-04
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 BNsol AS (
SELECT
DATE_TRUNC('day', BLOCK_TIMESTAMP) AS transfer_date,
SUM(AMOUNT) AS total_volume,
COUNT(TX_ID) AS transaction_count,
SUM(COUNT(TX_ID)) OVER (ORDER BY DATE_TRUNC('day', BLOCK_TIMESTAMP)) AS cumulative_tx_count
FROM
solana.core.fact_transfers
WHERE
MINT = 'BNso1VUJnh4zcfpZa6986Ea66P6TCp59hvtNJ8b1X85'
AND BLOCK_TIMESTAMP >= CURRENT_DATE - INTERVAL '7 days' -- Filter for the past 7 days
AND BLOCK_TIMESTAMP < CURRENT_DATE
GROUP BY
transfer_date
ORDER BY
transfer_date DESC
),
daily_token_prices AS (
SELECT
DATE_TRUNC('day', HOUR) AS price_date,
PRICE AS closing_price,
SYMBOL
FROM
solana.price.ez_prices_hourly
WHERE
TOKEN_ADDRESS = 'BNso1VUJnh4zcfpZa6986Ea66P6TCp59hvtNJ8b1X85'
AND HOUR >= CURRENT_DATE - INTERVAL '7 days' -- Filter for the past 7 days
AND HOUR < DATE_TRUNC('day', HOUR) + INTERVAL '1 day'
QUALIFY ROW_NUMBER() OVER (PARTITION BY DATE_TRUNC('day', HOUR) ORDER BY HOUR DESC) = 1
)
SELECT
BNsol.transfer_date,
BNsol.total_volume * token_prices.closing_price AS volume_usd,
BNsol.transaction_count,
BNsol.cumulative_tx_count,
token_prices.SYMBOL,
QueryRunArchived: QueryRun has been archived