tarikflipsideweekly volume solana coins
Updated 2023-01-29Copy 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 base as (SELECT *, date_trunc('HOUR', block_timestamp) as date_hour
FROM solana.core.fact_swaps
where block_timestamp >= DATE_TRUNC('week', CURRENT_DATE) - INTERVAL '7 DAY'
and succeeded),
-- SELECT * FROM base;
swaps_w_prices as (
SELECT s.*,
h.close as from_token_price,
h.token_name as from_token_name,
h.symbol as from_token_symbol,
hh.close as to_token_price,
hh.token_name as to_token_name,
hh.symbol as to_token_symbol
FROM base as s
left join solana.core.ez_token_prices_hourly as h
on s.swap_from_mint = h.token_address
and date_trunc('HOUR', s.block_timestamp) = h.recorded_hour
left join solana.core.ez_token_prices_hourly as hh
on s.swap_to_mint = hh.token_address
and date_trunc('HOUR', s.block_timestamp) = hh.recorded_hour)
,
swap_amounts_in_usd as (
SELECT *,
from_token_price * swap_from_amount as swap_from_amt_in_usd,
to_token_price * swap_to_amount as swap_to_amt_in_usd
from swaps_w_prices
where to_token_name is not null
and from_token_name is not null)
SELECT top 10
to_token_name,
sum(swap_to_amt_in_usd) as usd_amount_sum,
Run a query to Download Data