WITH labels as(

SELECT address,

address_name

FROM terra.labels

where address_name like 'm%'

and label = 'cw20Token'

),

prices as (

select date_trunc('hour', block_timestamp) as hour,

currency,

avg(price_usd) as price

from terra.oracle_prices

where block_timestamp >= '2021-01-01'

group by 1,2

),

transfers as (

SELECT

block_timestamp,

msg_value:contract::string as token_contract,

msg_value:execute_msg:transfer:amount / pow(10, 6) as volume

FROM terra.msgs

WHERE msg_value:execute_msg:transfer IS NOT NULL

AND block_timestamp >= '2021-01-01'

and tx_status = 'SUCCEEDED'

and msg_value:execute_msg:transfer:amount IS NOT NULL

l.address_name AS token_label,

SUM(coalesce(volume,0) * coalesce(price,0)) AS volumes

FROM transfers

JOIN labels l

ON token_contract = l.address