Quick Overview

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.

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.

Overview: 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.

Read the full eBay Software Engineer interview experience this question came from

Rolling 7-day sum of event values per user and day

You are given a table events(user_id, event_time, event_type, value) that stores user activity. Write a SQL query to compute, for each user and each calendar date on which they have at least one event, both: 1) the total value for that day, and 2) a rolling 7-day sum of value that includes that day and the previous 6 days, based on daily totals. Treat NULL in value as 0 when summing. Use window functions with PARTITION BY user_id, ORDER BY event_date, and a ROWS BETWEEN frame applied over daily totals. Also state what index you would recommend on events to make this query efficient on large datasets.

Tables

events(user_id INT, event_time TIMESTAMP, event_type VARCHAR(20), value DECIMAL(10,2))

Hints

  1. First aggregate events to daily totals per user in a CTE or subquery, then apply a window function to those daily rows.
  2. Use ROWS BETWEEN 6 PRECEDING AND CURRENT ROW over the ordered event_date to get a 7-day rolling window.

Deterministic chronological ranking of user events

Using the same events(user_id, event_time, event_type, value) table, write a SQL query to rank each user's events in strict chronological order. Use a window function to assign a rank per user that increases by 1 for each successive event. When multiple events for the same user have the same event_time, break ties deterministically by ordering on event_type and then value. Use RANK (or an equivalent ranking window function) with PARTITION BY/ORDER BY. Also mention what index you would recommend to support this query efficiently. Return event_time formatted as YYYY-MM-DD HH24:MI:SS in the sample output.

Tables

events(user_id INT, event_time TIMESTAMP, event_type VARCHAR(20), value DECIMAL(10,2))

Hints

  1. Use a ranking window function PARTITION BY user_id and ORDER BY event_time.
  2. Add event_type and value to the ORDER BY clause so that rows with the same timestamp are ordered deterministically.

Last non-null value seen so far per user

Using the same events(user_id, event_time, event_type, value) table, write a SQL query that returns, for every event row, the last non-null value seen so far for that user in chronological order. If a user has not yet had any non-null value, the result should be NULL for those early rows. Use a window function with LAST_VALUE (or an equivalent construct) over a frame that starts at the first event for the user and ends at the current row, and make sure null values do not overwrite the last non-null value. Order events per user chronologically, breaking ties on event_time using event_type and value. Also mention what index you would recommend for efficient execution. Return event_time formatted as YYYY-MM-DD HH24:MI:SS in the sample output.

Tables

events(user_id INT, event_time TIMESTAMP, event_type VARCHAR(20), value DECIMAL(10,2))

Hints

  1. Use LAST_VALUE as a window function over a frame from UNBOUNDED PRECEDING to CURRENT ROW so it only looks at rows up to the current one.
  2. If your SQL dialect supports it, use the IGNORE NULLS modifier with LAST_VALUE so that null values do not overwrite the previous non-null value.

Loading coding console...