I want to get the same result from clickstream query with workspace "Application Time To Complete"
Ran below query but found there's big gap between them. please review my code whether it's correctly written.
SELECT a.as_of_dt,
a.category,
a.product,
a.product1,
sum(a.complete_time) AS complete_time,
count(*) AS complete_cnt
FROM
(SELECT as_of_dt,
split_part(product_list, ';', 1) AS category,
CASE
WHEN split_part(product_list, ';', 1) IN ('ABC',
'DEF',
'XYZ') THEN split_part(product_list, ';', 1)
ELSE split_part(product_list, ';', 2)
END AS product,
split_part(product_list, ';', 2) AS product1,
CASE
WHEN substr(post_evar44, 1, 3) = 'iOS' THEN substr(post_evar57, 1, 6)
ELSE ltrim(split_part(split_part(user_agent, ';', 3), 'Build', 1))
END AS device,
post_evar57 AS mobile_os,
CASE
WHEN translate(post_prop15, 'abcdefghijklmnopqrstuvwxyz', '') = '' THEN 0
ELSE cast(translate(post_prop15, 'abcdefghijklmnopqrstuvwxyz', '') AS DOUBLE)
END AS complete_time
FROM eap_sitecat_hit_data_mob
WHERE as_of_dt LIKE '2019-09%'
AND split_part(product_list, ';', 1) IN ('AB',
'CD',
'EF',
'GH',
'IJ',
'KL')
AND concat(',',event_list,',') LIKE '%,206,%'
AND exclude_hit = '0'
AND hit_source NOT IN ('5',
'8',
'9')) AS a
GROUP BY a.as_of_dt,
a.category,
a.product,
a.product1,
ORDER BY count(*) DESC
