Query editor
/* Calculate the average daily traders for each quarter *//* Calculate the daily number of distinct traders */WITH daily_traders AS (SELECTDATE_TRUNC('day', block_time) AS day, /* Truncate the block_time to the day level */COUNT(DISTINCT tx_signer) AS traders /* Count the number of distinct traders 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,EXTRACT(YEAR FROM day) AS year, /* Extract the year for sorting */AVG(traders) AS average_daily_traders /* Calculate the average daily traders for each quarter */FROM daily_tradersGROUP 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;