Katkat
Back

Calculate Average Camera Switches per Session

SnapchatData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

Tests the ability to write a PostgreSQL query using window functions (LAG) to compute the average number of specific state transitions per session while accounting for zero-transition sessions and avoiding integer division.

Problem Description

Snapchat's camera team is analyzing how users interact with the camera interface during active sessions. When using the app, users can flip between the 'front' (selfie) and 'rear' (world-facing) camera modes.

Write a PostgreSQL query to calculate the average number of front-to-rear camera switches per session.

A front-to-rear switch is defined as an event with camera_mode = 'rear' immediately preceded by an event with camera_mode = 'front' within the same session, ordered chronologically by event_time.

Requirements:

  • All sessions present in camera_events must be included in the denominator, even if a session contains only one event or zero switches.
  • Round your final result to 2 decimal places.
  • Ensure calculations avoid integer-division truncation.

Schema

Table: camera_events

+-------------+-------------+
| Column Name | Type        |
+-------------+-------------+
| event_id    | INT         |
| session_id  | VARCHAR     |
| camera_mode | VARCHAR     | -- 'front' or 'rear'
| event_time  | TIMESTAMP   |
+-------------+-------------+

Example

Input

camera_events:
+----------+------------+-------------+---------------------+
| event_id | session_id | camera_mode | event_time          |
+----------+------------+-------------+---------------------+
| 1        | s1         | front       | 2024-01-01 10:00:00 |
| 2        | s1         | rear        | 2024-01-01 10:00:05 |
| 3        | s1         | front       | 2024-01-01 10:00:10 |
| 4        | s1         | rear        | 2024-01-01 10:00:15 |
| 5        | s2         | rear        | 2024-01-01 11:00:00 |
| 6        | s2         | front       | 2024-01-01 11:00:04 |
| 7        | s3         | front       | 2024-01-01 12:00:00 |
+----------+------------+-------------+---------------------+

Output

+-----------------------+
| avg_switches_per_sess |
+-----------------------+
| 0.67                  |
+-----------------------+

Explanation

  • Session s1: Has 2 front-to-rear switches (event 1 -> 2, event 3 -> 4).
  • Session s2: Has 0 front-to-rear switches (rear -> front is not front-to-rear).
  • Session s3: Single event, 0 switches.
  • Total switches = 2 + 0 + 0 = 2.
  • Total sessions = 3.
  • Average = 2 / 3 = 0.6667, rounded to 0.67.

Edge Cases to Consider

  • Sessions with only a single event must produce 0 switches, not be excluded from the session count.
  • Consecutive events with the same mode (e.g., 'front' followed by 'front') should not be counted as a switch.
  • Empty table should return 0.00 or NULL.

Hints

Hint 1

Use the window function LAG(camera_mode) OVER (PARTITION BY session_id ORDER BY event_time) to inspect the previous camera state for each event.

Hint 2

Aggregate the switch count per session first (or sum conditional switch flags across each session) before computing the outer average across all sessions.

Hint 3

Make sure to cast counts to numeric or float (e.g., `COUNT(...)::NUMERIC` or `AVG(...)`) to prevent PostgreSQL from performing integer division.

Follow-up questions

  • How would you modify this query if sessions were not explicitly labeled and instead had to be derived dynamically using a 30-minute inactivity threshold?
  • If the table contains hundreds of millions of events, how would you optimize the indexing or table partitioning strategy to support this query efficiently?
  • How would you extend this query to output both front-to-rear and rear-to-front transition rates side-by-side in a single scan?