Application complete time measure with clickstream query

vince2020

07-11-2019

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

Accepted Solutions (0)

Answers (0)