Reviewed by Aditya Kumar · Last reviewed 2026-03-24
If session_id exists: SELECT COUNT(DISTINCT session_id) AS unique_sessions FROM user_events. For session logic (30-min inactivity): Use LAG + CASE for session boundaries; SUM(session_flag) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id. **Why**: Define session...
If session_id exists: SELECT COUNT(DISTINCT session_id) AS unique_sessions FROM user_events. For session logic (30-min inactivity): Use LAG + CASE for session boundaries; SUM(session_flag) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id. Why: Define session (time-based, event-based) consistently. Scalability: Pre-aggregate in ETL; use HyperLogLog for approximate distinct at scale.
Red Flag: Ad-hoc session logic differing from product definition. Pro-Move: 'Session logic in transform layer; HLL for approximate DAU/MAU.'
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.