Morning Warning Logs

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Lyft

Problem

After noticing pre-business-hours warning spikes, the SRE team wants to review every WARN-level server log entry that fired before noon. Return all fields.

Warnings before noon.

Tables

server_logs

Columns: log_id INTEGER, server_name TEXT, log_level TEXT, message TEXT, response_time_ms INTEGER, log_timestamp TEXT

Expected output

Worked solution and explanation

This is a recurring time-of-day filter dressed up as an incident review. 'Before noon' is not a moment on the calendar. It is a window that opens every single day the table covers. Anyone can match log_level = 'WARN'. What separates candidates is filtering on the hour component of log_timestamp, not on the timestamp itself. The weaker answer compares log_timestamp to one noon datetime and gets one day's mornings, or everything before that day. The other trap is pulling the hour out as text and comparing it to the number 12, which is not a numeric comparison at all.

Pull the hour out, then compare it

Step 1: Match the level exactly

log_level holds five values: 'WARN', 'ERROR', 'DEBUG', 'CRITICAL' and 'INFO'. The review is about warnings, so it is an equality on 'WARN'. Sweeping in 'ERROR' or 'CRITICAL' because they are 'at least as bad' answers a different question.

Step 2: Treat log_timestamp as text you have to decompose

The column is TEXT shaped like '2026-02-02 01:07:13'. The hour you need is a sub-field of every row's value, and it repeats every day. STRFTIME('%H', log_timestamp) hands it back, but as the zero-padded string '01'.

Step 3: Cast, then compare strictly below 12

CAST(... AS INTEGER) < 12 turns '07' into 7 before comparing. The strict inequality is the boundary decision: 12:00:00 is noon, not before it. The seed has WARN rows logged in the 12 o'clock hour, so a <= 12 quietly adds them.

Step 4: Return the rows as stored

No grouping, no ordering, all six columns. Six of the 20 matching rows have a NULL response_time_ms. They are still warnings that fired before noon, so nothing should touch that column.

SELECT log_id, server_name, log_level, message, response_time_ms, log_timestamp
FROM server_logs
WHERE log_level = 'WARN'
    AND CAST(STRFTIME('%H', log_timestamp) AS INTEGER) < 12

One noon boundary

log_timestamp < '2026-09-12 12:00:00' keeps every warning up to one particular noon, mornings and afternoons alike. Drop the date and compare to a bare '12:00:00', and you are comparing strings that start with a year, which means nothing at all.

Hour of day

CAST(STRFTIME('%H', log_timestamp) AS INTEGER) < 12 asks the same question of every row regardless of its date. That recurring window is what 'before noon' means here.

Common follow-up questions

• The timestamps are UTC but the on-call team is in UTC-5. How does the filter change? (Tests shifting the timestamp before extracting the hour, not after.)
• Now restrict it to 06:00 up to but not including 09:00 on weekdays. (Tests a bounded hour range plus a day-of-week component from the same `log_timestamp`.)
• Instead of rows, show the number of morning warnings per `server_name` per hour. (Tests grouping on a derived key and keeping the extracted hour in the output.)