Codes produce different results from the one shown in SQL Analytics Training
I've just noticed that the code below for the graph "Percent of Sessions with Search Runs and Autocompletes" problem in "Understanding Search Functionality: Answer" in SQL Analytics Training has produced different result from what the graph and result table indicated.
I copy the code that was previously written by Benn Stancil in September 9, 2014 into my SQL editor in Mode Analytics and run it and the results I received are actually bigger than the results in this draft.
Is there something wrong here?
Thank you so much for your replies,
P/s: I don't know which class my question belongs to so I just put it in General Questions. Sorry if I did wrong.
SELECT DATE_TRUNC('week',z.session_start) AS week, COUNT(*) AS sessions, COUNT(CASE WHEN z.autocompletes > 0 THEN z.session ELSE NULL END) AS with_autocompletes, COUNT(CASE WHEN z.runs > 0 THEN z.session ELSE NULL END) AS with_runs FROM ( SELECT x.session_start, x.session, x.user_id, COUNT(CASE WHEN x.event_name = 'search_autocomplete' THEN x.user_id ELSE NULL END) AS autocompletes, COUNT(CASE WHEN x.event_name = 'search_run' THEN x.user_id ELSE NULL END) AS runs, COUNT(CASE WHEN x.event_name LIKE 'search_click_%' THEN x.user_id ELSE NULL END) AS clicks FROM ( SELECT e.*, session.session, session.session_start FROM tutorial.yammer_events e LEFT JOIN ( SELECT user_id, session, MIN(occurred_at) AS session_start, MAX(occurred_at) AS session_end FROM ( SELECT bounds.*, CASE WHEN last_event >= INTERVAL '10 MINUTE' THEN id WHEN last_event IS NULL THEN id ELSE LAG(id,1) OVER (PARTITION BY user_id ORDER BY occurred_at) END AS session FROM ( SELECT user_id, event_type, event_name, occurred_at, occurred_at - LAG(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event, LEAD(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) - occurred_at AS next_event, ROW_NUMBER() OVER () AS id FROM tutorial.yammer_events e WHERE e.event_type = 'engagement' ORDER BY user_id,occurred_at ) bounds WHERE last_event >= INTERVAL '10 MINUTE' OR next_event >= INTERVAL '10 MINUTE' OR last_event IS NULL OR next_event IS NULL ) final GROUP BY 1,2 ) session ON e.user_id = session.user_id AND e.occurred_at >= session.session_start AND e.occurred_at <= session.session_end WHERE e.event_type = 'engagement' ) x GROUP BY 1,2,3 ) z GROUP BY 1 ORDER BY 1