Where the Noise Lives

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

City and County of San Francisco

Problem

Our on-call team is figuring out which days of the week the servers run the noisiest. Show each day's name with how many log entries it sees.

Some days are noisier than others.

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

What this is really asking

Strip the 'server logs' costume and this is a histogram over a derived dimension: bucket every row by the weekday hiding inside its timestamp, then count the buckets. The data does not hand you a weekday column, so the whole problem is one move: manufacture the grouping key from log_timestamp before you can group on it. Candidates who try to GROUP BY log_timestamp itself get 31 million groups (one per distinct second) instead of seven. The grain you GROUP BY is the grain of your answer, and here you have to build that grain yourself.

The trap: it is a text column

log_timestamp is stored as TEXT, not a native date. In SQLite there is no EXTRACT(DOW FROM ...); reach for it and the query will not even parse. You pull the weekday with strftime('%w', log_timestamp), which returns a one-character string '0' (Sunday) through '6' (Saturday). Two traps live here: first, it is a string, so compare it against '0'..'6', not the integers 0..6; second, '%w' alone gives you a number nobody wants to read in a report, so you still owe the mapping to a real day name.

Step 1: Build the grouping key from the timestamp

strftime('%w', log_timestamp) turns each text timestamp into a weekday code '0'-'6'. This is the SQLite stand-in for Postgres EXTRACT(DOW ...). Decide on this expression first; everything else hangs off it.

Step 2: Label it, then group and count

Wrap the code in a searched CASE that maps '0' to 'Sunday' through '6' to 'Saturday'. Group on that labeled expression and COUNT(*) the rows in each bucket. One row per weekday name falls out.

Step 3: Order with a deterministic tiebreak

Sort entry_count descending for most-to-fewest. Add the weekday code as a second sort key so two days with identical counts always land in the same order instead of shuffling between runs.

The solution

SELECT
    CASE
        WHEN strftime('%w', log_timestamp) = '0' THEN 'Sunday'
        WHEN strftime('%w', log_timestamp) = '1' THEN 'Monday'
        WHEN strftime('%w', log_timestamp) = '2' THEN 'Tuesday'
        WHEN strftime('%w', log_timestamp) = '3' THEN 'Wednesday'
        WHEN strftime('%w', log_timestamp) = '4' THEN 'Thursday'
        WHEN strftime('%w', log_timestamp) = '5' THEN 'Friday'
        WHEN strftime('%w', log_timestamp) = '6' THEN 'Saturday'
    END AS day_of_week,
    COUNT(*) AS entry_count
FROM server_logs
GROUP BY day_of_week

GROUP BY `log_timestamp`

Groups on the full second-resolution timestamp: up to ~31M distinct groups, one per logged instant. The 'per day of week' requirement is silently lost and the result is unusable.

GROUP BY derived weekday

Groups on the strftime-derived label: exactly the weekdays present, at most seven rows. This is the grain the question asks for.

Common follow-up questions

• A weekday with zero logs is missing from your output entirely. How would you make all seven days appear, with 0 for the empty ones? (Tests whether they know a plain GROUP BY cannot emit absent groups and that a calendar/numbers table left-joined in is the fix.)
• These timestamps are UTC. If the business wants weekday volume in US Pacific time, what changes? (Tests timezone awareness: strftime works in UTC, so weekday boundaries can be off for non-UTC events.)
• The table is partitioned by day on `log_timestamp` stored as text. What would you change so a single-month version of this query prunes partitions instead of full-scanning? (Tests partition-pruning and storage-type reasoning on a 70M-row table.)