Interview guides · Futures Consulting

Futures Consulting Software Engineer Interview Questions & Guide 2026

Testing practice for Futures Consulting software engineer candidates: the boundaries worth covering, and why flakiness is a real defect.

The 60-second brief

At Futures Consulting, the Software Engineer role centers on bridging complex technical requirements with real-world infrastructure solutions. You will be responsible for designing, developing, and implementing technical systems that support the firm's engineering disciplines, including mechanical and civil design projects.

If the loop includes an asynchronous take-home, treat it as a code review of you rather than as a puzzle: structure, what you chose to test, and what you wrote down about the constraint you were working under. Hold the stated time box and say what you would have done with more of it.

Focus areas

Diagnose failures inside environments you cannot instrument
Make integration retries idempotent without target-side idempotency keys
Evolve schemas expand-contract across un-upgradable client deployments

Overview

This position is critical because you act as the connective tissue between abstract design concepts and functional, scalable execution. Whether you are optimizing HVAC systems or refining land development modeling software, your work directly influences the efficiency and precision of Futures Consulting's deliverables. You will operate in a high-stakes environment where technical accuracy and innovative problem-solving are the benchmarks of success.

The Futures Consulting interview process

Futures Consulting candidates report 2 rounds · ≈ 2-4 weeks. The stages below are what candidates describe, not a published process.

Initial Screening

The person on this call usually cannot evaluate your code and does not need to. They write a short paragraph, and that paragraph is what a hiring manager skims when deciding who to put on your loop. So the test is not whether your work was hard, it is whether a non-engineer can repeat it correctly. Name systems by what they did rather than by their internal codename, give each project a shape (what was breaking, what you changed, what happened after), and keep the whole walkthrough near ninety seconds. Depth that cannot survive a paraphrase reads as vagueness.

What to demonstrate

Whether a non-engineer can restate your projects without distorting them, since their paraphrase is what travels to the hiring manager, not your sentences
Whether each project has a shape rather than a stack list: the failure or constraint, the change you made, the result and how it was measured
Whether you can say what was yours inside a team project without either inflating it or disappearing into the plural

How to prepare

Rewrite each headline project as two sentences with no internal system names and no acronyms outside your company, then say them to someone outside engineering and have them repeat them back. Fix whatever came back wrong
Attach one measured number to each project: the baseline, the change, and the window it was measured over. Where nothing was ever measured, say that plainly rather than reaching for a plausible percentage
Time the background walkthrough against a clock. If it runs past two minutes, compress the earliest role to a single clause and spend the recovered time on the most recent one

Targeted Interviews

An unlabelled round is first an information problem, and the cheapest information is free. Whoever schedules it can usually tell you how long it runs, who will be in the room and what they work on, whether you will be writing code and in what environment, and whether anything is being sent beforehand. Ask in writing so the answer is on record, then prepare for the two or three formats those answers still leave open instead of betting on one. What separates a strong candidate is not guessing right; it is having an opening that works whichever one it turns out to be.

What to demonstrate

Whether you can start work from an ambiguous brief, since tolerating a vague scope without stalling is the same thing the job asks for
Whether the questions you asked beforehand were ones that change your preparation, such as duration, medium and who is joining, rather than ones whose answers you could not have acted on
Whether you adapt when the round turns out to be something other than what you were told, instead of spending the first ten minutes visibly recalibrating

How to prepare

Send one short scheduling message asking four things: how long, who is joining and what they work on, whether you will be writing code and where, and whether to prepare anything in advance. Treat a vague reply as real information, since it means the round is loosely structured and you will be shaping it yourself.
Write one opening that works in any of the formats still open: restate in your own words what you have been asked to do, then ask which of two directions is more useful to them. Say it aloud until it stops sounding recited.
Set up for the two most likely formats before the call starts, with a blank editor in the language you would choose and a shared document you can type into, so a format surprise costs you nothing in the first minutes

Where candidates lose points

Tenant context leaking across a connection pool

