Query editor
with burn as (select day, sum(burn) as burnfrom (selectdate_trunc('day', block_time) as day,sum(fee/1e9*0.5) as burnfrom solana.transactionswhere block_time>=(casewhen '{{Burn Timeframe}}' = '1 Year' then date(now() - interval '1' year)when '{{Burn Timeframe}}' = '2 Years' then date(now() - interval '2' year)when '{{Burn Timeframe}}' = 'All-Time' then date('2023-06-02')end)group by 1union allselect date_trunc('day', block_time) as day,sum(fee/1e9*0.5) as burnfrom solana.vote_transactionswhere block_time>=(casewhen '{{Burn Timeframe}}' = '1 Year' then date(now() - interval '1' year)when '{{Burn Timeframe}}' = '2 Years' then date(now() - interval '2' year)when '{{Burn Timeframe}}' = 'All-Time' then date('2023-06-02')end)group by 1)awhere day is not nullgroup by 1),price as (select date_trunc('day', minute) as day,avg(price) as pricefrom prices.usdwhere contract_address is nulland symbol = 'SOL'and blockchain is nulland date(minute) >= (casewhen '{{Burn Timeframe}}' = '1 Year' then date(now() - interval '1' year)when '{{Burn Timeframe}}' = '2 Years' then date(now() - interval '2' year)when '{{Burn Timeframe}}' = 'All-Time' then date('2023-06-02')end)group by 1)select date_trunc((casewhen '{{Burn Timeframe}}' = '1 Year' then 'day'when '{{Burn Timeframe}}' = '2 Years' then 'week'when '{{Burn Timeframe}}' = 'All-Time' then 'month'end), a.day) as day, sum(burn) as burn, sum(burn*price) as burn_usdfrom burn aleft join price con a.day=c.daygroup by 1