Query editor
/* Calculate the average daily swaps for each quarter *//* Calculate the daily number of swaps */WITH daily_swaps AS (SELECTDATE_TRUNC('day', block_time) AS day, /* Truncate the block_time to the day level */SUM(CASE WHEN log_index = 1 THEN 1 ELSE 0 END) AS swaps /* Sum the number of swaps per day */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,AVG(swaps) AS average_daily_swaps /* Calculate the average daily swaps for each quarter */FROM daily_swapsGROUP BY1ORDER BYCASEWHEN quarter = 'Q1 2023' THEN 1WHEN quarter = 'Q2 2023' THEN 2WHEN quarter = 'Q3 2023' THEN 3WHEN quarter = 'Q4 2023' THEN 4WHEN quarter = 'Q1 2024' THEN 5WHEN quarter = 'Q2 2024' THEN 6END;