After Hours API Calls

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

Domain: SQL
Difficulty: medium
Seniority: L3
Asked in: Vizient

Problem

Overnight and weekend traffic in api_calls looks heavier than the compliance team's 2025 audit expected. Count the calls made that year outside business hours, which run Monday to Friday from 09:00 up to 16:00, and return the total as a decimal value.

The office is dark. The API is not.

Tables

api_calls

Columns: call_id INTEGER, endpoint TEXT, method TEXT, status INTEGER, latency REAL, user_id INTEGER, call_time TEXT, err_msg TEXT

api_calls: 10 of 670 rows
call_id	endpoint	method	status	latency	user_id	call_time	err_msg
8079	/api/v1/orders	POST	200	4.2	100	2026-02-02 01:07:00	NULL
8158	/api/v2/products	PUT	200	7.9	197	2026-03-03 02:14:00	NULL
8237	/api/v1/search	DELETE	201	11.6	294	2026-04-04 03:21:00	NULL
8316	/api/v1/auth/login	PATCH	204	15.3	391	2026-05-05 04:28:00	NULL
8395	/api/v1/auth/logout	get	301	19	NULL	2026-06-06 05:35:00	NULL
8474	/api/v2/analytics	post	400	22.7	585	2026-07-07 06:42:00	Not found
8553	/api/v1/payments	GET	401	26.4	682	2026-08-08 07:49:00	Unauthorized
8632	/api/v1/users/	POST	403	30.1	779	2026-09-09 08:56:00	Rate limit exceeded
8711	/api/v2/products/	PUT	404	33.8	876	2026-01-10 09:03:00	Internal server error
8790	/api/v1/users	DELETE	500	37.5	NULL	2026-02-11 10:10:00	Bad gateway

Expected output

First 1 of 1 rows
after_hours_count
21

Worked solution and explanation

What this really is

This is a union of calendar conditions dressed up as a compliance audit. A call is out-of-hours if it falls on a weekend, OR it comes before 09:00, OR it comes at 16:00 or later. Almost everyone writes the hour test. What separates candidates is the weekend branch: an hour-only filter quietly treats a Saturday 11:00 call as business as usual. Drop that branch and the audit undercounts exactly the traffic compliance worries about most.

Step 1: Scope to the audit year

Filter api_calls to strftime('%Y', call_time) = '2025'. This conjunct sits outside the OR group, so it applies to every branch. Without the parentheses, a stray OR would pull early-morning calls from every year back into the count.

Step 2: Extract day and hour, then convert them

strftime('%w', ...) and strftime('%H', ...) return text such as '6' and '08'. Wrap each one in CAST(... AS INTEGER) before comparing it to a number. Sunday is 0 and Saturday is 6, so the weekend test is IN (0, 6).

Step 3: OR the three conditions

Weekend, hour < 9 and hour >= 16 are independent ways for a call to qualify. A call that matches two of them is still one row, so it is counted once with no extra work.

Step 4: Count as a decimal

CAST(COUNT(*) AS REAL) returns the total in the type the statement asks for.

Out-of-hours calls, 2025

SELECT CAST(COUNT(*) AS REAL) AS after_hours_count
FROM api_calls
WHERE strftime('%Y', call_time) = '2025'
  AND (
    CAST(strftime('%w', call_time) AS INTEGER) IN (0, 6)
    OR CAST(strftime('%H', call_time) AS INTEGER) < 9
    OR CAST(strftime('%H', call_time) AS INTEGER) >= 16
  )

One year filter, then three independent ways to be out-of-hours.

Clock only: hour < 9 OR hour >= 16 scores Saturday at 11:00 as in-hours, so every weekend daytime call vanishes from the audit.
Clock or calendar: Adding IN (0, 6) as a third OR branch catches the weekend whatever the clock says.

Common follow-up questions

Compliance now wants Dec 25 and 26 treated as fully out-of-hours. Where does that go, and can it double-count? (One more OR branch on `strftime('%m-%d', call_time)`; a filter keeps each row at most once.)
`call_time` is stored in UTC but the office runs on local time. What changes? (Shift the timestamp before extracting the hour and weekday.)
How would you return the out-of-hours share as a percentage instead of a count? (Count the qualifying rows inside the aggregate and divide by `COUNT(*)`.)