Katkat
Back

Write SQL for session analytics

ShopifyData Engineer · Technical Screen
EasyData EngineerTechnical ScreenCoding & Algorithms

Last updated

This problem tests your ability to clean event-based log data, perform time-series aggregation, and calculate session-level metrics using SQL window functions.

Problem Statement

You are working with a table named page_views that tracks user activity on an e-commerce platform. Because of network retries, the table contains duplicate entries for the same event. A session is defined as a sequence of activity where no two consecutive page views are more than 30 minutes apart.

Schema

Table: page_views

  • event_id: VARCHAR (Primary Key)
  • user_id: VARCHAR
  • page_url: VARCHAR
  • timestamp: TIMESTAMP

Task

  1. Deduplicate the data based on event_id.
  2. Assign a session_id to each activity. A new session starts if the time elapsed since the user's previous page view is greater than 30 minutes.
  3. Output a summary table containing user_id, session_id, session_start_time, session_end_time, and page_count (number of unique pages visited).

Example

Input:

event_iduser_idtimestamp
1A2023-10-01 10:00:00
2A2023-10-01 10:15:00
3A2023-10-01 10:50:00

Output:

user_idsession_idsession_startsession_endpage_count
A110:00:0010:15:002
A210:50:0010:50:001

Constraints

  • Assume timestamp is in UTC.
  • Your solution should handle high volumes of data efficiently using standard window functions.
  • Consider cases where a user has multiple sessions in a single day.

Hints

Hint 1

Use a Common Table Expression (CTE) to perform a `DISTINCT` operation or use `ROW_NUMBER()` to filter out duplicate `event_id`s.

Hint 2

Use the `LAG()` window function to calculate the time difference between the current row's timestamp and the previous row's timestamp for each user.

Hint 3

Use a cumulative sum over a boolean flag (where the gap > 30 minutes) to generate a unique session identifier for each user.

Follow-up questions

  • How would you modify your query to extract the 'product_id' parameter from the `page_url` string?
  • If the dataset grows to billions of rows, how would you partition the `page_views` table to optimize the performance of this sessionization query?
  • How would you account for sessions that span across midnight?