Records Created or Updated in a Time Window
Pelatro● EasyFULLTIMEONSITE INTERVIEWProblem statement
Find records that were created or updated during the supplied time window. Return each qualifying record’s id once.
For this practice version, Records stores the creation time and the most recent update time, and SyncWindow contains one row with the window bounds.
Table schema
MySQLPostgreSQLPandas
Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.
SyncWindow
| Column | Type | Nullable | Description |
|---|---|---|---|
| start_atPK | Timestamp | No | — |
| end_at | Timestamp | No | — |
Records
| Column | Type | Nullable | Description |
|---|---|---|---|
| idPK | Integer | No | — |
| created_at | Timestamp | No | — |
| updated_at | Timestamp | Yes | — |
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| id | Integer | No | — |
Row order: any order is accepted. Numeric tolerance: 0.
Constraints
- The practice tables are Records(id, created_at, updated_at) and SyncWindow(start_at, end_at). SyncWindow has exactly one row and start_at is strictly earlier than end_at.
- Use a half-open window: include a timestamp at start_at and exclude one at end_at. All timestamps use the same UTC clock, without fractional seconds.
- Records.id is a unique positive integer. created_at is non-null; updated_at may be null, or is at least created_at. A null update time does not prevent qualification by creation time.
- Check the two stored timestamps, not an unavailable history of earlier updates. A row qualifies by either timestamp and must appear only once.
- Records has at most 2000 rows. Output rows may be in any order.