Query editor
withperps_table as (selectcoalesce(UPPER(trim('\u0000' from (unhex(substring(marketKey,3))))), NULL) as marketKey,coalesce(UPPER(trim('\u0000' from unhex(substring(baseAsset, 3)))), NULL) as PERP, contract_addressfrom synthetix_futuresmarket_optimism.ProxyPerpsV2_evt_PerpsTrackinggroup by marketKey, PERP, contract_address),perps_tx as (selectcoalesce(UPPER(trim('\u0000' from (unhex(substring(ft.trackingCode,3))))), 'unknown') as tracking_code,coalesce(UPPER(trim('\u0000' from (unhex(substring(ft.marketKey,3))))), 'unknown') as marketKey,coalesce(UPPER(trim('\u0000' from unhex(substring(ft.baseAsset, 3)))),'unknown') as PERP,pm.contract_address, pm.evt_tx_hash, pm.evt_block_time, pm.account, pm.fee/1e18 as fee, pm.lastPrice/1e18 as lastPrice,pm.margin/1e18 as margin, pm.size/1e18 as size, pm.tradeSize/1e18 as tradeSize,case when pm.size = 0 then 1 else 0 end as helperfromsynthetix_futuresmarket_optimism.ProxyPerpsV2_evt_PositionModified pmleft join synthetix_futuresmarket_optimism.ProxyPerpsV2_evt_PerpsTracking ft on pm.evt_tx_hash=ft.evt_tx_hash and pm.fee=ft.feewhere ft.evt_tx_hash is not null),trades as (select * from perps_tx),time_table as (--select generate_series('2022-03-17 00:00', CURRENT_DATE, '1 day') as time)selectexplode(sequence(to_date('2022-12-19'),to_date(current_date, interval 1 day))) as time)--evt_table as (select *,sum(unique_exch_evt) over (partition by tracking_code order by day) as Unique_Exchange_Users,sum(unique_perp_evt) over (partition by perp order by day) as Unique_Perp_Users,sum(unique_evt) over (order by day) as Unique_Users,sum(unique_evt) over (partition by day) as unique_user_daily,-- IYKYK13 as O_ofrom (select day, tracking_code, PERP,sum(unique_exch_perp_evt) as unique_exch_perp_evt,sum(unique_perp_evt) as unique_perp_evt,sum(unique_exch_evt) as unique_exch_evt,sum(unique_evt) as unique_evtfrom (