WITH
first_swap AS (
SELECT
token_bought_address,
MIN(block_timestamp) AS min_block_timestamp
FROM
base.dex.trades
GROUP BY
ALL
)
SELECT
DATE_TRUNC('week', block_timestamp) AS block_date,
DATEDIFF(day, min_block_timestamp, block_timestamp) AS age_of_token,
CASE
WHEN age_of_token <= 7 THEN CAST(age_of_token AS VARCHAR)
WHEN age_of_token > 7
AND age_of_token <= 14 THEN '7-14'
WHEN age_of_token > 14 THEN '>14'
END AS age_group,
SUM(usd_amount) AS volume
FROM
base.dex.trades
LEFT JOIN first_swap AS b ON trades.token_bought_address = b.token_bought_address
WHERE
trades.block_timestamp BETWEEN DATEADD(months, -12, CURRENT_DATE) AND CURRENT_DATE
GROUP BY
ALL
ORDER BY
1 DESC,
age_group DESC
LIMIT
500000