SELECT --*

date_trunc('week', block_timestamp) as week,

to_chain,

from_chain,

sum(CASE WHEN from_chain LIKE 'osmo' THEN usd_volume ELSE 0 end) as from_osmo_volume,

sum(CASE WHEN to_chain LIKE 'osmo' THEN usd_volume ELSE 0 end) as to_osmo_volume,

(sum(CASE WHEN to_chain LIKE 'osmo' THEN usd_volume ELSE 0 end) - sum(CASE WHEN from_chain LIKE 'osmo' THEN usd_volume ELSE 0 end)) as net_in_volume,

count(DISTINCT CASE WHEN to_chain LIKE 'osmo' THEN tx_id end) as events

FROM (

SELECT --*

block_timestamp,

Substring(sender, 0 , charindex('1', sender) -1) as from_chain,

Substring(RECEIVER, 0 , charindex('1', RECEIVER) -1) as to_chain,

project_name,

CASE WHEN PROJECT_NAME LIKE 'USDC' then (amount / power(10, DECIMAL)) ELSE price * (amount / power(10, DECIMAL)) END as USD_volume ,

tx_id

FROM osmosis.core.fact_transfers

LEFT outer JOIN osmosis.core.dim_labels

ON CURRENCY like ADDRESS

LEFT outer JOIN (

SELECT

date_trunc('day', recorded_at) as day1,

symbol,

avg(price) as price

FROM osmosis.core.dim_prices

GROUP BY 1,2

) ON symbol LIKE lower(project_name)

AND day1 = date_trunc('day', block_timestamp)