Query editor
WITH b AS (SELECT"from" AS user,-value AS value,evt_block_timeFROM erc20_{{chain}}.evt_transferWHEREcontract_address = {{reciept_token}}UNION ALLSELECT"to" AS user,value AS value,evt_block_timeFROM erc20_{{chain}}.evt_transferWHEREcontract_address = {{reciept_token}}),cumulative_balances AS (SELECTuser,evt_block_time,SUM(value) OVER (PARTITION BY userORDER BY evt_block_time) AS balanceFROM b),holding_durations_with_lead AS (SELECTuser,evt_block_time AS start_time,LEAD(evt_block_time) OVER (PARTITION BY user ORDER BY evt_block_time) AS end_time,balanceFROM cumulative_balances),holding_durations AS (SELECTuser,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,DATE_DIFF('hour', start_time, end_time) AS hours_held,balanceFROM holding_durationsWHERE balance > 0