Query editor
WITH device_stats AS (SELECTpd.date,pd.id,COALESCE(i.device_count, 0) AS daily_new_devicesFROM (SELECTDATE(created_at) AS date,CAST(project AS VARCHAR) AS project_id,COUNT(CAST(id AS VARCHAR)) AS device_countFROM dune.iotex_devrel.dataset_ioid_devicesGROUP BY 1, 2UNION ALLSELECTDATE(CASEWHEN LENGTH(CAST(created_at AS VARCHAR)) = 16THEN parse_datetime(CAST(created_at AS VARCHAR) || ':00', 'yyyy-MM-dd''T''HH:mm:ss')ELSE parse_datetime(CAST(created_at AS VARCHAR), 'yyyy-MM-dd''T''HH:mm:ss')END) AS date,project_id,COUNT(id) AS device_countFROM dune.iotex_devrel.dataset_ioid_v2_devicesGROUP BY 1, 2) iRIGHT JOIN (SELECTd.date,ip.idFROM (SELECT CAST(id AS VARCHAR) AS idFROM dune.iotex_devrel.dataset_ioid_projectsUNIONSELECT idFROM dune.iotex_devrel.dataset_ioid_v2_projects) ipFULL OUTER JOIN dune.iotex_devrel.result_data_dim d ON 0 = 0WHERE d.date > DATE('2024-08-25')AND d.date <= current_date) pd ON i.date = pd.date AND i.project_id = pd.id),project_names AS (SELECTid,CASEWHEN LOWER(name) IN ('nubila', 'nubila network') THEN 'Nubila'ELSE nameEND AS unified_nameFROM (SELECT CAST(id AS VARCHAR) AS id, CAST(name AS VARCHAR) AS nameFROM dune.iotex_devrel.dataset_ioid_projectsUNIONSELECT id, nameFROM dune.iotex_devrel.dataset_ioid_v2_projects)),merged_stats AS (