Implement and debug event filtering in Python
Company: Affirm
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
You are given a list of event dictionaries with keys: id (str), type (str), ts (int, seconds since epoch), payload (dict). Implement filter_events(events, include_types=None, exclude_types=None, start_ts=None, end_ts=None, limit=None, dedupe_by_id=False) -> List[dict] with the following behavior:
- If include_types is provided, return only events whose type is in include_types.
- If exclude_types is provided, remove events whose type is in exclude_types (after applying include_types if both are provided).
- Keep events whose ts is within the inclusive window [start_ts, end_ts] when the bounds are provided.
- Return results in ascending ts order; if ts ties, preserve original input order (stable sort).
- If limit is provided, return the earliest limit events after filtering and sorting.
- If dedupe_by_id is True, keep only the event with the largest ts for each id.
- Discuss time and space complexity and how you would handle already-sorted input.
Debugging sub-part: Identify and fix the bugs in the following snippets and explain tests that would catch them.
A)
def filter_types(events, t):
return [e for e in events if e['type'] is t]
B)
def filter_events(events, include=set()):
return [e for e in events if e['type'] in include]
Overview: This question evaluates proficiency in data manipulation and algorithmic reasoning in Python, covering event filtering, stable sorting, deduplication by identifier, timestamp windowing, and analysis of time and space complexity.
Filter and deduplicate events with multiple criteria
Write a PostgreSQL query. You are given an events table with one row per event. Each event has an id, type, timestamp (seconds since epoch), a JSON payload, and an integer column event_order that represents the original input order (smaller event_order means the event appeared earlier in the input).
Write a single SQL query to implement the following behavior for this specific scenario:
- include_types = ('click', 'view')
- exclude_types = ('view')
- start_ts = 100
- end_ts = 300
- limit = 3
- dedupe_by_id = TRUE (keep only the event with the largest event_ts per event_id)
Behavior requirements:
1. First, keep only events whose event_type is in include_types.
2. Then, remove events whose event_type is in exclude_types.
3. Then, keep only events whose event_ts is within the inclusive window [start_ts, end_ts].
4. If dedupe_by_id is TRUE, for each event_id keep only the event with the largest event_ts; if there is a tie on event_ts for the same event_id, keep the one with the largest event_order.
5. Return the remaining events ordered by event_ts ascending; if event_ts ties, use event_order ascending to preserve the original relative order.
6. Finally, return only the earliest limit events (limit = 3) after all filtering and deduplication.
Write a query that returns the filtered, deduplicated, and sorted events for this exact configuration.
Tables
events(event_id VARCHAR(10), event_type VARCHAR(20), event_ts INT, payload JSON, event_order INT)
Hints
- Use a window function like ROW_NUMBER() partitioned by event_id to keep only the latest event per id.
- Apply include/exclude filters and the timestamp range inside a subquery, then deduplicate, then order and limit in the outer query.
Fix incorrect equality operator in type filter
Write a PostgreSQL query. A teammate wrote the following SQL to return only 'click' events:
SELECT *
FROM events
WHERE event_type IS 'click';
This query is intended to return all rows whose event_type is exactly 'click'. However, it is incorrect and may not behave as expected.
1. Identify the problem with this query.
2. Write a corrected SQL query that returns all events where event_type is exactly 'click', keeping the original input order (event_order ascending).
Tables
events(event_id VARCHAR(10), event_type VARCHAR(20), event_ts INT, payload JSON, event_order INT)
Hints
- In SQL, the IS operator is typically used for NULL checks (IS NULL, IS NOT NULL), not for comparing text values.
- Use the standard equality operator for comparing event_type to a literal string.
Make an optional type filter behave correctly with an empty list
You want to write a query that optionally filters events by a list of types. If the list is empty, the query should return all events (i.e., no type-based filtering). If the list is non-empty, it should return only events whose type is in the list.
A buggy version of the query is:
WITH params AS (
SELECT ARRAY['click', 'view']::VARCHAR[] AS include_types
)
SELECT e.*
FROM events e
CROSS JOIN params p
WHERE e.event_type = ANY(p.include_types);
This works when include_types has values, but if include_types were an empty array it would return zero rows instead of all rows.
1. Rewrite the query so that:
- When include_types is empty, all rows from events are returned.
- When include_types is non-empty, only rows whose event_type is in include_types are returned.
2. For the sample data below, assume include_types = ARRAY['click','view'] and show the result of your corrected query. Return payload as JSON text in the sample output.
Tables
events(event_id VARCHAR(10), event_type VARCHAR(20), event_ts INT, payload JSON, event_order INT)
Hints
- Think about how to make the WHERE clause optional: when the list is empty, the condition should be TRUE for every row.
- In PostgreSQL, you can use cardinality(array) to detect an empty array and combine it with OR in the WHERE clause.