ChiefUSD Conversion
    Updated 2022-12-23
    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