Event Log Processing And Sessionization

What's being tested

These questions test sessionization and event-level deduplication skills: turning raw, timezone-stamped logs into user sessions and summary metrics. Interviewers probe practical mastery of timezone-aware timestamps, session-gap logic, and building funnel/conversion metrics reliably from noisy event streams.

Patterns & templates

• Use `pandas` pd.to_datetime(..., utc=True) then dt.tz_convert() to make timestamps timezone-consistent before any delta math.
• Sort by (user_id, timestamp) and use pd.groupby('user_id')['timestamp'].shift() plus > session_gap to mark new sessions.
• Convert boolean new-session flags to session ids with cumsum() (linear memory) and then groupby(['user_id','session_id']) for session-level aggregates.
• For SQL, use window functions like LAG(timestamp) OVER (PARTITION BY user ORDER BY ts) and ROW_NUMBER() OVER (PARTITION BY user, session_id ORDER BY ts) to dedupe/order.
• Deduplicate by stable event id: df.drop_duplicates(subset=['event_id']) or de-dupe by (user,event_type,ts) within a tolerance window for id-less logs.
• Build funnels with user-level pivoting or CASE WHEN counts per session; compute proportions and bootstrapped CIs for uncertainty.
• Complexity: sorting dominates -> O(n log n) time, O(n) memory; stream/chunk approaches if data exceeds memory.

Tip: compute intermediate flags (is_new_session, is_first_event) early and persist them — they simplify later aggregations and correctness checks.

Common pitfalls

Pitfall: Treating timestamps as naive (no timezone) leads to wrong session gaps across DST or multi-region users.

Pitfall: Counting every event without deduplication inflates sessions and conversion rates; always verify event-id uniqueness.

Pitfall: Using a single global session gap (e.g., 30m) without validating against product behavior or mobile backgrounding assumptions can misattribute sessions.

Practice these

The practice cards below cover the canonical variants — solve all of them and time yourself.