Coding questions · Stripe
Generate user notifications from schedules
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.
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.
Generate User Notification Sheet from Schedules
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:
- Combine users with the schedule templates assigned to them that are active on that date (based on start_date/end_date and active_flag).
- 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).
- 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.
- 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.
- 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.
- 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.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| user_name | VARCHAR(50) | NOT NULL |
| timezone_offset_minutes | INT | NOT NULL |
| locale | VARCHAR(10) | NOT NULL |
| allow_email | BOOLEAN | NOT NULL |
| allow_push | BOOLEAN | NOT NULL |
Sample data:
| allow_email | allow_push | locale | timezone_offset_minutes | user_id | user_name |
|---|---|---|---|---|---|
| true | true | en-US | -240 | 1 | Alice |
| true | false | de-DE | 60 | 2 | Bob |
| false | false | en-IN | 330 | 3 | Carol |
| true | true | fr-FR | 0 | 4 | Dan |
Table schedule_templates
| column | type | constraints |
|---|---|---|
| template_id | INT | PRIMARY KEY, NOT NULL |
| template_name | VARCHAR(100) | NOT NULL |
| base_time_utc | TIMESTAMP | |
| priority | INT | NOT NULL |
| active_flag | BOOLEAN | NOT NULL |
Sample data:
| active_flag | base_time_utc | priority | template_id | template_name |
|---|---|---|---|---|
| true | 2025-06-01 12:00:00 | 1 | 101 | Daily summary |
| true | 2025-06-01 16:00:00 | 2 | 102 | Security digest |
| true | 2025-06-01 12:00:00 | 3 | 103 | Marketing promo |
| true | NULL | 1 | 104 | Broken template (missing time) |
Table user_schedules
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| template_id | INT | NOT NULL |
| start_date | DATE | NOT NULL |
| end_date | DATE |
Sample data:
| end_date | start_date | template_id | user_id |
|---|---|---|---|
| NULL | 2025-01-01 | 101 | 1 |
| 2025-12-31 | 2025-05-01 | 102 | 1 |
| NULL | 2025-02-01 | 101 | 2 |
| 2025-06-30 | 2025-05-15 | 103 | 2 |
| NULL | 2025-01-01 | 101 | 3 |
| NULL | 2025-01-01 | 101 | 4 |
| NULL | 2025-06-01 | 102 | 4 |
| NULL | 2025-06-01 | 104 | 4 |
Table localized_messages
| column | type | constraints |
|---|---|---|
| template_id | INT | NOT NULL |
| locale | VARCHAR(10) | NOT NULL |
| message_text | VARCHAR(255) | NOT NULL |
Sample data:
| locale | message_text | template_id |
|---|---|---|
| en-US | Good morning, here is your summary. | 101 |
| de-DE | Guten Morgen, hier ist Ihre Übersicht. | 101 |
| fr-FR | Bonjour, voici votre récapitulatif. | 101 |
| en-US | Your security alerts for today. | 102 |
| fr-FR | Vos alertes de sécurité du jour. | 102 |
| en-US | Daily special promotion. | 103 |
Expected output
Notification sheet for delivery date 2025-06-01 after applying user preferences, local time conversion, localization with fallback to en-US, conflict resolution by priority, and filtering malformed templates. Ordered here by user_id and local_send_time for readability.
| delivery_channel | local_send_time | locale | notification_text | template_id | user_id | user_name |
|---|---|---|---|---|---|---|
| push | 2025-06-01 08:00:00 | en-US | Good morning, here is your summary. | 101 | 1 | Alice |
| push | 2025-06-01 12:00:00 | en-US | Your security alerts for today. | 102 | 1 | Alice |
| 2025-06-01 13:00:00 | de-DE | Guten Morgen, hier ist Ihre Übersicht. | 101 | 2 | Bob | |
| push | 2025-06-01 12:00:00 | fr-FR | Bonjour, voici votre récapitulatif. | 101 | 4 | Dan |
| push | 2025-06-01 16:00:00 | fr-FR | Vos alertes de sécurité du jour. | 102 | 4 | Dan |
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.