Query editor
WITHdate_range AS (SELECTDATE_TRUNC('{{3 Time Granularity}}',LEAST(now(),'{{4 End Date}}'::date)- interval '{{2 Trailing Num}}' {{3 Time Granularity}}-- + interval '1' {{3 Time Granularity}} -- since Betweens are >=) AS start_time,DATE_TRUNC('{{3 Time Granularity}}',LEAST(now(),'{{4 End Date}}'::date)- interval '{{2 Trailing Num}}' {{3 Time Granularity}}- interval '{{2 Trailing Num}}' {{3 Time Granularity}}-- + interval '1' {{3 Time Granularity}} -- since Betweens are >=) AS cohort_start_time,'{{4 End Date}}'::date + (CASE WHEN '{{3 Time Granularity}}' = 'hour' --to be inclusive of the end dateTHEN interval '1' hour ELSE interval '1' dayEND) AS end_time_map,DATE_TRUNC('{{3 Time Granularity}}',LEAST(now(),'{{4 End Date}}'::date))AS end_time_now,'{{4 End Date}}'::date AS end_time), excluded_methods AS (SELECT method, descriptorFROM (values('0x095ea7b3','ERC20 Approval') --'ERC20 Approval',('0xa9059cbb','ERC20 Transfer') --'ERC20 Transfer',('0xd0e30db0','WETH Wrap') --'WETH Wrap',('0x2e1a7d4d','WETH Unwrap') --'WETH Unwrap',('0x42842e0e','ERC721 Transfer'), ('0x23b872dd','ERC721 Transfer') --'ERC721 Transfer' --safe transfer and transfer,('0xa22cb465','ERC721 Approval') --'ERC721 Approval',('0xcbd4ece9','Bridge In (L1 to L2)') --'Bridge In (L1 to L2)',('0x32b7006d','Bridge Out (L2 to L1)') --'Bridge Out (L2 to L1)',('0xbede39b5','OVM Gas Price Oracle'), ('0xbf1fe420','OVM Gas Price Oracle') --'OVM Gas Price Oracle',('0x60806040','Contract Creation'), ('0x60c06040','Contract Creation') --'Contract Creation') a (method, descriptor)), gs_range AS (SELECTexplode(sequence((SELECT start_time FROM date_range),(SELECT end_time_now FROM date_range),interval '1 {{3 Time Granularity}}')) AS dts), op_contracts AS (SELECT *,CASE