Query editor
-- Get date series datawith generateDates AS(SELECT * FROM UNNEST(sequence(CAST('2022-01-01' AS TIMESTAMP),CAST(NOW() AS TIMESTAMP), interval '1' day)) as t(date)),-- First mint datecalendar_1 AS (SELECT MIN(DATE_TRUNC('day', evt_block_time)) as datefrom erc20_base.evt_transferwhere contract_address = 0xd2a530170D71a9Cfe1651Fb468E2B98F7Ed7456b -- AUDF token contract (Base)),daily as (selectDATE_TRUNC('{{period}}', block_date) as "date",-- token_pair,-- version,case when token_sold_address = 0xd2a530170D71a9Cfe1651Fb468E2B98F7Ed7456b -- AUDF token contract (Base)then 'selling swap' else 'buying swap' end as direction,sum(amount_usd) as volume,count(distinct tx_hash) as swapsfrom dex.tradeswhereblockchain = 'base'and (token_bought_address = 0xd2a530170D71a9Cfe1651Fb468E2B98F7Ed7456bor token_sold_address = 0xd2a530170D71a9Cfe1651Fb468E2B98F7Ed7456b)-- and DATE_TRUNC('{{period}}', block_date) >= date '{{date}}'group by 1,2),all_dates as (selectDATE_TRUNC('{{period}}', a."date") as "date", COALESCE(b.direction,'buying swap') as direction, COALESCE(b.volume,0) as volume, COALESCE(b.swaps,0) as swapsfrom generateDates aleft join daily bon a."date" = b."date"-- where-- and DATE_TRUNC('{{period}}', a."date") >= date '{{date}}')select*, sum(volume) over (order by "date") as cumulative_volume, sum(swaps) over (order by "date") as cumulative_swapsfrom all_dateswhere DATE_TRUNC('{{period}}', "date") >= (select * from calendar_1)group by 1,2,3,4order by 1 desc