select date_trunc('day',BLOCK_TIMESTAMP) as day

,case

when day>='2023-05-09' and day<='2023-05-15' then 'One Week Before Redemption'

when day>='2023-05-16' and day<='2023-05-22' then 'One Week After Redemption' end as enable_time

,AMOUNT

,AMOUNT_USD

,FROM_ADDRESS

,TO_ADDRESS

,ORIGIN_TO_ADDRESS

,ORIGIN_FROM_ADDRESS

,TX_HASH

from ethereum.core.ez_token_transfers

where BLOCK_TIMESTAMP>='2023-05-09' and BLOCK_TIMESTAMP<='2023-05-22'

and CONTRACT_ADDRESS='0xae7ab96520de3a18e5e111b5eaab095312d7fe84'),

tab2 as (

select ADDRESS

,LABEL

,LABEL_TYPE

from ethereum.core.dim_labels

where LABEL_TYPE='dex'

and label is not null)

select day

,enable_time

,sum(case when ORIGIN_FROM_ADDRESS=FROM_ADDRESS then AMOUNT_USD end) as sold_usd

,sum(case when ORIGIN_FROM_ADDRESS=TO_ADDRESS then AMOUNT_USD end) as Bought_usd

,count(distinct case when ORIGIN_FROM_ADDRESS=FROM_ADDRESS then ORIGIN_FROM_ADDRESS end) as sellers

,count(distinct case when ORIGIN_FROM_ADDRESS=TO_ADDRESS then ORIGIN_FROM_ADDRESS end) as buyers

,count(distinct case when ORIGIN_FROM_ADDRESS=FROM_ADDRESS then TX_HASH end) as sale_count

,count(distinct case when ORIGIN_FROM_ADDRESS=TO_ADDRESS then TX_HASH end) as Buy_count

from tab1

left join tab2

on tab1.ORIGIN_TO_ADDRESS=tab2.ADDRESS

group by 1,2