takeabreath$RAY Volume, Trade Count, Avg Trade Size, Turnover Rate
Updated 2024-04-01
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
›
⌄
-- forked from $SLND Volume, Trade Count, Avg Trade Size, Turnover Rate @ https://flipsidecrypto.xyz/edit/queries/d1e9d4f1-e4f8-481b-93d1-8bea6420ddb9
-- forked from Solend Prices @ https://flipsidecrypto.xyz/edit/queries/fd354036-93ee-411a-838b-fb465f098270
WITH slnd_swaps AS (
SELECT
swaps.block_timestamp,
swaps.tx_id,
prices.close,
prices.close * swaps.swap_from_amount AS amount_usd
FROM
solana.defi.fact_swaps AS swaps
INNER JOIN solana.price.ez_token_prices_hourly AS prices
ON
DATE_TRUNC('hour', swaps.block_timestamp) = prices.recorded_hour
AND prices.token_address = '4k3Dyjzvzp8eMZWUXbBCjEvwSkkk59S5iCNLY3QrkX6R'
WHERE
'4k3Dyjzvzp8eMZWUXbBCjEvwSkkk59S5iCNLY3QrkX6R' = swaps.swap_from_mint
AND swaps.block_timestamp > DATE('2022-11-27')
AND swaps.succeeded = TRUE
),
slnd_volume_tradeCount_ats_inter AS (
SELECT
DATE_TRUNC('day', block_timestamp) AS day,
SUM(amount_usd) OVER (PARTITION BY day ORDER BY block_timestamp) AS volume,
COUNT(tx_id) OVER (PARTITION BY day ORDER BY block_timestamp) AS trade_count,
AVG(amount_usd) OVER (PARTITION BY day ORDER BY block_timestamp) AS avg_trade_size,
AVG(close)
OVER (
ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS close_7,
ROW_NUMBER() OVER (PARTITION BY day ORDER BY block_timestamp DESC) AS rank
FROM slnd_swaps
),
QueryRunArchived: QueryRun has been archived