Setting the tenant on a connection and relying on it for the rest of the request looks correct in every test that runs one request at a time. Under transaction pooling the connection goes back to the pool with that session state still attached, and the next checkout, serving a different client, inherits it; row-level security then enforces the previous tenant's policy flawlessly and returns the wrong rows. It reproduces only under concurrency, which is the load profile your integration tests do not have. Use a transaction-scoped setting, and assert that the setting matches the request's tenant immediately before the first query rather than trusting that it was set correctly upstream.

Forking the product per client instead of building a configuration or extension point

A fork is the cheapest possible answer for one client and the most expensive possible answer for the estate. Maintenance cost grows as O(clients x changes): a single security patch now needs one port per fork, each with its own conflicts, its own test run and its own release window, and the forks drift further apart with every port. The honest rule is that a required fork is a product gap, so it should either become a supported extension point with a stable interface, or be an explicit, time-boxed and separately priced artefact that is never expected to receive upstream fixes.

Finishing a solution without stating its complexity

Give time and space in the same breath as the code, and define n explicitly when there are two sizes, since n nodes and m edges are not interchangeable. Space is the half that gets skipped: count the auxiliary structures you allocate and the recursion stack at its deepest, not only the answer you hand back.

Sorting when the problem never required a total order

Match the algorithm to the guarantee actually needed: the top k comes from a size-k heap in O(n log k) time and O(k) space, distinctness needs a set rather than an ordering, and a small bounded integer key range admits a linear counting pass. A full O(n log n) sort is the right default only when you genuinely need everything in order.

Questions & practice

Find peak window utilisation and the first rejected request

Medium · sliding window · two pointers · rate limiting

One connector binding's requests against a client endpoint arrive as timestamps t[0..n-1] in milliseconds, sorted non-decreasing, n up to 1,000,000. The endpoint's budget is R requests per rolling window of W milliseconds, and a request at time t is admitted only if strictly fewer than R already-admitted requests fall in the half-open window (t-W, t]. Return (a) the maximum number of requests falling in any W-millisecond window, counting every request regardless of admission, and (b) the index of the first request the limiter would reject. Both parts in O(n) time.

Approach

Part (a) is a two-pointer scan: for each right index, advance left while t[right] - t[left] >= W, which leaves the window exactly (t[right]-W, t[right]], and track max(right-left+1). Left is monotone non-decreasing, so total work is O(n) amortised with O(1) extra space. The maximum over all windows of width W is attained at a window whose right edge is a request timestamp, so scanning right edges is sufficient.
Part (b) is not the same scan. A rejected request consumes no budget, so the window must count admitted requests only. Keep a FIFO deque of admitted timestamps: pop from the front while front <= t - W, admit if the deque holds fewer than R, and return the first index where it does not. O(n) total pushes and pops, O(R) memory.
Pin the boundary convention before writing either loop. With a half-open (t-W, t] window a request exactly W milliseconds after an earlier one does not see it; switching to a closed window changes the answer at every boundary and is where most disagreements with the client's own limiter come from.
Note what fixed buckets of width W buy and cost: O(1) memory, but they admit up to 2R inside a span shorter than W, R at the end of one bucket and R at the start of the next. If buckets are unavoidable, halve the bucket width or use a weighted estimate across two adjacent buckets.
If part (a) already exceeds R, the binding cannot run at this concurrency under any limiter shape. That is a scheduling problem, not a retry-policy problem, and no backoff tuning fixes it.

Worked solution (25 min)

Take W = 10,000 ms, R = 3, and timestamps in milliseconds: [0, 1000, 2000, 3000, 11000, 11500, 12000, 12500].
Part (a): at right=3 (t=3000) the window (-7000, 3000] holds 4 requests. At right=7 (t=12500) advance left past 0, 1000 and 2000 to index 3, since 12500-3000 = 9500 < 10000; the window holds indices 3..7, which is 5 requests.
Confirm no other right edge beats 5 by inspecting each, then record the peak.
Part (b): admit index 0, 1 and 2 (deque grows to 3 entries, none expiring since t-W is negative). At index 3, t=3000: nothing to pop, deque size is 3, which is not strictly fewer than R=3, so reject.
Check what happens after the rejection if rejected timestamps were wrongly enqueued: indices 4 onwards would also be refused despite the deque having expired entries by t=11000.

