SELECT

date_trunc('day', block_timestamp) AS date,

split(asset, '~')[1] AS asset, -- Correctly parsing asset symbol

split(asset, '~')[0] AS chain, -- Correctly parsing chain

SUM(amount_e8 / pow(10, 8)) AS deposit_amount

FROM

thorchain.defi.fact_trade_account_deposit_events

GROUP BY 1, 2, 3

),

withdraw AS (

SELECT

date_trunc('day', block_timestamp) AS date,

split(asset, '~')[1] AS asset, -- Correctly parsing asset symbol

split(asset, '~')[0] AS chain, -- Correctly parsing chain

SUM(amount_e8 / pow(10, 8)) AS withdraw_amount

FROM

thorchain.defi.fact_trade_account_withdraw_events

GROUP BY 1, 2, 3

),

daily_net_balance AS (

SELECT

d.date,

d.chain,

d.asset,

COALESCE(SUM(d.deposit_amount), 0) - COALESCE(SUM(w.withdraw_amount), 0) AS net_balance

FROM

deposit d

LEFT JOIN

withdraw w ON d.date = w.date AND d.chain = w.chain AND d.asset = w.asset

GROUP BY 1, 2, 3

),

daily_prices AS (

SELECT

date_trunc('day', hour) AS date,

symbol,