Query editor
/* Calculate the average 24-hour trading volume for each quarter *//* Calculate the daily trading volume */WITH daily_volume AS (SELECTDATE_TRUNC('day', block_time) AS day, /* Truncate the block_time to the day level */SUM(COALESCE(input_usd, output_usd)) AS volume /* Sum the input and output USD to get the daily volume */FROM jupiter_solana.aggregator_swapsWHEREblock_time BETWEEN CAST('2023-01-01' AS TIMESTAMP) AND CAST('2024-06-30' AS TIMESTAMP) /* Filter data from Q1 2023 to Q2 2024 */GROUP BY1)SELECTCASEWHEN day BETWEEN CAST('2023-01-01' AS TIMESTAMP) AND CAST('2023-03-31' AS TIMESTAMP) THEN 'Q1 2023' /* Define Q1 2023 */WHEN day BETWEEN CAST('2023-04-01' AS TIMESTAMP) AND CAST('2023-06-30' AS TIMESTAMP) THEN 'Q2 2023' /* Define Q2 2023 */WHEN day BETWEEN CAST('2023-07-01' AS TIMESTAMP) AND CAST('2023-09-30' AS TIMESTAMP) THEN 'Q3 2023' /* Define Q3 2023 */WHEN day BETWEEN CAST('2023-10-01' AS TIMESTAMP) AND CAST('2023-12-31' AS TIMESTAMP) THEN 'Q4 2023' /* Define Q4 2023 */WHEN day BETWEEN CAST('2024-01-01' AS TIMESTAMP) AND CAST('2024-03-31' AS TIMESTAMP) THEN 'Q1 2024' /* Define Q1 2024 */WHEN day BETWEEN CAST('2024-04-01' AS TIMESTAMP) AND CAST('2024-06-30' AS TIMESTAMP) THEN 'Q2 2024' /* Define Q2 2024 */END AS quarter,EXTRACT(YEAR FROM day) AS year, /* Extract the year for sorting */AVG(volume) AS average_24h_volume /* Calculate the average daily volume for each quarter */FROM daily_volumeGROUP BY1, 2ORDER BYyear, /* Order by year */CASE /* Order by quarter within each year */WHEN quarter LIKE 'Q1%' THEN 1WHEN quarter LIKE 'Q2%' THEN 2WHEN quarter LIKE 'Q3%' THEN 3WHEN quarter LIKE 'Q4%' THEN 4END;