20 role-specific questions
Backend Developer Interview Questions & Answers
Backend interviews test how you design APIs, model and query data, keep systems fast and reliable, and reason about failure. Language matters less than fundamentals: HTTP, SQL, caching, queues and observability.
Free, no sign-up. 93 questions in this role's practice pool, including behavioural, HR and situational questions.
APIs & back-end architecture interview questions
API design, HTTP, caching, queues, microservices, reliability and observability — language-agnostic back-end questions.
1.
Design a REST endpoint for creating an order. Which method, status codes and error responses would you use?
IntermediateTechnicalPractise this question and get feedbackWhat the interviewer is evaluating
Clean, predictable API design.
Key points a strong answer covers
- POST to a collection. POST /orders creates an order.
- 201 + Location. Return 201 Created with the new resource (and a Location header).
- Validation errors. 400/422 with a consistent error body listing field errors.
- Auth errors. 401 if not authenticated, 403 if not allowed.
- Idempotency. An Idempotency-Key header prevents duplicate orders on retries.
Suggested answer structure
- Define it in one sentence.
- Explain how it works underneath.
- Give a practical example from code or a project.
- Close with a trade-off, pitfall or when you'd use it.
Common mistakes
- Returning 200 with
{ success: false }for errors. - Using GET or verbs in URLs like /createOrder.
Likely follow-up questions
- How would you make this safe to retry if the client times out?
- How would you version this API?
Example strong answer
I'd use
POST /orderswith a JSON body of items and a shipping address. On success,201 Createdwith the created order and aLocation: /orders/{id}header. Invalid input returns 422 (or 400) with a consistent error body — I like RFC 9457 problem details listing each field error. Unauthenticated requests get 401, and 403 if the user can't order for that account. A product that's out of stock might be 409 Conflict. Because clients retry on timeouts, I'd accept anIdempotency-Keyheader: the server stores the key with the result, and a retry with the same key returns the original order instead of creating a duplicate.2.
What's the difference between PUT and PATCH, and what does idempotent mean?
BeginnerTechnicalPractise this question and get feedbackWhat the interviewer is evaluating
Precise understanding of HTTP semantics, which decides how safe retries are.
Key points a strong answer covers
- PUT replaces. PUT replaces the resource with the representation sent.
- PATCH partial. PATCH applies a partial update.
- Idempotent meaning. Idempotent: repeating the same request leaves the server in the same state.
- Which are idempotent. GET, PUT and DELETE are idempotent; POST isn't; PATCH may or may not be.
Suggested answer structure
- Define it in one sentence.
- Explain how it works underneath.
- Give a practical example from code or a project.
- Close with a trade-off, pitfall or when you'd use it.
Common mistakes
- Saying POST is idempotent.
- Confusing idempotent with safe (no side effects).
Likely follow-up questions
- Is DELETE idempotent if the second call returns 404?
- Why does idempotency matter for retries?
Example strong answer
PUT replaces the whole resource with what you send — fields you leave out are effectively removed or reset. PATCH sends only the changes, like
{ "status": "shipped" }. Idempotent means making the same request once or ten times leaves the server in the same state. GET, PUT and DELETE are idempotent; POST is not, because each call usually creates something new; PATCH can be either, depending on the operation — 'set status to shipped' is, 'add 1 to quantity' isn't. DELETE stays idempotent even if the second call returns 404, because the state is the same. It matters because networks fail and clients retry, and idempotent requests are safe to retry.3.
Where and how would you add caching to a slow API?
IntermediateTechnicalPractise this question and get feedbackWhat the interviewer is evaluating
Caching strategy and the invalidation problem.
Key points a strong answer covers
- Layers. Cache at the HTTP/CDN layer, in the app (Redis), or in the database.
- Cache-aside. Cache-aside: check the cache, on a miss load and store.
- Expiry & invalidation. Choose TTLs and invalidate on writes.
- What not to cache. Be careful with per-user and strongly consistent data.
- Stampede. Prevent stampedes with locks, jitter or stale-while-revalidate.
Suggested answer structure
- Define it in one sentence.
- Explain how it works underneath.
- Give a practical example from code or a project.
- Close with a trade-off, pitfall or when you'd use it.
Common mistakes
- Adding a cache without an invalidation plan.
- Caching per-user responses publicly.
Likely follow-up questions
- What is a cache stampede and how do you prevent it?
- How do Cache-Control headers interact with a CDN?
Example strong answer
First I'd confirm what's slow and how often the data changes. For public, rarely changing responses, HTTP caching with
Cache-Controllets the CDN and browsers serve them without touching the server. For expensive queries I'd use cache-aside in Redis: check the cache, on a miss load from the database and store the result with a TTL. On writes I invalidate or update the affected keys, so users don't see stale data for long. I'm careful with per-user data — it must never be cached under a shared key. And for very hot keys I'd prevent a stampede when they expire, with a short lock, TTL jitter or serving stale while one request refreshes.4.
When would you use a message queue, and what problems does it introduce?
IntermediateTechnicalPractise this question and get feedbackWhat the interviewer is evaluating
Asynchronous architecture and its trade-offs.
Key points a strong answer covers
- Decouple / async work. Queues move slow or non-urgent work out of the request path and decouple services.
- Absorb spikes. They absorb traffic spikes so workers process at their own pace.
- Delivery guarantees. Most queues are at-least-once, so consumers must be idempotent.
- Failure handling. Retries with backoff and a dead-letter queue for failed messages.
- Examples. RabbitMQ, SQS, Kafka, BullMQ.
Suggested answer structure
- Define it in one sentence.
- Explain how it works underneath.
- Give a practical example from code or a project.
- Close with a trade-off, pitfall or when you'd use it.
Common mistakes
- Assuming each message is delivered exactly once.
- No plan for messages that keep failing.
Likely follow-up questions
- How do you make a consumer idempotent?
- When would you choose Kafka over RabbitMQ?
Example strong answer
I'd use a queue when work doesn't need to finish inside the request — sending emails, generating reports, processing uploads — or to decouple services, so the order service publishes 'order placed' and billing and shipping consume it independently. Queues also absorb spikes: workers drain the backlog at their own pace. The trade-offs: most queues guarantee at-least-once delivery, so a message can arrive twice and consumers must be idempotent, for example by recording processed message ids. Messages that keep failing need retries with backoff and then a dead-letter queue to inspect. You also need monitoring of queue depth and age, and the system becomes eventually consistent.
5.
Monolith or microservices? How would you decide?
AdvancedTechnicalPractise this question and get feedbackWhat the interviewer is evaluating
Architectural judgement rather than following trends.
Key points a strong answer covers
- Monolith benefits. A monolith is simpler to build, deploy, debug and keep consistent.
- Microservices benefits. Microservices allow independent deployment and scaling and suit many autonomous teams.
- Costs of distribution. They add network failures, eventual consistency, and operational and observability overhead.
- Modular monolith / boundaries. Start with a modular monolith with clear boundaries; extract services when there's a real need.
Suggested answer structure
- Define it in one sentence.
- Explain how it works underneath.
- Give a practical example from code or a project.
- Close with a trade-off, pitfall or when you'd use it.
Common mistakes
- Choosing microservices for a small team on a new product.
- Splitting services that share a database.
Likely follow-up questions
- What is a distributed monolith?
- How would you extract the first service from a monolith?
Example strong answer
It depends on the team and the problem more than the technology. A monolith is one codebase and one deployment: simpler to build, test, debug and keep consistent, which is usually right for a new product or a small team. Microservices pay off when several teams need to deploy independently, or parts of the system have very different scaling or reliability needs. But they bring network failures, eventual consistency, distributed tracing and much more operational work. My default is a modular monolith with clear domain boundaries and no cross-module database access, and extracting a service only when there's a concrete reason, like an image-processing workload that needs to scale separately.
6.
How do you make a back-end service observable in production?
IntermediateTechnicalPractise this question and get feedbackWhat the interviewer is evaluating
Operational maturity.
Key points a strong answer covers
- Logs. Structured logs with request/correlation ids.
- Metrics. Metrics for latency, traffic, errors and saturation.
- Tracing. Distributed tracing (OpenTelemetry) across services.
- Alerting / SLOs. Alerts on user-facing symptoms tied to SLOs.
Suggested answer structure
- Define it in one sentence.
- Explain how it works underneath.
- Give a practical example from code or a project.
- Close with a trade-off, pitfall or when you'd use it.
Common mistakes
- Logging unstructured text without request ids.
- Alerting on every CPU spike instead of user impact.
Likely follow-up questions
- What would you alert on for an API?
- How do traces help debug a slow request across services?
Example strong answer
I cover the three pillars. Logs are structured JSON with a request or correlation id on every line, so one request can be followed end to end, and no sensitive data. Metrics track the golden signals — latency percentiles, request rate, error rate and saturation — on dashboards in something like Prometheus and Grafana. Distributed tracing with OpenTelemetry shows where time goes inside a request across services and the database. Alerts fire on user-facing symptoms tied to SLOs — for example p95 latency over 500 ms or error rate over 1% for five minutes — rather than every CPU spike, and each alert links to a runbook.
7.
How would you implement rate limiting for an API?
IntermediateTechnicalPractise this question and get feedbackWhat the interviewer is evaluating
Protecting services from abuse and overload.
Key points a strong answer covers
- Algorithms. Token bucket, fixed or sliding window.
- Key. Limit per API key, user or IP.
- Shared store. Store counters in Redis so limits work across instances.
- Response. Return 429 with Retry-After and rate-limit headers.
- Where. Enforce at the gateway/edge as well as in the app for sensitive endpoints.
Suggested answer structure
- Define it in one sentence.
- Explain how it works underneath.
- Give a practical example from code or a project.
- Close with a trade-off, pitfall or when you'd use it.
Common mistakes
- Keeping counters in each instance's memory behind a load balancer.
- Returning 500 instead of 429.
Likely follow-up questions
- How does a token bucket allow short bursts?
- What extra limits would you put on a login endpoint?
Example strong answer
I'd identify clients by API key or user id, falling back to IP for anonymous traffic, and pick an algorithm — a token bucket is a good default because it allows short bursts while enforcing an average rate; a sliding window is simpler to explain. Counters live in Redis, often with an atomic Lua script, so the limit holds across all instances. Over the limit, the API returns 429 Too Many Requests with a Retry-After header and the usual rate-limit headers so clients can back off. Coarse limits belong at the edge — the API gateway or CDN — and stricter, specific limits go on sensitive endpoints like login and password reset.
8.
How do you evolve an API without breaking existing clients?
IntermediateTechnicalPractise this question and get feedbackWhat the interviewer is evaluating
Backward compatibility discipline.
Key points a strong answer covers
- Additive changes. Prefer additive, backward-compatible changes.
- Versioning. Version breaking changes (URL or header).
- Deprecation. Deprecate with notice, headers and a timeline.
- Contracts / monitoring. Document with OpenAPI, test contracts and monitor who still uses old versions.
Suggested answer structure
- Define it in one sentence.
- Explain how it works underneath.
- Give a practical example from code or a project.
- Close with a trade-off, pitfall or when you'd use it.
Common mistakes
- Renaming fields in place.
- Removing a version without checking usage.
Likely follow-up questions
- Is adding a new enum value a breaking change?
- What are consumer-driven contract tests?
Example strong answer
Most changes can be additive: new optional fields, new endpoints, new optional parameters — clients ignore what they don't know. I avoid renaming or removing fields and changing types or meanings in place. When a breaking change is unavoidable, I introduce a new version —
/v2/in the URL or a version header — and run both side by side. The old version is deprecated with clear notice,DeprecationandSunsetheaders, a migration guide and a timeline, and I monitor usage so I know who still calls it before removing it. An OpenAPI spec and contract tests catch accidental breaking changes in CI. Even adding an enum value can break strict clients, so I document how clients should treat unknown values.9.
How do you keep data consistent when one operation updates several services?
AdvancedTechnicalPractise this question and get feedbackWhat the interviewer is evaluating
Distributed transactions and practical patterns.
Key points a strong answer covers
- No distributed ACID. Two-phase commit is rarely practical across services.
- Saga. A saga runs local transactions with compensating actions on failure.
- Outbox. The transactional outbox writes the event in the same DB transaction as the change.
- Idempotency / eventual consistency. Consumers are idempotent; the system is eventually consistent.
Suggested answer structure
- Define it in one sentence.
- Explain how it works underneath.
- Give a practical example from code or a project.
- Close with a trade-off, pitfall or when you'd use it.
Common mistakes
- Writing to the DB and publishing to a queue as two separate steps.
- Ignoring partial failures.
Likely follow-up questions
- What goes wrong if you save the order and then publish the event separately?
- Choreography or orchestration for sagas?
Example strong answer
Across services you can't rely on one ACID transaction, and two-phase commit is usually too fragile and slow. Instead I use a saga: each service performs a local transaction and emits an event, and if a later step fails, earlier steps are undone with compensating actions — for an order, payment is refunded and stock released. To avoid the classic bug of saving the order but crashing before publishing the event, I use the transactional outbox: the event is written to an outbox table in the same database transaction as the order, and a relay publishes it. Consumers are idempotent because events can arrive twice, and the business accepts eventual consistency, with the UI showing states like 'payment pending'.
10.
How would you design file uploads for large files (for example, videos)?
IntermediateTechnicalPractise this question and get feedbackWhat the interviewer is evaluating
Practical design for heavy payloads.
Key points a strong answer covers
- Direct to object storage. Store files in object storage, not the database or app server.
- Pre-signed URLs. Clients upload directly with pre-signed URLs, bypassing your servers.
- Multipart / resumable. Multipart or resumable uploads for large files.
- Validation & security. Validate size and type, and scan files.
- Processing. Process asynchronously (transcoding, thumbnails) via events.
Suggested answer structure
- Define it in one sentence.
- Explain how it works underneath.
- Give a practical example from code or a project.
- Close with a trade-off, pitfall or when you'd use it.
Common mistakes
- Streaming gigabytes through the API server.
- Trusting the file extension.
Likely follow-up questions
- How does a pre-signed URL stay secure?
- How would you show upload progress and resume after a dropped connection?
Example strong answer
I wouldn't stream large files through the API servers. The client asks the API for permission, the API validates the user, size and type and returns pre-signed URLs, and the browser uploads directly to object storage like S3. For large videos I'd use multipart upload so chunks upload in parallel and a dropped connection only retries one chunk, with progress shown per part. When the upload completes, storage emits an event that triggers background jobs: verify the real file type, virus-scan, then transcode and generate thumbnails. The database stores metadata and the object key, never the file itself, and files are served through a CDN.
SQL & databases interview questions
Indexes, joins, transactions, normalisation, query performance and choosing between SQL and NoSQL.
1.
A query on a large table has become slow. How do you investigate and fix it?
IntermediateTechnicalPractise this question and get feedbackWhat the interviewer is evaluating
A methodical, evidence-based approach to performance.
Key points a strong answer covers
- Execution plan. Start with the execution plan (EXPLAIN ANALYZE / actual plan).
- Indexes. Add or adjust indexes (composite, covering) for filters, joins and sorts.
- Query shape. Select only needed columns, avoid non-sargable filters and N+1, paginate.
- Stats / data growth. Check statistics, bloat and locking.
- Verify. Measure before and after.
Suggested answer structure
- Define it in one sentence.
- Explain how it works underneath.
- Give a practical example from code or a project.
- Close with a trade-off, pitfall or when you'd use it.
Common mistakes
- Adding indexes blindly without reading the plan.
- Not checking whether the query is waiting on locks.
Likely follow-up questions
- What makes a WHERE clause non-sargable?
- Why can OFFSET pagination get slower on later pages?
Example strong answer
First I'd capture the exact query and its parameters, then run
EXPLAIN ANALYZE(or the actual execution plan in SQL Server) to see where the time goes — a sequential scan on a big table, a bad join order, a huge sort, or rows estimated wrongly. Common fixes: an index on the filter and join columns, often composite in the right column order, or covering so the table isn't touched; rewriting non-sargable conditions likeWHERE YEAR(created_at) = 2024into a range; selecting only needed columns; keyset pagination instead of large OFFSETs. I also check stale statistics and whether the query is actually waiting on locks. Then I measure again and watch it in production.2.
What is a database index, and what are the trade-offs?
BeginnerTechnicalPractise this question and get feedbackWhat the interviewer is evaluating
Core understanding of how indexes work.
Key points a strong answer covers
- Structure for fast lookup. An index is a separate sorted structure (usually a B-tree) that lets the database find rows without scanning.
- Speeds reads. It speeds WHERE, JOIN and ORDER BY on the indexed columns.
- Costs writes and space. Every insert/update must also update the index, and it uses space.
- Composite order. Composite index column order matters (leftmost prefix).
Suggested answer structure
- Define it in one sentence.
- Explain how it works underneath.
- Give a practical example from code or a project.
- Close with a trade-off, pitfall or when you'd use it.
Common mistakes
- Indexing low-selectivity columns like a boolean on its own.
- Not knowing composite column order matters.
Likely follow-up questions
- Would an index on (last_name, first_name) help a search by first_name?
- What's a covering index?
Example strong answer
An index is a separate data structure, usually a B-tree, that keeps a column's values sorted with pointers to the rows — like a book's index — so the database can seek straight to matching rows instead of scanning the whole table. It speeds up WHERE filters, joins and ORDER BY on those columns. The trade-offs: every insert, update and delete must also maintain the index, so too many slow down writes, and they take storage. In a composite index column order matters: an index on
(customer_id, created_at)serves queries filtering by customer, or customer and date, but not date alone. I add indexes based on real query patterns and the execution plan.3.
Explain the different types of SQL joins.
BeginnerTechnicalPractise this question and get feedbackWhat the interviewer is evaluating
SQL fundamentals: which rows each join keeps, and the bugs that come from mixing them up.
Key points a strong answer covers
- INNER JOIN. INNER JOIN returns rows with matches in both tables.
- LEFT / RIGHT JOIN. LEFT JOIN keeps all rows from the left table, with NULLs where there's no match.
- FULL OUTER. FULL OUTER JOIN keeps unmatched rows from both sides.
- CROSS / self join. CROSS JOIN gives every combination; a self join joins a table to itself.
- Example. e.g. customers LEFT JOIN orders finds customers with no orders.
Suggested answer structure
- Define it in one sentence.
- Explain how it works underneath.
- Give a practical example from code or a project.
- Close with a trade-off, pitfall or when you'd use it.
Common mistakes
- Filtering the right table in WHERE and accidentally turning a LEFT JOIN into an INNER JOIN.
- No concrete example.
Likely follow-up questions
- How would you find customers who have never placed an order?
- Why might a join produce duplicate rows?
Example strong answer
An INNER JOIN returns only rows that match in both tables — orders with their customers. A LEFT JOIN returns every row from the left table and the matching rows from the right, with NULLs where there's no match;
customers LEFT JOIN orders ... WHERE orders.id IS NULLfinds customers who've never ordered. RIGHT JOIN is the mirror image and rarely needed. FULL OUTER JOIN keeps unmatched rows from both sides, useful for reconciling two data sets. CROSS JOIN produces every combination. A self join joins a table to itself, like employees to their managers. A classic bug is putting a condition on the right table in WHERE, which silently turns a LEFT JOIN into an INNER JOIN — it belongs in the ON clause.4.
What are transactions and ACID? What do isolation levels control?
IntermediateTechnicalPractise this question and get feedbackWhat the interviewer is evaluating
Data integrity understanding.
Key points a strong answer covers
- Atomicity. All the statements succeed or none do.
- Consistency. Constraints keep the data valid.
- Isolation. Concurrent transactions don't see each other's partial work (to a degree).
- Durability. Committed data survives crashes.
- Isolation levels & anomalies. Levels trade correctness for concurrency: dirty reads, non-repeatable reads, phantoms.
Suggested answer structure
- Define it in one sentence.
- Explain how it works underneath.
- Give a practical example from code or a project.
- Close with a trade-off, pitfall or when you'd use it.
Common mistakes
- Not knowing the database's default isolation level.
- Doing read-modify-write without a lock or atomic update.
Likely follow-up questions
- How would you prevent two users booking the last seat?
- What is a lost update?
Example strong answer
A transaction groups statements into one unit, and ACID describes its guarantees. Atomicity: all or nothing — a transfer debits and credits, or neither happens. Consistency: constraints like foreign keys and checks keep data valid. Isolation: concurrent transactions don't interfere with each other's partial work. Durability: once committed, data survives a crash. Isolation levels control how much concurrent transactions can see: read committed (the default in PostgreSQL and SQL Server) prevents dirty reads; repeatable read prevents values changing under you; serializable behaves as if transactions ran one at a time. Higher levels are safer but cause more blocking or retries, so for the 'last seat' problem I'd use a row lock, a conditional atomic update, or a unique constraint.
5.
What is normalisation, and when would you denormalise?
IntermediateTechnicalPractise this question and get feedbackWhat the interviewer is evaluating
Data modelling judgement.
Key points a strong answer covers
- Remove duplication. Normalisation stores each fact once to avoid duplication.
- Normal forms. 1NF atomic values, 2NF no partial dependencies, 3NF no transitive dependencies.
- Anomalies. It prevents update, insert and delete anomalies.
- Denormalise for reads. Denormalise deliberately for read performance or reporting, keeping it in sync.
Suggested answer structure
- Define it in one sentence.
- Explain how it works underneath.
- Give a practical example from code or a project.
- Close with a trade-off, pitfall or when you'd use it.
Common mistakes
- Over-normalising so simple screens need ten joins.
- Denormalising without a plan to keep copies in sync.
Likely follow-up questions
- Why store the unit price on an order line instead of looking it up?
- What's a materialised view?
Example strong answer
Normalisation organises tables so each fact is stored once. In practice: first normal form means atomic values and no repeating groups; second, every column depends on the whole key; third, no column depends on another non-key column — the customer's city belongs in the customers table, not repeated on every order. That prevents anomalies where you update the address in one place and not another. I denormalise deliberately when reads dominate and joins are measurably too slow — a counter of comments on a post, or a reporting table — and keep it in sync with triggers, application logic or a materialised view. Some 'duplication' is actually correct: an order line stores the unit price because prices change later.
6.
When would you choose a NoSQL database over a relational one?
IntermediateTechnicalPractise this question and get feedbackWhat the interviewer is evaluating
Choosing storage based on access patterns, not hype.
Key points a strong answer covers
- Relational strengths. Relational databases excel at structured data, joins, constraints and transactions.
- NoSQL types. NoSQL covers document, key-value, wide-column and graph stores.
- Access patterns / scale. NoSQL fits known access patterns at huge scale or flexible schemas.
- Trade-offs. You trade query flexibility and often consistency.
Suggested answer structure
- Define it in one sentence.
- Explain how it works underneath.
- Give a practical example from code or a project.
- Close with a trade-off, pitfall or when you'd use it.
Common mistakes
- Choosing MongoDB because “it scales” without access-pattern analysis.
- Saying NoSQL means no schema at all.
Likely follow-up questions
- How would you model a one-to-many relationship in a document database?
- What's single-table design in DynamoDB?
Example strong answer
My default for business data is a relational database like PostgreSQL: structured data, joins, constraints, transactions, and the flexibility to answer questions I didn't plan for — and it handles JSON columns when part of the data is flexible. I'd pick NoSQL for specific needs: a key-value store like Redis for caching and sessions; DynamoDB or Cassandra for enormous scale with well-known access patterns; a document database when records are naturally self-contained and vary in shape, like product catalogues; a graph database for relationship-heavy queries. The trade-offs are less ad-hoc query flexibility, denormalised data you must keep in sync, and often weaker consistency guarantees.
7.
What is the N+1 query problem, and how do you fix it?
IntermediateTechnicalPractise this question and get feedbackWhat the interviewer is evaluating
ORM awareness and performance debugging.
Key points a strong answer covers
- Definition. One query loads a list, then one more query runs for each item.
- Typical cause: lazy loading. It usually comes from ORM lazy loading inside a loop.
- Fix: eager load / join. Eager-load or join, or batch with WHERE id IN (...).
- Detect. Detect by logging query counts per request.
Suggested answer structure
- Define it in one sentence.
- Explain how it works underneath.
- Give a practical example from code or a project.
- Close with a trade-off, pitfall or when you'd use it.
Common mistakes
- Fixing it by caching instead of fixing the query.
- Eager-loading everything everywhere.
Likely follow-up questions
- How would you detect N+1 queries automatically in tests?
- How does DataLoader solve it in GraphQL?
Example strong answer
The N+1 problem is when code runs one query to fetch a list — say 50 blog posts — and then one extra query per item to fetch something related, like each post's author: 51 queries instead of one or two. It usually comes from an ORM lazily loading a relationship inside a loop or a template. The fix is to load the related data up front: eager loading (
includein Prisma,Includein EF Core,select_relatedin Django), a join, or a batchedWHERE author_id IN (...)query. I spot it by logging the number of queries per request in development or with APM tools, and some teams add a test that asserts the query count for key endpoints.8.
How would you model orders and order items in a relational database?
IntermediateTechnicalPractise this question and get feedbackWhat the interviewer is evaluating
Practical schema design.
Key points a strong answer covers
- Keys. Each table has a primary key; order items reference the order with a foreign key.
- One-to-many. One order has many items.
- Price snapshot. Store the unit price on the order item, because product prices change.
- Constraints / money. Use constraints, and decimal or integer cents for money — never floats.
- Status & indexes. Track status, and index by customer and date.
Suggested answer structure
- Define it in one sentence.
- Explain how it works underneath.
- Give a practical example from code or a project.
- Close with a trade-off, pitfall or when you'd use it.
Common mistakes
- Storing money as floating point.
- Looking up the current product price for historic orders.
Likely follow-up questions
- How would you handle an order's status history?
- How would you store an address that might change later?
Example strong answer
I'd have
customers,products,ordersandorder_items.ordershas an id,customer_idas a foreign key, status, currency, totals and timestamps.order_itemshas its own id,order_idandproduct_idforeign keys, quantity, and the unit price at the time of purchase — copied, not looked up, because product prices change and old orders must stay correct. Money is a DECIMAL or integer minor units, never a float. Constraints enforce that quantity is positive and the foreign keys exist. The shipping address is copied onto the order for the same reason as price. I'd indexorders(customer_id, created_at)for order history, and keep status changes in a separate history table if they need auditing.9.
What are window functions in SQL? Give an example.
AdvancedTechnicalPractise this question and get feedbackWhat the interviewer is evaluating
Analytical SQL skills.
Key points a strong answer covers
- Calculate across related rows. Window functions compute across a set of related rows using OVER(...).
- Rows are kept. Unlike GROUP BY, they don't collapse rows.
- PARTITION BY / ORDER BY. PARTITION BY groups rows; ORDER BY defines the order inside each partition.
- Functions. ROW_NUMBER, RANK, LAG/LEAD, running totals.
Suggested answer structure
- Define it in one sentence.
- Explain how it works underneath.
- Give a practical example from code or a project.
- Close with a trade-off, pitfall or when you'd use it.
Common mistakes
- Thinking window functions reduce the number of rows like GROUP BY.
- Using them in a WHERE clause directly.
Likely follow-up questions
- How would you get each customer's most recent order?
- What's the difference between RANK and DENSE_RANK?
Example strong answer
A window function calculates a value across a set of rows related to the current row, defined with
OVER (...), but unlike GROUP BY it keeps every row.PARTITION BYsplits rows into groups andORDER BYorders them within each group. For example, each customer's latest order:ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn, then filterrn = 1in an outer query, since you can't filter on a window function in the same WHERE. Other uses: running totals withSUM(amount) OVER (ORDER BY day), month-over-month change withLAG, and rankings withRANKorDENSE_RANK, which differ in whether ties leave gaps.10.
How do you run a database schema change on a large production table without downtime?
AdvancedTechnicalPractise this question and get feedbackWhat the interviewer is evaluating
Operational safety with data.
Key points a strong answer covers
- Expand and contract. Expand–contract: add new structure, migrate, then remove the old in a later release.
- Locking awareness. Some operations lock tables; use online/concurrent options.
- Backfill in batches. Backfill data in small batches.
- Deploy order / rollback. Keep code compatible with old and new schema; plan rollback.
- Test. Rehearse on a production-sized copy.
Suggested answer structure
- Define it in one sentence.
- Explain how it works underneath.
- Give a practical example from code or a project.
- Close with a trade-off, pitfall or when you'd use it.
Common mistakes
- Renaming a column in one step while old code still reads it.
- Adding an index without CONCURRENTLY on a busy Postgres table.
Likely follow-up questions
- How would you rename a column safely?
- Why can adding a NOT NULL column with a default be risky on some databases?
Example strong answer
I use an expand-and-contract approach. To rename a column, say: first add the new column, deploy code that writes to both and reads the old, backfill the new column in small batches so I don't hold long locks or swamp replication, switch reads to the new column, and only in a later release drop the old one. Each step is backward compatible, so I can roll back the code at any point. I check which operations take heavy locks on our database — in PostgreSQL I create indexes
CONCURRENTLY— and use online schema-change tools where needed. I rehearse on a production-sized copy to know how long it takes, and run it during low traffic with monitoring.
Ready to answer them out loud?
Run a timed backend developer mock interview, type or speak your answers, and get a score breakdown with what to improve.