Write SQL with window functions for analytics
Company: eBay
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Onsite
Given a table events(user_id, event_time, event_type, value), write SQL to: a) compute each user's rolling 7-day sum of value by day, b) rank each user's events chronologically with deterministic tie-breaking, and c) return, for every event, the last non-null value seen so far per user. Use window functions (PARTITION BY/ORDER BY, ROWS BETWEEN, RANK, LAST_VALUE with appropriate framing). Explain any indexing or partitioning choices for performance.
Quick Answer: This question evaluates understanding and practical use of SQL window functions—specifically rolling 7-day aggregates, deterministic chronological ranking, and retrieving the last non-null value per partition—while also probing awareness of framing, ordering, and performance trade-offs like indexing and partitioning.