(SELECT date(block_timestamp) AS pdate, swap_from_mint AS asset, avg(swap_to_amount) / avg(swap_from_amount) AS asset_price

FROM solana.fact_swaps

WHERE (swap_to_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' OR swap_to_mint = 'Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB')

AND swap_from_mint = 'DUSTawucrTsGU8hcqRdHDCbuYhCPADMLM2VcCb8VnFnQ'

AND swap_to_amount > 0

AND swap_from_amount > 0

AND date(block_timestamp) >= '2022-01-01'

GROUP BY 1,2

ORDER BY 1 ASC)

SELECT date_trunc('week', s.block_timestamp) AS date, COUNT(DISTINCT tx_id) AS tx_count, COUNT(DISTINCT swapper) AS swappers,

CASE WHEN SWAP_FROM_MINT = 'DUSTawucrTsGU8hcqRdHDCbuYhCPADMLM2VcCb8VnFnQ' THEN 'Sell' ELSE 'Buy' END AS type,

avg(CASE WHEN SWAP_FROM_MINT = 'DUSTawucrTsGU8hcqRdHDCbuYhCPADMLM2VcCb8VnFnQ' THEN SWAP_FROM_AMOUNT ELSE SWAP_TO_AMOUNT END) AS avg_vol,

median(CASE WHEN SWAP_FROM_MINT = 'DUSTawucrTsGU8hcqRdHDCbuYhCPADMLM2VcCb8VnFnQ' THEN SWAP_FROM_AMOUNT ELSE SWAP_TO_AMOUNT END) AS median_vol,

sum(CASE WHEN SWAP_FROM_MINT = 'DUSTawucrTsGU8hcqRdHDCbuYhCPADMLM2VcCb8VnFnQ' THEN SWAP_FROM_AMOUNT ELSE SWAP_TO_AMOUNT END) AS vol,

sum(CASE WHEN SWAP_FROM_MINT = 'DUSTawucrTsGU8hcqRdHDCbuYhCPADMLM2VcCb8VnFnQ' THEN SWAP_FROM_AMOUNT ELSE SWAP_TO_AMOUNT END * prices.asset_price) AS usd_vol,

sum(vol) over (partition BY type ORDER BY date) AS cumu_vol,

sum(usd_vol) over (partition BY type ORDER BY date) AS cumu_usd_vol

FROM solana.core.fact_swaps s

JOIN prices ON prices.pdate = date(s.block_timestamp)

WHERE (SWAP_FROM_MINT = 'DUSTawucrTsGU8hcqRdHDCbuYhCPADMLM2VcCb8VnFnQ' OR SWAP_TO_MINT = 'DUSTawucrTsGU8hcqRdHDCbuYhCPADMLM2VcCb8VnFnQ')

AND swap_to_amount > 0

AND swap_from_amount > 0

GROUP BY date, type