From solana.core.fact_swaps s LEFT OUTER JOIN solana.core.dim_labels l

ON s.swap_from_mint = l.address

Where block_timestamp::date >= '2022-08-01' And succeeded = 'True'

Group by timest, address_name),

t2(timest, address_name, swapcount, sumto) as (Select date_trunc('week',s.block_timestamp) as timest,address_name, count(succeeded) as swapcount, sum(s.swap_to_amount) as sumto

From solana.core.fact_swaps s LEFT OUTER JOIN solana.core.dim_labels l

ON s.swap_to_mint = l.address

Where block_timestamp::date >= '2022-08-01' And succeeded = 'True'

Group by timest, address_name)

select case when t1.address_name like '%usd coin%' then 'USD coin'

when t1.address_name like '%ust%' then 'UST'

when t1.address_name like '%usdt%' then 'USDT'

else 'Others' end as "address_name",

sum(t1.swapcount) as "From swapcount", sum(t2.swapcount) as "To swapcount"

from t1 left outer join t2

on t1.timest = t2.timest and t1.address_name = t2.address_name

group by 1