# After Hours API Calls

> The office is dark. The API is not.

Canonical URL: <https://datadriven.io/problems/after_hours_api_calls>

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

## Problem

The compliance team is auditing API activity outside business hours for December 2026. Business hours are Monday through Friday, 09:00 to 16:00. All weekend calls and calls on December 25th and 26th count as out-of-hours regardless of time. Return the total count of out-of-hours calls as a decimal value.

## Tables

### api_calls

- call_id INTEGER
- endpoint TEXT
- method TEXT
- status INTEGER
- latency REAL
- user_id INTEGER
- call_time TEXT
- err_msg TEXT

Sample rows (10 of 670):

| 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 |
| --- |
| 0 |

## Worked solution and explanation

### Why this problem exists in real interviews

Interviewers use the `api_calls` table here to probe grouped aggregation. The columns `endpoint`, `method`, `status` force candidates to reason about the correct grain before writing any aggregation.

---

### Break down the requirements

#### Step 1: Group by `STRFTIME('%Y'`

Restrict `api_calls` to December of the target year via strftime('%Y-%m', `call_time`) = '2026-12'.

#### Step 2: Compute `SUM(status)`

Flag a call out-of-hours if it falls on a weekend (%w in 0,6), before 09:00, at/after 16:00, or on Dec 25 / Dec 26.

#### Step 3: Order by the metric

Count the matching rows and CAST the count to REAL so the result is returned as a decimal.

---

### The solution

**Group-aggregate for after hours api calls**

```sql
SELECT CAST(COUNT(*) AS REAL) AS after_hours_count
FROM api_calls
WHERE strftime('%Y-%m', call_time) = '2026-12'
  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
    OR strftime('%m-%d', call_time) IN ('12-25', '12-26')
  )
```

> **Cost Analysis**
>
> The main table has 300M rows (77 GB). Partitioned on `call_time`, so queries filtering on that column skip most partitions. The GROUP BY reduces the row count early, keeping downstream operations cheap.

> **Interviewers Watch For**
>
> Strong candidates state the correct `GROUP BY` grain before writing any SQL, showing they think about the output shape first.

> **Common Pitfall**
>
> Selecting a non-aggregated column without including it in `GROUP BY` is the most common error. Some engines reject it; others silently return arbitrary values.

---

## Common follow-up questions

- The `err_msg` column in `api_calls` has roughly 96% NULLs. How does your query handle those rows, and would the result change if NULLs were replaced with zeros? _(Tests whether the candidate understands how NULLs propagate through aggregation functions and whether their WHERE/JOIN conditions implicitly filter them out.)_
- Your GROUP BY aggregates `call_id` from `api_calls`. If two groups have the same aggregate value, how is the output ordered, and is that deterministic? _(Tests awareness that ORDER BY on a non-unique value produces non-deterministic row order without a tiebreaker.)_
- `call_id` in `api_calls` has ~300M distinct values. What index strategy keeps your query from doing a full table scan? _(Tests whether the candidate can design indexes for high-cardinality columns and understands selectivity.)_
- If the business definition of `method` changed mid-quarter (e.g., a status value was renamed), how would you handle historical consistency? _(Tests awareness of slowly changing dimensions and backward-compatible query design.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/after_hours_api_calls)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.