from thorchain.swaps),

synth as (select to_asset

from thorchain.swaps

where native_to_address like 'thor%' and to_asset!='THOR.RUNE'

group by 1),

rawb as (select block_timestamp, tx_id, pool_name, native_to_address, from_asset, to_amount, case

when to_amount_usd = 0 then to_amount

else to_amount_usd end as vol_usd

from raw

where from_asset in (select to_asset from synth)),

rawm as (select block_timestamp, tx_id, pool_name, native_to_address, to_asset, to_amount, case

when to_amount_usd = 0 then to_amount

else to_amount_usd end as vol_usd

from thorchain.swaps

where native_to_address like 'thor%' and to_asset!='THOR.RUNE'),

mints as (select date_trunc('day', block_timestamp) as dt, to_asset, count(distinct tx_id) as tx_count, count(distinct native_to_address) as minter_count, sum(vol_usd) as volume_usd

from rawm

group by 1,2),

burns as (select date_trunc('day', block_timestamp) as dt, from_asset, count(distinct tx_id) as tx_count, count(distinct native_to_address) as minter_count, sum(vol_usd) as volume_usd

from rawb

group by 1,2),

final as (select a.dt, a.to_asset, SUBSTRING(a.to_asset, 0, charindex('/', a.to_asset, 1)) as blockchain,

a.volume_usd as mint_volume_usd, (b.volume_usd) as burn_volume_usd, (mint_volume_usd-burn_volume_usd) as nett_synths_change

from mints a

left outer join burns b on a.dt = b.dt and a.to_asset = b.from_asset)

select dt, blockchain, sum(nett_synths_change) as nett_, case

when nett_ is null then 0.001

else nett_ end as nett_change