Query editor
WITH sot AS (SELECT *FROM query_6231640),-- Token flow into ActivePool = inflow, out = outflowget_netflows AS (SELECTdate_trunc('day', evt_block_time) AS day,s.collateral_type,SUM(CASE WHEN evt.to = s.active_pool THEN value / pow(10, s.decimals) ELSE 0 END) AS inflow_token,SUM(CASE WHEN evt."from" = s.active_pool THEN -value / pow(10, s.decimals) ELSE 0 END) AS outflow_tokenFROM erc20_flare.evt_transfer evtINNER JOIN sot sON evt.contract_address = s.coll_tokenAND (evt.to = s.active_pool OR evt."from" = s.active_pool)WHERE evt_block_time >= date_trunc('day', now() - interval '30' day)GROUP BY 1, 2),-- Latest price per collateral type & its USD valueget_prices AS (SELECTs.collateral_type,s.price_symbol,max_by(p.price, p.timestamp) AS latest_price,max_by(p.timestamp, p.timestamp) AS price_timestampFROM sot sLEFT JOIN prices.day pON p.symbol = s.price_symbolAND p.blockchain = 'flare'GROUP BYs.collateral_type,s.price_symbol),add_prices AS (SELECTgn.day,gn.collateral_type,gn.inflow_token,gn.outflow_token,gn.inflow_token * gp.latest_price AS inflow_usd,gn.outflow_token * gp.latest_price AS outflow_usdFROM get_netflows gnLEFT JOIN get_prices gpON gn.collateral_type = gp.collateral_type)SELECTday,-- Total USD flows (chartable 👌)SUM(inflow_usd) AS "USD inflow",SUM(outflow_usd) AS "USD outflow",-- FXRP onlySUM(CASE WHEN collateral_type = 'FXRP' THEN inflow_token ELSE 0 END) AS "FXRP inflow",SUM(CASE WHEN collateral_type = 'FXRP' THEN outflow_token ELSE 0 END) AS "FXRP outflow",