Window Functions and Time Series Analytics Questions
Advanced SQL window functions: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and aggregate functions (SUM, COUNT, AVG) with OVER and partition clauses. Using window functions to solve practical problems: ranking users or events within segments, calculating running totals and cumulative metrics, identifying trends and transitions over time, detecting patterns in user behavior sequences. Applications: cohort retention analysis (calculating retention rates across cohorts), user lifetime value trends, engagement metrics over time windows, and sequential user actions.
EasyTechnical
0 practiced
You have events(user_id INT, event_time TIMESTAMP, event_type TEXT). Write SQL using LAG to compute the time difference in seconds between a user's consecutive events ordered by event_time. Show how you handle the first event per user (which has no previous event). Provide a short sample and expected output.
EasyTechnical
0 practiced
Write a SQL query to find the top 3 highest-value sessions per user from sessions(session_id TEXT, user_id INT, session_start TIMESTAMP, session_value NUMERIC) using window functions. Return user_id, session_id, session_value, and rank, ordered by user_id then rank.
EasyTechnical
0 practiced
When using LAG or LEAD, what are common pitfalls related to incorrect PARTITION BY usage or duplicated timestamps? Describe at least three pitfalls and mitigation strategies for each.
HardTechnical
0 practiced
You need to attach the most recent profile as of event_time for billions of events joined to a large profiles table. Propose an optimized plan: lateral joins, window function pre-aggregation of latest profile per user partitioned by effective_date, or stateful enrichment in streaming. Provide SQL or pseudocode and discuss indexing, partitioning, and cost trade-offs.
EasyTechnical
0 practiced
Explain the role of the OVER() clause in SQL window functions. Provide two short examples: 1) an aggregate without OVER (GROUP BY), and 2) the same aggregate with OVER to show per-row context. Explain default behavior when PARTITION BY or ORDER BY are omitted.
Unlock Full Question Bank
Get access to hundreds of Window Functions and Time Series Analytics interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.