Skip to content

Instantly share code, notes, and snippets.

@konosp

konosp/1_filterRows.sql Secret

Last active Sep 23, 2019
Embed
What would you like to do?
Measuring time in funnel steps - SQL
SELECT
CONCAT(post_visid_high, POST_VISID_LOW) as adobe_id
, visit_num
, DATE_TIME as hit_time
CASE
WHEN (instr(POST_EVENT_LIST, '211') > 0) THEN 'start'
WHEN (instr(POST_EVENT_LIST, '236') > 0) THEN 'end'
ELSE POST_PROP1
END as registration_event_type
from
[table name]
WHERE
DATE_TIME >= TO_DATE('01.07.2019 00:00:00', 'dd.mm.yyyy hh24:mi:ss')
AND DATE_TIME <= TO_DATE('01.07.2019 00:30:00', 'dd.mm.yyyy hh24:mi:ss')
AND ((instr(POST_EVENT_LIST, '211') > 0) OR -- Registration Start
(instr(POST_EVENT_LIST, '236') > 0) OR -- Registration End
(POST_PROP1 is not null))
select
adobe_id
, visit_num
, registration_event_type
, min(hit_time) as timing
from
t
group by
adobe_id,
visit_num,
registration_event_type
select
adobe_id
, visit_num
, registration_event_type
, trunc(TIMING) AS dt
, round((timing - (min(timing) OVER (partition by adobe_id, visit_num order by timing asc))) * 24 * 60 * 60) AS time_diff_secs_start
from
t2
SELECT
registration_event_type
, round(AVG(time_diff_secs_previous_step),0) AS avg_step
, MEDIAN(time_diff_secs_previous_step) AS median_step
, round(AVG(time_diff_secs_start), 0) AS avg_start
, MEDIAN(time_diff_secs_start) AS median_start
FROM
t3
GROUP BY
registration_event_type
ORDER BY
MEDIAN(time_diff_secs_start) asc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment