Interview prep · questions

APIs and data interview questions

10 questions, from core to senior. Say your answer out loud first, then open the model answer and check it against the points a strong answer covers.

Practice them as cards

How do you design a REST API for a resource such as orders?

Core

Show the model answer

I model resources as nouns in the URL and use the HTTP method for the action. So GET /orders lists, POST /orders creates, GET /orders/42 reads one, PATCH /orders/42 changes it, and DELETE /orders/42 removes it. There are no verbs in the path. The status code carries the result: 201 with a Location header after a create, 204 after a delete, 400 or 422 for bad input, 404 when the resource does not exist. A list endpoint is always paginated, with a limit and a cursor, and it takes filters and sorting as query parameters. All errors have one shape, for example a code, a message and details for each field, so the client handles them in one place. I version the API, usually with v1 in the path, and inside a version I only add fields; I never remove or rename them.

A strong answer covers

Uses nouns for resources and HTTP methods for actions
Returns correct status codes, including 201 after a create
Paginates every list and can compare cursor with offset
Has one error shape for the whole API
Has a versioning plan and avoids breaking changes

Likely follow-up

Why is cursor pagination better than offset pagination for a large list that changes often?

What does idempotent mean, and why does it matter for a payment endpoint?

Core

Show the model answer

An operation is idempotent when doing it many times has the same effect as doing it once. It matters because networks fail. The client sends a request, the server does the work, and the response is lost. The client cannot know whether it worked, so it retries. If the operation is not idempotent, the user pays twice. GET, PUT and DELETE are idempotent. PUT means: set this resource to this state, so a repeat changes nothing. POST is not: two POST requests create two payments. To make a POST safe to retry, the client creates a unique idempotency key, for example a UUID, and sends it in a header. The server stores the key with the result. When the same key arrives again, it returns the stored result and does not repeat the work. In the frontend I create the key once per user action, so every retry uses it.

A strong answer covers

Defines it: many identical calls have the same effect as one
Explains the lost response that forces a retry
Describes an idempotency key that the server stores with the result
Says PUT and DELETE are idempotent and POST is not

Likely follow-up

Where does the server keep idempotency keys, and for how long?

What is the difference between authentication and authorization, and where do you enforce each?

Core

Show the model answer

Authentication answers who you are, and authorization answers what you are allowed to do. Authentication happens at login, with a password, a single sign-on provider or a passkey, and the result is a session or a token. With a session, the server stores the state and the browser holds an ID in a cookie, so it is easy to revoke. With a token like a JWT, the server only verifies a signature. That works well across many services, but revoking is harder. Authorization is checked on the server on every request: first the role or permission, then ownership, for example that this order belongs to this user. In NestJS this is the job of guards. The frontend can hide a button, but that is only for a better experience. Anyone can call the API directly, so the server never trusts a role or user ID that the client sends.

A strong answer covers

Defines both: identity against permission
Connects them to 401 (not authenticated) and 403 (not allowed)
Says the server checks every request, including who owns the record
Says hiding a button is for experience, not for security

Likely follow-up

A user changes the ID in the URL and sees another user's order. What is wrong, and how do you fix it?

What is the difference between INNER JOIN and LEFT JOIN, and why does this query use LEFT JOIN?

Core

SELECT u.id, u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name
ORDER BY order_count DESC;

Show the model answer

An INNER JOIN returns only the rows that have a match in both tables. A LEFT JOIN returns every row from the left table, and puts NULL in the columns of the right table when there is no match. This query counts the orders of each user, and it must also show users with no orders, so it uses a LEFT JOIN from users to orders. GROUP BY makes one group for each user, and COUNT counts the rows in each group. It counts o.id and not the star, because COUNT of a column skips NULL. So a user with no orders gets 0. With COUNT star that user would get 1, and with an INNER JOIN the user would not be in the result at all. One common mistake: a condition on the orders table in WHERE removes the rows with NULL. That condition belongs in the ON clause.

A strong answer covers

Says INNER JOIN keeps only matching rows and LEFT JOIN keeps all rows of the left table
Says LEFT JOIN is used so that users with no orders still appear
Explains COUNT(o.id): it skips NULL, so those users get 0
Knows that a WHERE condition on the right table removes the unmatched rows

Likely follow-up

How do you change the query to show only users with more than five orders?

What is a database index, and why not put one on every column?

Core

Show the model answer

An index is a separate sorted structure, usually a B-tree, that lets the database find rows without reading the whole table. A B-tree lookup takes a few steps even in a very large table, and a full scan reads every row. The cost is on writes: each write must keep every index up to date, and indexes take space. So I add them for the columns in WHERE, JOIN and ORDER BY of frequent queries. In a composite index the column order matters. An index on user_id and then created_at helps a query that filters by user_id, but it does not help much for a query that filters only by created_at. The database can ignore an index. That happens when the column is inside a function, when a LIKE pattern starts with a wildcard, or when the query returns a large part of the table. I check with EXPLAIN ANALYZE.

A strong answer covers

Describes an index as a sorted structure (a B-tree) that avoids a full table scan
Says reads get faster, writes get slower, and indexes use space
Explains column order in a composite index: queries should filter on the leading column
Gives a case where the index is not used: a function on the column or a leading wildcard
Checks the plan with EXPLAIN

Likely follow-up

What is a covering index, and when does it help?

What does a database transaction guarantee, and how do you stop the double booking in this example?

Core

-- Two requests run this at the same time, for two different users
SELECT status FROM seats WHERE id = 17;
-- Both read 'free', so both continue
UPDATE seats SET status = 'booked', user_id = :user WHERE id = 17;

Show the model answer

A transaction makes a group of statements one unit: all of them succeed, or none of them do. ACID names the guarantees. Atomic means all or nothing. Consistent means the rules of the data, such as constraints, still hold at the end. Isolated means transactions that run at the same time do not see each other's unfinished work. Durable means a committed change survives a crash. Here both requests read that the seat is free before either one writes, and at the default isolation level a transaction alone does not stop that. With pessimistic locking, SELECT FOR UPDATE locks the row, so the second request waits and then sees that the seat is taken. With optimistic locking, the UPDATE also checks a version number or the old status, and if it changes no row, someone else was faster. Pessimistic fits frequent conflicts, optimistic fits rare ones.

A strong answer covers

Explains ACID in plain words, not only the four letters
Describes the race: two requests read free, then both write
Gives pessimistic locking (SELECT FOR UPDATE) and optimistic locking (a version check in the UPDATE)
Says a transaction at the default isolation level does not stop this race alone
Says when each fits: pessimistic for frequent conflicts, optimistic for rare ones

Likely follow-up

What are the isolation levels, and which one does PostgreSQL use by default?

The query log for one page load looks like this. What is the problem, and how do you fix it?

Senior

SELECT * FROM posts ORDER BY created_at DESC LIMIT 100;
SELECT * FROM users WHERE id = 7;
SELECT * FROM users WHERE id = 12;
SELECT * FROM users WHERE id = 7;
SELECT * FROM users WHERE id = 31;
-- ... and 96 more queries like these

Show the model answer

This is the N+1 query problem: one query for a list, and then one more query for each item in it, so 100 posts cost 101 queries. An ORM causes it when a relation is loaded lazily or inside a loop: the code fetches the posts, and then for each post it asks for the author. Each query is fast, but together they are slow, mostly because of the round trips. The fix is to load the authors for the whole list together, with a JOIN or with one more query that uses WHERE id IN. In an ORM this is eager loading, with an option such as include or relations. I find the problem in the query log, where the same query repeats with different IDs. EXPLAIN on one query does not show it. I use EXPLAIN afterwards, to check that the new query uses an index.

A strong answer covers

Names the N+1 problem: one query for the list plus one for each item
Explains how lazy loading or a loop in an ORM causes it
Gives the fixes: a JOIN or eager loading, or batching with WHERE IN
Says how to spot it: the query log shows the same query many times

Likely follow-up

When is one big JOIN worse than two separate queries?

Where can you cache data in a web application, and how do you keep the cache correct?

Senior

Show the model answer

There are four main layers between the user and the data: the browser, the CDN, an application cache such as Redis, and the database itself. The browser and the CDN cache HTTP responses, and Cache-Control headers control them. The CDN is shared between users, so it is for public content. In the backend the usual pattern is cache-aside. The code reads from Redis first. On a miss it reads the database, writes the result to Redis with a time to live, and returns it. The hard part is invalidation. With only a TTL, the data can be out of date for that time. So on a write I also delete the key. I do not put private data in a shared cache, and I do not cache values that must be exact, such as an account balance. And I cache only what I have measured to be slow.

A strong answer covers

Names the layers: browser, CDN, application cache, database
Describes cache-aside: read the cache, on a miss read the database and fill the cache
Explains invalidation: a TTL, plus deleting the key on a write
Says what not to cache: private data in a shared cache, values that must be exact

Likely follow-up

A popular key expires and a thousand requests reach the database at once. How do you prevent that?

An API answers your client with this response. Why do APIs do this, and how should the client react?

Senior

HTTP/1.1 429 Too Many Requests
Retry-After: 30

Show the model answer

This is a rate limit. It protects the service from overload and abuse, keeps usage fair between clients, and slows down attacks such as password guessing. When a client goes over the limit, the server answers 429 Too Many Requests, often with a Retry-After header that says how long to wait. A common algorithm is the token bucket. Each client has a bucket that fills with tokens at a fixed rate, up to a maximum. Each request takes one token, and when the bucket is empty, the request is refused. This allows short bursts but limits the average rate. A good client respects Retry-After. Without that header, it uses exponential backoff: it waits 1 second, then 2, then 4, up to a maximum. It adds random jitter, so that many clients do not retry at the same moment, and it stops after a few attempts.

A strong answer covers

Gives the reasons: protect the service, fairness, slow down abuse
Names the 429 status and the Retry-After header
Explains the token bucket: tokens refill at a fixed rate, bursts up to the bucket size
Describes the client: exponential backoff with jitter and a limit on retries

Likely follow-up

Which requests are safe to retry automatically, and which are not?

How do you process webhooks or queue messages reliably when the same one can arrive twice?

Senior

Show the model answer

I assume at-least-once delivery, so I make the consumer idempotent. The sender repeats a message until it gets a success response, because after a timeout it cannot know whether my server did the work. So duplicates are normal, and order is not guaranteed. Each event has a unique ID. I store the IDs I have processed, with a unique constraint, in the same transaction as the business change, and I skip an ID I have seen. For a webhook I first verify the signature. The header holds an HMAC of the raw body, made with a shared secret. I compute the same HMAC and compare them. Then I save the event, answer 200 quickly, and do the slow work in a background job. On the sending side, the outbox pattern writes the event to a table in the same transaction as the data, and a separate worker publishes it.

A strong answer covers

Says at-least-once delivery means duplicates will arrive
Makes the consumer idempotent: stores processed event IDs and skips repeats
Verifies the webhook signature (an HMAC of the raw body) before trusting it
Answers quickly and does slow work in a background job
Explains the outbox pattern in one sentence

Likely follow-up

Why do most systems promise at-least-once delivery and not exactly-once delivery?