ClickHouse SQL: Calculating Daily Active Users and Growth - Optimized for Search Engines

SELECT ts,total_num,total_increase_num\nFROM \n(\n SELECT ts,\n if(i \u003e= 31, length(arrayDistinct(arrayFlatten(arraySlice(total, i-30, 31)))), length(arrayDistinct(arrayFlatten(arraySlice(total, 1, i))))) as total_num\n FROM \n (\n SELECT groupArray(A.Ds) as t,\n groupArray(app_id_list) as total\n from (\n SELECT Ds, groupArray(appid) as app_id_list\n FROM (\n select AppIdentifier as appid, Ds\n from log_iMonkey_iOS_overview\n GROUP BY AppIdentifier, Ds\n union all\n select AppIdentifier as appid, Ds\n from log_iMonkey_Android_overview\n where Debug = 1\n GROUP BY AppIdentifier, Ds\n union all\n select CAST(PlatformType AS String) as appid, Ds\n from log_iMonkey_Wechat_overview\n where PlatformType != 0 and Debug = 1\n GROUP BY PlatformType, Ds\n )\n GROUP BY Ds\n order by Ds\n ) as A \n ) as A\n JOIN t as ts,\n arrayEnumerate(t) as i on ts \u003e= toDate(#monthly_active_new_business.start#)\n) as AA\nleft join \n(\n SELECT ts,\n total_increase[i] as total_increase_num\n FROM \n (\n SELECT groupArray(Ds) as t,\n groupArray(app_count) as total_increase\n from (\n SELECT min_Ds as Ds,\n count(distinct appid) as app_count\n FROM (\n select AppIdentifier as appid,\n min(Ds) as min_Ds\n from log_iMonkey_iOS_overview\n GROUP BY AppIdentifier\n union all\n select AppIdentifier as appid,\n min(Ds) as min_Ds\n from log_iMonkey_Android_overview\n where Debug = 1\n GROUP BY AppIdentifier\n union all\n select CAST(PlatformType AS String) as appid,\n min(Ds) as min_Ds\n where PlatformType != 0 and Debug = 1\n from log_iMonkey_Wechat_overview\n GROUP BY PlatformType\n )\n GROUP BY min_Ds\n order by min_Ds\n ) \n ) as A\n JOIN t as ts,\n arrayEnumerate(t) as i on ts \u003e= toDate(#monthly_active_new_business.start#)\n) as CC on AA.ts = CC.ts