Report not found

There was a problem finding this report.
Mapping every Event to a Session - With a Join
March 19, 2015 · Refreshed 7 months ago
Description
How to map every event to a session, using a join.

Collaborators

Run History
SELECT e.user_id, e.occurred_at, e.event_name, bounds.user_session_number FROM ( SELECT *, LEAD(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS next_session_start, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY occurred_at) AS user_session_number FROM ( SELECT user_id, occurred_at, LAG(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event FROM tutorial.playbook_events ) last WHERE EXTRACT('EPOCH' FROM occurred_at) - EXTRACT('EPOCH' FROM last_event) >= (60 * 10) OR last_event IS NULL ) bounds JOIN tutorial.playbook_events e ON e.user_id = bounds.user_id AND e.occurred_at >= bounds.occurred_at AND (e.occurred_at < bounds.next_session_start OR bounds.next_session_start IS NULL) ORDER BY 1,2
SELECT e.user_id, e.occurred_at, e.event_name, bounds.user_session_number FROM ( SELECT *, LEAD(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS next_session_start, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY occurred_at) AS user_session_number FROM ( SELECT user_id, occurred_at, LAG(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event FROM tutorial.playbook_events ) last WHERE EXTRACT('EPOCH' FROM occurred_at) - EXTRACT('EPOCH' FROM last_event) >= (60 * 10) OR last_event IS NULL ) bounds JOIN tutorial.playbook_events e ON e.user_id = bounds.user_id AND e.occurred_at >= bounds.occurred_at AND (e.occurred_at < bounds.next_session_start OR bounds.next_session_start IS NULL) ORDER BY 1,2
<div class="mode-header embed-hidden"> <h1>{{ title }}</h1> <p>{{ description }}</p> </div> <div class="container mode-grid"> <div class="row"> <div class="col-md-12"> <mode-table id="table_0d572f92914e" dataset="dataset" options="table_options"></mode-table> </div> </div> </div>
{{ dataSourceName(params.queryId) }}

The dataset is too large to view in browser

Export

Looks like something went wrong with your query.

{{ DS.queryRuns[params.queryId].errorMessage }}
This query was cancelled