Expected result: (a) peak = 5 requests in the window (2500, 12500]. (b) first rejection at index 3, t = 3000 ms. Indices 0, 1 and 2 are admitted; the requests at 11000 onwards are admissible again because the first three have left the rolling window.

Verify it yourself

The left pointer in part (a) never decreases across the whole scan.
Brute force over all 8 right edges reproduces peak = 5.
Peak (5) > R (3) confirms the budget is exceeded at this arrival rate, independent of the limiter's shape.
Every admitted timestamp remaining in the deque satisfies front > t - W at the moment of the check.

Follow-up

Three bindings and four workers share this one endpoint's quota. Where does the counter live, and what is the correct behaviour when that store is unreachable?
Add full-jitter backoff to the retry path and state precisely what it prevents that plain exponential backoff does not.
The client documents the limit as 'about 100 per minute'. How would you measure the real one without tripping it in their production?

Compute the minimal re-run set that preserves idempotency keys

Hard · interval complement · idempotency keys · watermarks

One connector binding must cover [span_start, span_end). You have all its connector_run rows: (idempotency_key, attempt_no, status, watermark_from, watermark_to), with half-open ranges, where idempotency_key is derived deterministically from the connector and the range, so re-running a range reproduces its key. Only status='succeeded' counts as covered. Produce the minimal re-run plan: every instant in the span ends up covered, any sub-interval some prior run already claimed is re-run at exactly that claim's range, and genuinely unclaimed sub-intervals become new claims cut at the binding's chunk size. Explain what merging ranges would cost.

Approach

Merge the succeeded ranges into a maximal disjoint union: sort by start, sweep extending the open segment while the next start is not beyond the current end. O(n log n) time, O(n) space, and this is the only status that contributes coverage.
Take the complement of that union inside [span_start, span_end) to get the uncovered gaps, in O(u) over the merged segments. Clip at both ends so the plan never reaches outside the span.
Sweep the non-succeeded claims (ambiguous, failed, abandoned) against the gap list with a two-pointer over both sorted lists. Any claim intersecting a gap is emitted at its original watermark_from and watermark_to, byte for byte, so the derived idempotency_key reproduces and the retry rejoins the same ledger row. O(n) after sorting.
Subtract the emitted claim ranges from the gaps; whatever survives was never claimed by any attempt and becomes fresh ranges cut at the chunk size. Only these get new keys. O(u + c).
Accept that a re-emitted claim may overlap time that is already succeeded. Range bookkeeping is not what makes the retry safe; the record-level dedupe key is, and re-reading a covered window costs one extra fetch against the client's quota, which is far cheaper than a key you can no longer reproduce.
Exclude anything still in status 'running' with a live lease. Re-claiming it races the in-flight attempt, doubles the quota spend against the client endpoint, and produces two ledger writers for one key. Total plan cost is O(n log n) time and O(n) space.

Follow-up

An ambiguous run may have applied every record before the timeout. What do you read to settle it before re-running, and what is left if the client's schema offers no natural key?
Retention pruned the ledger rows for one of these keys. What does the re-run do now, and what bounds how long the ledger must be kept?
Two planners run concurrently against the same binding. What stops them emitting and executing the same plan twice?

Compare release versions and report the live estate spread

Easy · parsing · version skew · ordering

Each environment row carries reported_release as TEXT: either NULL, or a version of the form MAJOR.MINOR.PATCH with an optional -rc.N suffix. Up to 400 environments. Ordering rule: compare major, then minor, then patch numerically; a -rc.N build precedes the same MAJOR.MINOR.PATCH with no suffix, and rc numbers compare numerically. Return the lowest and highest reported versions, the minor-version spread between them, and the count of environments whose reported_release is NULL. NULL means the environment's version is unknown, not that it converged.

Approach

Parse each string once into a comparable tuple: (major, minor, patch, is_final, rc), where is_final is 1 for a plain release and 0 for a pre-release, and rc is 0 when absent. Tuple comparison then yields the stated order for free, including 3.10.0-rc.1 < 3.10.0. O(L) per string, O(n*L) total.
Track min and max in one linear scan rather than sorting: O(n) time, O(1) extra space, and n is only a few hundred so the parse dominates either way.
Fail loudly on a string that does not match the grammar instead of defaulting it to 0.0.0 or to the newest release. An unparseable reported_release is a reconciliation-ingest bug, and either default hides it in the direction that looks safe.
Compute the minor spread only when min and max share a major version. Across majors the difference of minor numbers is meaningless, so return the pair of versions and flag the condition rather than a number.
Report the NULL count as its own output line. Folding unknowns into 'converged' is how an install ends up running a release nobody knew was still live.

Follow-up

Two majors are live at once. What does 'spread' mean now, and what single number, if any, would you still publish?
reported_release is a claim from the environment, and last_heartbeat_at may be days old for a client-managed install. At what staleness does this figure stop being usable?
How would you enforce the supported window when the rollout plan is built, instead of reporting a violation after it already exists?

Return the latest attempt per connector idempotency key

Easy · distinct-on · indexing · idempotency

connector_run stores one row per attempt: run_id, connector_id, idempotency_key, attempt_no SMALLINT, status (queued, running, succeeded, failed, ambiguous, cancelled, abandoned), failure_class, watermark_from, watermark_to, started_at, finished_at, unique on (connector_id, idempotency_key, attempt_no). For one connector_id, write the query returning the highest-numbered attempt per idempotency_key, keeping only those keys whose latest attempt is not succeeded, ordered by watermark_from. Give the index that serves it, and say what the plan does without that index.

Approach

Use DISTINCT ON (idempotency_key) with ORDER BY idempotency_key, attempt_no DESC. PostgreSQL requires the ORDER BY to lead with exactly the DISTINCT ON expressions, and that requirement is what makes the row picked deterministic rather than whichever the scan happened to reach first.
Apply the not-succeeded filter outside, in a wrapping SELECT. Putting it in the inner WHERE changes the question from 'keys whose latest attempt failed' to 'keys that have any non-succeeded attempt', which reports a key that failed twice and then succeeded, and is the single most common wrong answer here.
Know the portable alternative: row_number() OVER (PARTITION BY idempotency_key ORDER BY attempt_no DESC) in a subquery, filtered = 1 outside. A window function cannot be filtered in WHERE because WHERE is evaluated before the window, and PostgreSQL has no QUALIFY clause, so the subquery is mandatory rather than stylistic.
Index (connector_id, idempotency_key, attempt_no DESC). With connector_id fixed by equality, the index supplies the exact order DISTINCT ON needs and the Sort node disappears. It does not reduce rows read: through PostgreSQL 17 there is no loose index scan, so the executor still walks every attempt in range rather than skipping to each next distinct key.
Give the complexity honestly: without the index the plan is a scan plus an O(n log n) sort over the connector's attempts; with it, O(n) ordered reads and constant working memory, where n is attempts in range, not distinct keys. If the distinct keys are a tiny fraction of attempts, a recursive skip-scan is the next step.

Follow-up

attempt_no is not monotonic in time because a retry was queued out of order. Does your query still pick the right row, and which column should it actually order by?
How would you return the latest attempt per key across all connectors in one environment without the query degrading to a full sort?
An ambiguous latest attempt means the target may or may not have been written. What does this query feed, and should ambiguous be treated as failed here?

Find watermark coverage gaps with a window function

Medium · window functions · gaps and islands · event tables

Each succeeded connector_run claims a half-open watermark range [watermark_from, watermark_to). Ranges for one connector can overlap, and a re-run can be nested entirely inside an earlier range. Write the query that returns, per connector_id, every interval not covered by any succeeded run, as (gap_from, gap_to), ordered by connector then time. State the frame clause you use and why the default one is wrong, and give the index that removes the sort.

Approach

Rule out lag(watermark_to) first and say why: ordered by start time, the immediately preceding row may end earlier than a row before it, so a nested or shorter range makes you report a gap that a wider earlier range already covers. The correct comparison is against the running maximum of every previous watermark_to, not the last one.
Compute max(watermark_to) OVER (PARTITION BY connector_id ORDER BY watermark_from, watermark_to ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING). The ROWS frame is load-bearing: the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes the current row, so the running max always covers the current row's own end and the query reports no gaps ever.
Add watermark_to as a tiebreaker in the window ORDER BY so rows sharing a start instant are ordered deterministically under a ROWS frame, then emit a gap where the running max is non-null and watermark_from is strictly greater than it. The first row per connector has an empty frame and a NULL max; that is not a gap unless you define a floor such as the binding's creation time, which you should decide explicitly.
Filter status = 'succeeded' in the window's input, and argue the classification: an ambiguous run may or may not have applied its range, so counting it as covered can drop data silently. Gap detection should treat ambiguous as uncovered and leave resolution to the reconciliation path.
Offer the shorter alternative with its precondition: on PostgreSQL 14 and later, range_agg(tstzrange(watermark_from, watermark_to, '[)')) per connector builds a multirange, and the gaps are its complement against the window you care about. It handles adjacency and nesting without any frame subtlety, at the cost of requiring that version.
Index (connector_id, watermark_from, watermark_to) WHERE status = 'succeeded'. It supplies the window's required ordering directly, so the plan is a WindowAgg over an index scan with no Sort, and the work is O(n) over that connector's succeeded runs.

Worked solution (30 min)

Load a fixture for one connector: [10:00, 11:00), [10:15, 10:30) nested inside it, and [11:30, 12:00).
Write the lag version first and record what it returns, so the failure mode is concrete rather than theoretical.
Replace lag with the running max under an explicit ROWS frame, then filter to rows where watermark_from exceeds it.
Add a second connector and a pair of runs sharing an identical watermark_from to exercise the tiebreaker.
Create the partial index and re-check the plan.

