Parse logs and query by time
Company: Oracle
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Implement a parser that converts raw log lines into structured records with fields {timestamp, level, message}. Build an API to support:
(a) insert(logLine),
(b) query(startTime, endTime, levelFilter?) returning records in time order. Explain how you will parse timestamps and time zones, handle malformed lines, and support large-scale data (e.g., indexing by time, batching, or using SQL windowing). Analyze time and space complexity and provide basic tests.
Overview: This question evaluates competency in parsing and structuring log lines, interpreting timestamps and time zones, handling malformed input, designing insert/query APIs, reasoning about scalability and indexing, performing time/space complexity analysis, and planning basic tests.
Read the full Oracle Software Engineer interview experience this question came from
Parse raw log lines into structured fields
You are given application logs stored as raw text lines in `raw_logs`. Each well-formed line has this format:
`YYYY-MM-DD HH:MI:SS LEVEL message...`
The timestamp is UTC and occupies the first 19 characters. `LEVEL` is a single uppercase word, followed by a space and the message text. Some rows are malformed and should be ignored.
Write a PostgreSQL query that returns one row per well-formed log line with these columns:
- `log_ts`: the parsed timestamp rendered as `YYYY-MM-DD HH24:MI:SS`
- `level`: the log level immediately after the timestamp
- `message`: everything after the level and following space
Filter out malformed rows, parse the timestamp from the first 19 characters, and order the result by the parsed timestamp ascending.
Tables
raw_logs(id INT, raw_line VARCHAR(255))
Hints
- Use a regular expression to discard rows that do not match the log-line shape before casting the timestamp.
- Parse the first 19 characters as a timestamp, then split the text that begins at character 21 into level and message.
Query logs by time range and level
Assume you already have a structured `logs` table with one row per log entry, including parsed timestamp, level, and message.
For this query, use these concrete API parameters:
- `startTime = '2025-06-01 00:00:00'`
- `endTime = '2025-06-01 23:59:59'`
- `levelFilter = 'INFO'`
Write a PostgreSQL query that returns all records where `log_ts` is between `startTime` and `endTime` inclusive and `level` equals `levelFilter`. Return `id`, `log_ts`, `level`, and `message`, with `log_ts` rendered as `YYYY-MM-DD HH24:MI:SS`. Order the result by `log_ts` ascending, using `id` as a tiebreaker.
Tables
logs(id INT, log_ts TIMESTAMP, level VARCHAR(10), message VARCHAR(255))
Hints
- Use `BETWEEN` on `log_ts` for the inclusive time window and an `AND` predicate for the level filter.
- Use `TO_CHAR(log_ts, 'YYYY-MM-DD HH24:MI:SS')` to match the timestamp string shown in the expected result.