ChiefUSD Conversion
Updated 2022-12-23
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 shdw as (
SELECT date_trunc('day', recorded_hour) as daily_time,
Symbol,
-- ID,
CASE
WHEN Symbol = 'SHDW' THEN avg(close) ELSE 0 END as shdw_price
-- sol_price/shadow_price as sol_shdw_ratio
FROM solana.core.fact_token_prices_hourly
WHERE Symbol IN ('SHDW')
AND (Symbol = 'SHDW' AND ID = 'genesysgo-shadow')
GROUP BY 1,2
Order by daily_time DESC),
SSC as(
SELECT DATE_TRUNC('day', block_timestamp) as daily_time,
avg(sales_amount) as SSC_sol_price,
COUNT(sales_amount) AS num_sales
FROM solana.core.fact_nft_sales AS sales
JOIN solana.core.dim_nft_metadata AS metadata
ON sales.mint = metadata.mint
WHERE metadata.Project_Name = 'Shadowy Super Coder'
AND succeeded = 'True'
GROUP BY daily_time, sales_amount
-- HAVING COUNT(sales_amount) > 3
ORDER BY daily_time DESC),
-- Gets SHDW price
sol as (
SELECT date_trunc('day', recorded_hour) as daily_time,
Symbol,
CASE
WHEN Symbol = 'SOL' THEN avg(close) ELSE 0 END as sol_price
-- sol_price/shadow_price as sol_shdw_ratio
FROM solana.core.fact_token_prices_hourly
WHERE Symbol IN ('SOL')
Run a query to Download Data