Query editor
WITH latest_price AS (SELECT priceFROM prices.dayWHERE contract_address = 0xa2E3356610840701BDf5611a53974510Ae27E2e1 -- wbethORDER BY timestamp DESCLIMIT 1),all_chains AS (SELECT-amount as amount,user,'mwbeth' as token,evt_block_timeFROM helio_bnb.interaction_v2_evt_withdrawwhere evt_tx_hash in (select evt_tx_hash from erc20_bnb.evt_transferwhere contract_address=0xE786eC5b4838410C24e5C1c75633d7C59705d6be)UNION ALLSELECTamount,user,'mwbeth' AS token,evt_block_timeFROM helio_bnb.interaction_v2_evt_depositwhere collateral=0x7dC91cBD6CB5A3E6A95EED713Aa6bF1d987146c8),cumulative_balances AS (SELECTuser,token,evt_block_time,SUM(amount) OVER (PARTITION BY user, token ORDER BY evt_block_time) AS balanceFROM all_chains),holding_durations_with_lead AS (SELECTuser,token,evt_block_time AS start_time,LEAD(evt_block_time) OVER (PARTITION BY user, token ORDER BY evt_block_time) AS end_time,balanceFROM cumulative_balances),holding_durations AS (SELECTuser,token,GREATEST(start_time, TRY_CAST('{{start_time}}' AS TIMESTAMP)) AS start_time,LEAST(COALESCE(end_time, TRY_CAST('{{end_time}}' AS TIMESTAMP)), TRY_CAST('{{end_time}}' AS TIMESTAMP)) AS end_time,balanceFROM holding_durations_with_leadWHEREstart_time < TRY_CAST('{{end_time}}' AS TIMESTAMP)AND COALESCE(end_time, TRY_CAST('{{end_time}}' AS TIMESTAMP)) > TRY_CAST('{{start_time}}' AS TIMESTAMP)),hourly_points AS (SELECTuser,token,date_diff('hour', start_time, end_time) AS hours_held,balance