Generate user notifications from schedules
Company: Stripe
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Given a schedule template format and user profile data (timezone, locale, delivery preferences), implement a program that generates a user notification sheet. The program should merge event data with user information, handle recurring events, perform correct timezone conversions, localize date/time and text, resolve conflicting or overlapping events, and validate missing or malformed fields. Specify input and output schemas, show example transformations, and discuss the time and space complexity of your approach.
Overview: This question evaluates skills in data manipulation and integration, including schema design, merging event and user profile data, recurring event handling, timezone and locale-aware conversions, conflict resolution, and data validation using SQL and Python.
Write a PostgreSQL query. You are given tables describing users, schedule templates, user-specific schedule assignments, and localized message texts. For a single delivery date 2025-06-01, write a SQL query that generates a user notification sheet.
Each schedule template represents a daily recurring notification; the column base_time_utc stores the UTC send-time for the 2025-06-01 occurrence of that template.
Your query should, for the delivery date 2025-06-01:
1. Combine users with the schedule templates assigned to them that are active on that date (based on start_date/end_date and active_flag).
2. Convert the base_time_utc to each user's local time using their timezone_offset_minutes (minutes to add to UTC to get local time).
3. Determine a single delivery_channel per user: use 'push' if allow_push is TRUE; otherwise use 'email' if allow_email is TRUE; if both are FALSE, the user should receive no notifications.
4. Localize the notification text by joining to localized_messages: use the row whose locale matches the user's locale when available; otherwise fall back to the 'en-US' locale for that template.
5. Resolve conflicts: if a user has multiple notifications that would be sent at the exact same local_send_time, only keep the notification with the smallest priority value (highest priority). Discard the others.
6. Filter out malformed templates where base_time_utc IS NULL.
Return one row per final notification to be sent, with the following columns in the result:
- user_id
- user_name
- delivery_channel
- local_send_time (TIMESTAMP in the user’s local time)
- locale (the user’s locale)
- template_id
- notification_text
Produce the SQL query to generate this notification sheet for 2025-06-01.
Tables
users(user_id INT, user_name VARCHAR(50), timezone_offset_minutes INT, locale VARCHAR(10), allow_email BOOLEAN, allow_push BOOLEAN)
schedule_templates(template_id INT, template_name VARCHAR(100), base_time_utc TIMESTAMP, priority INT, active_flag BOOLEAN)
user_schedules(user_id INT, template_id INT, start_date DATE, end_date DATE)
localized_messages(template_id INT, locale VARCHAR(10), message_text VARCHAR(255))
Hints
- First filter to schedules active on 2025-06-01 and users who allow at least one delivery channel before doing any complex logic.
- Use timezone_offset_minutes to shift base_time_utc into local time, COALESCE to implement locale fallback from user locale to en-US, and a ROW_NUMBER window function to drop lower-priority notifications that collide at the same local_send_time.