Coding questions · eBay
Write SQL with window functions for analytics
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.
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.
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:
- the total value for that day, and
- 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.
Table events
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| event_time | TIMESTAMP | NOT NULL |
| event_type | VARCHAR(20) | NOT NULL |
| value | DECIMAL(10,2) |
Sample data:
| event_time | event_type | user_id | value |
|---|---|---|---|
| 2025-05-01 10:00:00 | click | 1 | 5 |
| 2025-05-03 12:00:00 | purchase | 1 | 20 |
| 2025-05-05 09:00:00 | click | 1 | NULL |
| 2025-05-06 18:30:00 | purchase | 1 | 10 |
| 2025-05-10 14:15:00 | click | 1 | 8 |
| 2025-05-02 08:00:00 | click | 2 | NULL |
| 2025-05-04 16:45:00 | purchase | 2 | 15 |
| 2025-05-04 16:45:00 | refund | 2 | -5 |
| 2025-05-07 11:20:00 | click | 2 | 3 |
| 2025-05-09 19:00:00 | purchase | 2 | 7 |
Expected output
For each (user_id, event_date), return the daily sum of value (treating NULL as 0) and the rolling 7-row window sum over that user's daily totals, ordered by date.
| daily_value | event_date | rolling_7d_value | user_id |
|---|---|---|---|
| 5 | 2025-05-01 | 5 | 1 |
| 20 | 2025-05-03 | 25 | 1 |
| 0 | 2025-05-05 | 25 | 1 |
| 10 | 2025-05-06 | 35 | 1 |
| 8 | 2025-05-10 | 43 | 1 |
| 0 | 2025-05-02 | 0 | 2 |
| 10 | 2025-05-04 | 10 | 2 |
| 3 | 2025-05-07 | 13 | 2 |
| 7 | 2025-05-09 | 20 | 2 |
Hints
- First aggregate events to daily totals per user in a CTE or subquery, then apply a window function to those daily rows.
- 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.
Table events
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| event_time | TIMESTAMP | NOT NULL |
| event_type | VARCHAR(20) | NOT NULL |
| value | DECIMAL(10,2) |
Sample data:
| event_time | event_type | user_id | value |
|---|---|---|---|
| 2025-05-01 10:00:00 | click | 1 | 5 |
| 2025-05-03 12:00:00 | purchase | 1 | 20 |
| 2025-05-05 09:00:00 | click | 1 | NULL |
| 2025-05-06 18:30:00 | purchase | 1 | 10 |
| 2025-05-10 14:15:00 | click | 1 | 8 |
| 2025-05-02 08:00:00 | click | 2 | NULL |
| 2025-05-04 16:45:00 | purchase | 2 | 15 |
| 2025-05-04 16:45:00 | refund | 2 | -5 |
| 2025-05-07 11:20:00 | click | 2 | 3 |
| 2025-05-09 19:00:00 | purchase | 2 | 7 |
Expected output
Return each event with a per-user chronological rank, breaking ties on event_time by event_type and then value, so ordering is deterministic.
| event_rank | event_time | event_type | user_id | value |
|---|---|---|---|---|
| 1 | 2025-05-01 10:00:00 | click | 1 | 5 |
| 2 | 2025-05-03 12:00:00 | purchase | 1 | 20 |
| 3 | 2025-05-05 09:00:00 | click | 1 | NULL |
| 4 | 2025-05-06 18:30:00 | purchase | 1 | 10 |
| 5 | 2025-05-10 14:15:00 | click | 1 | 8 |
| 1 | 2025-05-02 08:00:00 | click | 2 | NULL |
| 2 | 2025-05-04 16:45:00 | purchase | 2 | 15 |
| 3 | 2025-05-04 16:45:00 | refund | 2 | -5 |
| 4 | 2025-05-07 11:20:00 | click | 2 | 3 |
| 5 | 2025-05-09 19:00:00 | purchase | 2 | 7 |
Hints
- Use a ranking window function PARTITION BY user_id and ORDER BY event_time.
- 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.
Table events
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| event_time | TIMESTAMP | NOT NULL |
| event_type | VARCHAR(20) | NOT NULL |
| value | DECIMAL(10,2) |
Sample data:
| event_time | event_type | user_id | value |
|---|---|---|---|
| 2025-05-01 10:00:00 | click | 1 | 5 |
| 2025-05-03 12:00:00 | purchase | 1 | 20 |
| 2025-05-05 09:00:00 | click | 1 | NULL |
| 2025-05-06 18:30:00 | purchase | 1 | 10 |
| 2025-05-10 14:15:00 | click | 1 | 8 |
| 2025-05-02 08:00:00 | click | 2 | NULL |
| 2025-05-04 16:45:00 | purchase | 2 | 15 |
| 2025-05-04 16:45:00 | refund | 2 | -5 |
| 2025-05-07 11:20:00 | click | 2 | 3 |
| 2025-05-09 19:00:00 | purchase | 2 | 7 |
Expected output
For each event row, return the most recent non-null value for that user up to and including that event, ordered chronologically per user.
| event_time | event_type | last_non_null_value | user_id | value |
|---|---|---|---|---|
| 2025-05-01 10:00:00 | click | 5 | 1 | 5 |
| 2025-05-03 12:00:00 | purchase | 20 | 1 | 20 |
| 2025-05-05 09:00:00 | click | 20 | 1 | NULL |
| 2025-05-06 18:30:00 | purchase | 10 | 1 | 10 |
| 2025-05-10 14:15:00 | click | 8 | 1 | 8 |
| 2025-05-02 08:00:00 | click | NULL | 2 | NULL |
| 2025-05-04 16:45:00 | purchase | 15 | 2 | 15 |
| 2025-05-04 16:45:00 | refund | -5 | 2 | -5 |
| 2025-05-07 11:20:00 | click | 3 | 2 | 3 |
| 2025-05-09 19:00:00 | purchase | 7 | 2 | 7 |
Hints
- 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.
- 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.