Expected result: SELECT connector_id, prev_end AS gap_from, watermark_from AS gap_to FROM (SELECT connector_id, watermark_from, watermark_to, max(watermark_to) OVER (PARTITION BY connector_id ORDER BY watermark_from, watermark_to ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_end FROM connector_run WHERE status = 'succeeded') s WHERE prev_end IS NOT NULL AND watermark_from > prev_end ORDER BY connector_id, gap_from; — on the fixture it returns exactly one row, 11:00 to 11:30.

Verify it yourself

The lag version reports 10:30 to 11:30, an hour of which is demonstrably covered by the first range; the running-max version reports only 11:00 to 11:30.
Remove the explicit ROWS frame and confirm the query returns no gaps at all, which is the default frame including the current row.
Two runs with identical watermark_from produce a stable result across repeated executions once watermark_to is in the window ORDER BY.
EXPLAIN shows WindowAgg over an Index Scan with no Sort once the partial index exists.

Follow-up

How do you distinguish a real gap from a period when the connector was deliberately paused?
The connector has run for two years and you only care about the last 30 days. What does adding that predicate do to the correctness of the running maximum?
How would you turn this query into an alert without it firing on the trailing edge, where the newest range is simply not written yet?

Generate month-end invoice lines exactly once during a failover

Medium · idempotency · write-heavy · consistency vs availability · failover

Billing generates invoice_line rows (generation_key UNIQUE, engagement_id, period_start, period_end, line_type, amount_minor, currency, fx_rate_numeric, status) from approved work_record rows for thousands of engagements inside a multi-hour month-end window, while late approvals are still landing and earlier partial runs are being retried. Mid-window, the billing database fails over and reads land on a replica 40 seconds behind. Design the run: what makes a repeat a no-op, what the handler does on a unique violation, and whether the run continues through the failover or stops.

Approach

Put the whole idempotency story in the key: generation_key deterministic over (engagement_id, period_start, period_end, line_type, generator_version), unique in the database. A concurrent second run then raises 23505 on insert, and the handler must catch that specific SQLSTATE, read the existing row back and return it as success. Surfacing the violation as an error is the production failure that actually happens - a retry that genuinely succeeded is reported failed and retried forever.
Keep generator_version inside the key on purpose. A corrected calculation is then a new key, which means a new line plus a credit note against the old one, rather than an overwrite of a figure a client has already seen. Pin fx_rate_numeric at issue for the same reason: a rate resolved at read time makes last month's invoice a different number this month.
Serialise per engagement rather than globally with pg_advisory_xact_lock(engagement_id) around the read-approved-work-then-insert sequence, so two workers cannot both observe the same approved set and both try to bill it. Parallelism comes from the thousands of engagements, so per-engagement serialisation costs nothing measurable and removes the entire interleaving class.
Decide the read requirement before the failover decides it for you. Generating from a replica 40 seconds behind hides every approval committed in that window and produces a line that looks correct and is short. This is the consistency-versus-availability call, and consistency wins here for a nameable reason: the failure is not a retryable error but an issued invoice that must be corrected by credit note and explained to a client. Read approved work from the primary, or gate the run on measured replication lag and let it pause rather than under-bill.
Make pausing cheap by making the run resumable: every finished engagement already has its generation key present, so a restart skips it with one index lookup. That only holds because the key is computed rather than a sequential cursor - a 'last processed id' would have to be reconstructed after an unclean stop.

Worked solution (30 min)

Run two generation workers concurrently against the same engagement and period and count invoice_line rows.
Insert a late approved work_record after the line is issued, then run generation for that period again.
Point the reader at a replica held 40 seconds behind, approve work inside that window, and generate.
Restart the full run from the beginning when it is half complete, and time the skip path.

Expected result: The concurrent workers produce exactly one row: the loser catches 23505, reads back and returns the same invoice_line_id. The late record does not mutate the issued line; it lands in the next period or as a credit note plus reissue, leaving the original row and its audit trail intact. The replica read omits the in-window approvals and produces a short amount_minor, which is exactly what the lag gate exists to prevent. The restarted run skips completed engagements at one index lookup each.

Verify it yourself

Grouping by (engagement_id, period_start, period_end, line_type) never yields more than one non-credit-note row.
No statement anywhere updates amount_minor on a row whose status is issued, and the database refuses it independently of the code.
Re-running the whole month produces zero new rows and zero errors.

Follow-up

A work record is approved an hour after its period was billed. Where does it go, and what does the client see?
Two workers contend for one engagement's advisory lock and the holder crashes mid-transaction. What is the state when the second one acquires it?
The run must finish before the month-end deadline and the primary is now the bottleneck. What work do you move off it, and what do you refuse to move?

Mint short-lived client credentials that expire with the engagement

Easy · credentials · ttl · audit · partitioning

The Access Broker mints credentials for client-owned systems. Its callers are connector jobs and operator sessions, roughly 400 mints a minute at peak against a 150 ms p99 budget. Every mint appends to credential_grant (grant_id, engagement_id, principal_id, target_system_id, scopes, issued_at, not_after, issued_by_request_id), and those rows stay queryable per engagement for a contracted retention period measured in years. Design the mint path and the audit store: how not_after is computed, what happens when the audit write fails, and how an auditor answers who held access to one client system last March.

Approach

Compute not_after server-side as min(requested_ttl, engagement.ends_on + access_grace_days - now) and refuse the mint when that is already non-positive. The requested TTL is a hint; the engagement window is a bound. Then subtract the verifier's clock-skew allowance: a validator that tolerates 60 seconds of skew accepts a token 60 seconds past not_after, so effective expiry is not_after plus that leeway and it has to land inside the grace window rather than just outside it.
Write the audit row in the same transaction that reserves grant_id, before the secret material leaves the process, and treat a failed audit write as a failed mint. A mint that failed is retryable; access that cannot be attributed to a named principal is permanent, and the asymmetry decides the ordering.
Make the mint idempotent on issued_by_request_id with a unique index, so a caller retrying after a timeout does not leave a second untracked credential alive. On 23505, read the row back. Then choose deliberately between a short replay window that returns the same material and a conflict that forces a fresh mint, and justify it: replay means the secret crosses the wire twice, conflict means a timeout costs the caller a new credential.
Range-partition credential_grant by issued_at monthly and index (engagement_id, issued_at DESC). The auditor's question then reads one or two partitions instead of the full history, and expiring a retention window becomes DETACH plus DROP - a metadata operation - rather than a delete of millions of rows that then has to be vacuumed.
Note that the cross-table rule cannot be a CHECK constraint, because a PostgreSQL CHECK cannot reference another table. Either denormalise the engagement's end into the grant row and CHECK against that local column, or enforce it in a trigger, and run a scheduled query that proves no live grant outlives its engagement.

Follow-up

The connector caches a credential for its full lifetime and the engagement is terminated ten minutes later. What is actually true about access, and for exactly how long?
An operator needs a week of access to a client system but the engagement ends in two days. What does the system do, and who is allowed to change that answer?
How would you prove to an auditor that no credential was ever minted against a closed engagement?

Environment list page slows as the estate grows

Easy · n+1 query · latency · batching

An estate console lists environments for an operator: environment_id, client name, isolation_tier, the desired release version string, the reported release version string, and the engagement's current status. A 10-row filter returns in 25 ms; the unfiltered 200-row page takes 640 ms. No single statement exceeds 0.5 ms, and the database records 553 statements for that request. Rows where reported_release_id is NULL are measurably cheaper than the rest. Diagnose the cause and give the fix, stating the statement count you expect afterwards.

Approach

Get the shape before theorising about plans. Divide the per-request statement delta in pg_stat_statements by the rows returned: 553 statements for 200 rows, none over 0.5 ms, is round trips rather than work, and rules out a bad plan immediately.
Account for 553 exactly, because the arithmetic is the diagnosis and not a detail. One driver query, one desired-release lookup per row, one current-engagement lookup per row, and one reported-release lookup only for the rows where reported_release_id is non-null: 1 + 200 + 200 + 152. The 152 is why client-managed rows are cheaper, and if the sum did not land on the observed number something else is firing too.
Sanity-check the cost model: 553 round trips at roughly 1 ms each is about 0.55 s on top of a 25 ms baseline, which accounts for the 640 ms. A hypothesis that does not reproduce the measured number is not finished.
Batch it. Collect the ids from the driver query, then fetch releases with WHERE release_id = ANY($1) and the open engagement versions with WHERE engagement_id = ANY($1) AND effective_to IS NULL, and assemble in memory. Three statements per page, independent of page size.
Check index usage before declaring victory. The engagement fetch must use the partial unique index on (engagement_id) WHERE effective_to IS NULL; without it, ANY($1) scans every amendment version of every engagement and the fix regresses harder than the bug did.
Pin the behaviour with a test asserting a constant statement count per request, since no individual query here is slow enough to ever trip a slow-query alarm.

Follow-up

The operator wants to sort the page by reported release version. Where does that sort have to happen now, and what does it cost?
Page size goes to 1,000. Which breaks first, the driver query or the ANY($1) parameter list?
reported_release_id NULL means unknown, not converged. How should the list render that, and what does the count at the top of the page then mean?

Suggested practice checklist

Four days sample coding, design, fundamentals and the practical rounds at deliberately shallow depth, which is enough to surface the topics you did not know were in scope. That map, rather than a guess made on day one, decides where the last three days go.

Day 1 — Coding, one pass at shallow depth

Solve one problem from each of six families, an array with two pointers, hash counting, binary search, a tree traversal, a graph traversal and one dynamic program, under a hard twenty-minute cap with no extensions, marking each finished, late, or stalled.
For every stall, write the exact move you could not make rather than the subject, so the note reads could not turn the recurrence into a loop rather than bad at dynamic programming.
Fix nothing today. The value of the pass is the unfixed record.

Deliverable: Six timed attempts marked finished, late or stalled, each stall carrying a named blocking move.

Day 2 — Design, one pass at shallow depth

Spend twenty minutes each on three different shapes, a read-heavy feed, a write-heavy ingest path, and something needing a transaction across two entities, stopping each at requirements, interface and data model.
After each, write the first question you could not answer, which is usually a number you could not estimate or a failure mode you had no vocabulary for.
Mark which of the three you would be most relieved not to be asked, and treat that as data rather than as a preference.

Deliverable: Three shallow designs, each with the first unanswerable question written at the bottom.

Day 3 — Fundamentals and the practical rounds

Answer eight short questions in writing at four minutes each, covering the material that fills the gaps between the big rounds: what happens between a URL and a rendered page, what an index costs on write, when a process is preferable to a thread, and what conditions a deadlock requires.
Do one thirty-minute practical task of the kind a take-home compresses: read an unfamiliar two-hundred-line file and write what it does, what you would change, and the one thing you remain unsure of.
Score every answer fluent, correct but slow, or absent, and keep the absent ones visible.

Deliverable: Eight scored short answers and one written reading of unfamiliar code.

Day 4 — The rounds that are about you, and the map

Deliver three behavioural answers aloud against a timer, a conflict, a failure you owned, and a decision made without enough information, marking any that ran past three minutes or contained no number.
Assemble the map: every marked item from days one to three on a single page, sorted by how likely it is to appear in your loop rather than by how uncomfortable it felt.
Choose exactly two areas for the remaining three days and write down what you are deliberately abandoning.

Deliverable: A one-page scored map of the whole surface area with two areas chosen and the rest explicitly abandoned.

Day 5 — First chosen area, to the depth you skipped

Work the higher-ranked area in four focused blocks, choosing items one level above where you stalled rather than repeating what already works.
After each block write the rule you extracted in one sentence with its precondition attached, since a rule carrying no precondition is exactly what fails under a variation.
Re-attempt the day-one or day-two item that exposed this area and compare against the original timing.

Deliverable: Four worked blocks, a timed re-attempt against the original, and three one-sentence rules with preconditions.

Day 6 — Second chosen area, where the gap is coverage rather than speed

Treat the second area differently from the first. Day five drilled something you could already half-do; this one is usually a topic you had simply never met, so build one worked reference example end to end and keep it, rather than attempting six problems badly.
Write down the vocabulary you were missing on day two or three, five terms at most, each with the one sentence that makes it usable in an answer rather than the textbook definition.
Redo the shallow attempt that exposed this area and note whether you now fail later in the problem, because moving the failure point is the realistic gain from a single day and is worth more than a score that did not change.

Deliverable: One worked reference example for the newly covered area, a five-term vocabulary list, and a note on where the failure point moved.

Day 7 — Reassemble the loop

Sit two rounds back to back with no gap, ordering them so the area you chose second comes last, because the map was built from rested, isolated attempts and the loop will reach your weaker area when you are already spent.
Write where the second round suffered from the first, which is normally the point at which structure collapses into narration.
Reduce the week to one page holding only the rules you can state without reading them.

Deliverable: Mock notes on cross-round carryover plus a one-page card of rules you can recite from memory.

Scope is read from what you decided

Team size, service count and tickets closed say very little. Seniority shows in the decision you owned: what you chose not to build, which constraint you traded away, whose objection you had to resolve before anything could move. A large project where you executed someone else's plan is a small story.

Can you describe a time you had to explain a complex technical solution to a non-technical stakeholder?

Medium · behavioural and engineering judgement

Approach

Give the blast radius: what could have broken, and what you measured.
Name the disagreement and how you resolved it with evidence.
Close with what you would do differently, concretely.

Follow-up

What did you decide not to do, and why?
How did you know your change caused the improvement?

How do you handle project-based deadlines when requirements shift unexpectedly?

Medium · behavioural and engineering judgement

Approach

Give the blast radius: what could have broken, and what you measured.
Pick a story where you made the decision, not one where you watched it.
State the situation in two sentences and spend the rest on the reasoning.

Follow-up

What would you do differently if you ran that again?
What did you decide not to do, and why?

Why are you looking for work at this stage of your career?

Medium · behavioural and engineering judgement

Approach

Name the disagreement and how you resolved it with evidence.
Give the blast radius: what could have broken, and what you measured.
State the situation in two sentences and spend the rest on the reasoning.

Follow-up

How did you know your change caused the improvement?
What did you decide not to do, and why?

What specific technical challenges are you seeking to solve in your next role?

Medium · behavioural and engineering judgement

Approach

Give the blast radius: what could have broken, and what you measured.
Pick a story where you made the decision, not one where you watched it.
Close with what you would do differently, concretely.

Follow-up

What would you do differently if you ran that again?
How did you know your change caused the improvement?