Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

Application complete time measure with clickstream query

Avatar

Avatar
Level 1
vince2020
Level 1

Likes

0 likes

Total Posts

1 post

Correct Reply

0 solutions
View profile

Avatar
Level 1
vince2020
Level 1

Likes

0 likes

Total Posts

1 post

Correct Reply

0 solutions
View profile
vince2020
Level 1

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)