Codes produce different results from the one shown in SQL Analytics Training

Hi all,

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,

Tung Nguyen

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


 

1reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hey Tung Nguyen ,

    Thanks for writing in and apologies for the delayed response. Are you still running into this issue? 

    Like
Like Follow
  • 11 days agoLast active
  • 1Replies
  • 426Views
  • 2 Following