Interview guides · Algonquin College

Algonquin College Software Engineer Interview Questions & Guide 2026

Algonquin College software engineer prep on databases: indexes, query plans, and the anomalies an isolation level still permits.

The 60-second brief

Working as a Software Engineer at Algonquin College involves contributing to the technological infrastructure that supports a premier educational institution. You are not just writing code; you are building and maintaining the digital systems that faculty, staff, and students rely on daily to facilitate learning and administrative operations.

Most rounds test how you handle an under-specified problem more than what you recall. A wrong first approach you correct out loud is recoverable; a constraint you assumed silently and were never given is the expensive mistake.

Focus areas

Diff a roster snapshot before applying any deletes
Scope every query, cache and job by tenant
Make outbound grade publication idempotent and ordered

Overview

The role often requires a blend of technical precision and an understanding of institutional needs. Whether you are working on Enterprise CRM solutions like Salesforce or supporting specialized web-based applications, your contributions directly impact the efficiency of campus services. You will be expected to navigate complex requirements, translate them into functional software, and ensure that your solutions are scalable and user-focused.

This position is ideal for engineers who thrive in environments where technical work serves a broader public purpose. You will face challenges related to system integration, data management, and the need for high-availability services, making this a critical role within the college's IT ecosystem.

The Algonquin College interview process

Algonquin College candidates report 3 rounds · ≈ 3-5 weeks. The stages below are what candidates describe, not a published process.

Initial Screening

Before anything technical happens, someone has to decide which rung of the ladder your loop is calibrated to, and that decision sets the bar for every round after it. It comes from how you describe scope, not from your title, because titles do not convert cleanly between companies. The weak version of the answer is team size and years. The strong version names the largest change you shipped where nobody reviewed the design, what would have broken if you had been wrong, and what you were paged for. Get the level said out loud on this call, because the range and the loop both follow from it.

What to demonstrate

Whether the scope in your own account maps onto a level the team actually has an opening at, so a mismatch ends the process cheaply rather than after four interviewers have spent a day
Whether your title needs re-mapping: the same word describes very different amounts of independent decision-making at a twenty-person company and a ten-thousand-person one
Whether your compensation expectation can be filled at that level in the structure the role pays in, which is why the number gets asked for before any engineer is scheduled

How to prepare

Write down two changes from the last two years: the largest one you designed with nobody reviewing the design, and the largest one where someone more senior did. Lead with the first when scope comes up, and be ready to say which parts of the second were yours
Ask which level the loop is calibrated to and what changes at the level above it, then plan your weeks from that answer rather than from the posting
Settle a total-compensation range beforehand with the split named, base against bonus against equity and its vesting period, so a question about numbers gets a number instead of the word market

Technical Assessments

Most of the time lost in this format is not lost to thinking. It goes to a standard-library call you half-remember, an off-by-one in a loop bound, and a debugging loop that mutates code at random until something passes. When output is wrong, stop re-reading the whole function: take the smallest input that reproduces it and walk the state through by hand, printing intermediates if the environment allows. Guessing at a fix without a failing case you understand is how a five-minute bug becomes twenty, and the clock does not pause while you do it.

What to demonstrate

Whether you reach the right structure without a detour, and can write it from memory rather than only recall that one exists
Whether overflow is considered where the language has fixed-width integers, since a signed 32-bit value stops at 2,147,483,647 and then wraps in Java, is undefined behaviour in C++, and does not arise in Python, whose integers grow instead
Whether recursion depth is treated as a constraint on large inputs, given that CPython's default limit is 1000 frames and a deep recursion can exhaust the stack in any language where an iterative version would not
Whether a failing case is isolated and explained before any edit is made to the code

How to prepare

From an empty file and with no references open, implement the pieces you lean on most: a heap push and pop, an iterative DFS with an explicit stack, and a binary search whose midpoint is written lo + (hi - lo) / 2, which avoids the overflow that (lo + hi) / 2 can hit in a fixed-width integer type
Time yourself on the ten library calls you look up most, such as sorting with a custom comparator, splitting and joining strings, and finding the next key at or above a value in an ordered map, until the lookup is gone
Take a solution you know is broken and, before touching it, write one sentence naming the input, the expected value and the actual value. Repeat until you do it without deciding to.

In-depth 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

Applying a bulk roster snapshot as authoritative, including its absences.

A truncated file, a partially completed export, or a change in the upstream identifier scheme all present as a large set of rows that vanished, and nothing in the file distinguishes that from a real mass withdrawal. Applied naively it deprovisions enrollments in a single run, and the recovery is not just an undo because in-progress work and derived caches have already moved. Every pipeline that survives has a proportional gate, a recorded diff, and a restartable apply keyed on the snapshot digest.

Running learner submissions without hard, enforced resource ceilings.

Submitted code is untrusted input written by people who are actively learning and sometimes actively probing. Without a wall-clock limit alongside the CPU limit, a sleeping or blocked process pins a worker; without a process/thread cap, a three-line fork bomb takes the host; without an output cap, a stray print loop fills the disk and the log pipeline; without network isolation, a submission can reach internal services or exfiltrate the hidden tests. Each ceiling has to be enforced by the kernel or the runtime, since a limit checked by the harness after the fact is not a limit.

Choosing a schema before the access patterns are known

Write the queries first, with their filters, sort orders, cardinalities and which ones sit on the latency-critical path, then design tables and indexes to serve them. An index nothing queries still costs write throughput and storage, and a hot query with no supporting index becomes a full scan that only hurts once the table is big.

Listing technologies instead of trade-offs

Name the property the design needs first, such as ordered range scans, multi-entity transactions, cheap appends, or a predictable p99, then pick something that provides it and say what it gives up in exchange. Almost any component is defensible once you state the requirement it satisfies and the one it sacrifices.

Questions & practice

Detect two-device autosave interleaving in an event stream

Medium · sliding window · event time · watermarks

Autosave events arrive on a stream as (attempt_id, client_instance_id, seq, received_at) at roughly 4,000 events per second, out of event-time order by at most 120 seconds. For each attempt, report the earliest 600-second window in which that attempt's autosaves alternate between client instances — A, then B, then A again — which indicates a second tab or device rather than a reload. A plain handoff, where one instance stops and another starts, must not be reported. Memory must be bounded by concurrency, not by total events. State your bounds.

Approach

Partition by attempt_id so every event for one attempt lands on one worker, then buffer per attempt until the watermark — the maximum received_at seen minus the 120-second lag bound — passes an event's timestamp. Only then is that event's event-time position final. Anything arriving after its watermark goes to a side output and is counted, not silently dropped, because a rising late rate is the signal that the lag bound is wrong.
Inside one attempt, slide two pointers over the event-time-ordered buffer, advancing the right edge one event at a time and evicting from the left while right.t - left.t > 600. Each event enters and leaves exactly once, so the window maintenance is O(1) amortised per event.
Represent the window as a run-length-compressed deque of (client_instance_id, count) rather than a raw list. Pushing on the right either increments the tail run or appends a new one; evicting on the left decrements the head run and pops it at zero. Runs never merge, because adjacent runs have different ids by construction, so both operations stay O(1).
Read the alternation criterion straight off that structure: three or more runs in the window means A, B, A or three distinct instances, which is interleaving. Exactly two runs is a clean handoff — a reload where one instance stops and the next begins — and must not fire. Counting distinct ids instead of runs is what produces a false positive on every browser refresh.
Bound the memory arithmetically. At 4,000 events per second with one autosave per learner every 25 seconds, about 100,000 attempts are active, and a 600-second window holds roughly 24 events each. That is 2.4 million live events; packed at 48 bytes it is about 115 MB, and several times that with boxed objects. The run-compressed deque is smaller still, since a single-instance window collapses to one entry.
Separate this from the write-path rule it resembles. The attempt rejects an autosave whose seq is below last_autosave_seq, which protects the stored state; it does not detect anything, because two tabs sharing a monotonic counter produce strictly increasing sequences and never trip it. Detection and rejection answer different questions and both are needed.

Worked solution (35 min)

Implement the per-attempt reorder buffer keyed on event time, releasing events only once the watermark has passed them, and count anything later as a side output.
Implement the run-compressed window with explicit push-right and evict-left operations, asserting after each that no two adjacent runs share an id.
Emit the window's start instant the first time the run count reaches three for an attempt, and stop tracking that attempt for reporting.
Feed three fixtures: a clean handoff A...A then B...B; an interleave A, B, A inside 600 seconds; and an A, B, A where the two A events are 700 seconds apart.
Replay each fixture with the events shuffled within the 120-second lag bound and confirm the verdicts are unchanged.

Expected result: The handoff reports nothing, the tight interleave reports a window whose start equals the first A event's instant, and the 700-second case reports nothing because the window never holds three runs at once; all three verdicts survive shuffling inside the lag bound.

Verify it yourself

Adjacent runs in the deque never share a client_instance_id at any point during a replay.
Total events pushed minus total evicted equals the current window size after every step.
Resident memory tracks active attempts rather than total events, verified by replaying the same hour twice as long.
An input where every attempt has exactly one instance reports zero windows and leaves the deque at length one throughout.

Follow-up

A learner's connection drops for ten minutes and the client replays its buffered autosaves on reconnect. Does your detector fire, and should it?
You must now also report how many seconds of overlap the two instances had. What changes in the window structure?
The lag bound is violated and events arrive 15 minutes late for one tenant. What does your side output let you do that a dropped-event counter would not?

Diff a roster snapshot against live enrollments in one pass

Easy · hash join · diffing · soft delete

A tenant's bulk roster snapshot has been normalised into N incoming enrollment records carrying section_id, person_id, role (learner, instructor, aide, observer), source, begin_date and end_date. You also hold M stored enrollment rows for that tenant where deleted_at IS NULL. N and M are each up to 1,000,000. In one pass over the incoming records, classify each as an add, an update or a no-op, and produce the set of stored rows the snapshot never asserts, which becomes deletes_proposed on roster_sync_run. State your time and space bounds in bytes.

Approach

Fix the key first, because the schema already decided it: the partial unique index is (tenant_id, section_id, person_id, role) WHERE deleted_at IS NULL, so role is part of the identity. One human legitimately holds two live rows in a section (instructor and observer), and keying on (section_id, person_id) alone collapses them into one, emitting a spurious update and a spurious delete on every run.
Load the M stored rows into an open-addressed hash map from that 3-tuple to (enrollment_id, comparable_fields, seen_flag). Packed, the key is 24 bytes and the payload about 8, so at a 0.7 load factor that is roughly 45-50 MB for a million rows; boxed objects in a JVM or CPython map cost 5-10x that, which is the number that decides whether this fits in a worker.
Stream the incoming records and probe once each: miss is an add, hit with equal comparable fields is a no-op, hit with any difference is an update. Set the seen flag on hit. After the stream, the stored entries still unseen are deletes_proposed; count them and divide by M to get the fraction the gate is evaluated on.
Handle revival explicitly: an incoming record that misses the live map may still match a soft-deleted row. Look that up and clear deleted_at in place rather than inserting a new enrollment_id, because the partial index constrains only live rows and will not catch the duplicate — the history simply fragments into a new id at every term boundary.
Complexity is O(N + M) expected time and O(M) space. State the alternative and its crossover: sorting both sides and merging is O(N log N + M log M) with O(1) working memory beyond the sort, and it wins as soon as M stops fitting in RAM, since an external sort degrades gracefully where a hash map does not.
Guard the semantics before any of this runs. Absence means deletion only for a bulk feed; on a delta feed absence is silence and only an explicit tobedeleted status removes anything. A tenant that switches feed modes mid-term will otherwise have one delta file read as a snapshot, which proposes deleting almost everything.

Follow-up

The upstream system changes its external_sourced_id scheme, so every incoming row looks new and every stored row looks absent. How do you reconcile without creating duplicate persons?
The apply aborts halfway through. What makes the next run finish rather than double-apply, and what does snapshot_digest do for you?
M grows to 50 million for one tenant. Which of your two algorithms do you run, and what changes about the delete detection?

Stream a quoted roster CSV without loading the file

Easy · parsing · state machine · streaming

You are handed a 2 GB roster export as a byte stream. It follows RFC 4180: any field may be quoted, a quoted field may contain commas and CRLF, a literal quote inside a quoted field is written as two quotes, and the file may open with a UTF-8 byte order mark. There are up to 10,000,000 records and a single field may reach 64 KB. Write a reader that yields one record at a time without buffering the whole file and that fails loudly on a malformed file rather than emitting a short record. State your bounds.

Approach

Refuse the shape that fails first: splitting the stream on newlines and then splitting each line on commas. A quoted field may contain a CRLF, so line splitting cuts records in half, and the damage is silent because both halves parse into plausible short records.
Write a byte-level state machine with four states — field start, unquoted field, quoted field, and quote-seen-inside-quoted. In the last state a second quote emits one literal quote and returns to quoted; a comma or newline ends the field; anything else is a malformed file. That table is the whole parser and it is the part to get right on paper before typing.
Strip the BOM only at offset zero, comparing the first three bytes against EF BB BF. Left in place it becomes part of the first header name, so the header lookup for that column misses and the column reads as absent for every record in the file.
Cap field length at the stated 64 KB and record length at a sane multiple of it. Without a cap, one unbalanced quote makes the parser treat the remaining 2 GB as a single field and the process dies of memory exhaustion rather than telling you the file is broken at byte 12,004.
Treat end of stream inside a quoted field as a hard error, not an implicit close. A truncated upload is the most common malformed input here and it is byte-for-byte indistinguishable from a complete file if you close the field silently — the missing rows then present downstream as a mass withdrawal.
Complexity: O(n) time in bytes with one pass and no backtracking, and O(longest field + longest record) space, which is bounded by the caps rather than by the file. Read in fixed blocks and keep the partial field across block boundaries instead of reading line-wise.

Follow-up

The file arrives gzipped and you must report progress as a percentage. What can you actually report, and what does that do to your memory bound?
Two exporters disagree on line endings and one emits a bare LF inside a quoted field. Does your state machine care, and should it?
How do you distinguish a truncated file from a complete one when the byte stream itself gives you no signal?

Decide whether the gradebook grid is derived or materialised

Hard · denormalisation · read patterns · cache invalidation

An instructor gradebook renders 35 learners by 60 published assignments in one request against a 300 ms p95 budget. Truth lives in attempt(tenant_id, assignment_id, person_id, attempt_no, state, is_late) and score(attempt_id, tenant_id, score_given, revision), and the policy is best attempt wins. Separately, a district report crosses 90,000 learners and 40 million attempts. Decide with arithmetic whether the grid is derived per request or materialised into gradebook_cell, and if materialised give its key, its staleness detector, and every event that invalidates a cell.

Approach

Do the arithmetic before choosing anything. The grid is 2,100 cells. With an index on attempt (tenant_id, assignment_id, person_id, attempt_no) and one score lookup per selected attempt, that is a few thousand index tuples and a few thousand buffer accesses against a warm cache — single-digit milliseconds, an order of magnitude inside a 300 ms budget. Deriving it wins, and the thing you avoid buying is an invalidation set you would own forever.
Identify the read that actually breaks, because it is not this one. The district report crosses 40 million attempts: that is scan-shaped, has no per-request deadline, and belongs in a periodic rollup with its own store and its own freshness SLA. Conflating the two is how a live-path cache gets built to fix an analytics problem.
Name the condition that would change the answer, so the decision is falsifiable rather than a preference: a policy of average-across-attempts with per-item weighting, a grid that grows to 200 assignments across several terms, or a term-end hour in which 10^3 sections open at once. Each multiplies the per-request work rather than the data, which is the signal that a projection is now cheaper than the recomputation.
If you do materialise, key gradebook_cell on (tenant_id, section_id, assignment_id, person_id) and store source_attempt_id and source_score_revision on the row. Those two columns are what turn 'is this stale' from an opinion into a comparison, and they let a repair job find divergence by joining rather than by rebuilding everything.
Enumerate invalidation exhaustively, since the omissions are where this design fails: a newly graded attempt, a regrade that bumps score.revision, an attempt voided after an integrity review, a change to the assignment's policy or point total, an enrollment added or soft-deleted (a withdrawn learner's cell must stop rendering without being deleted, because the score still exists), and a content version repin. Each is an event the projection consumes; a cell written inline by the request path is how a projection quietly becomes the system of record.
Write the consistency contract down: the projection is derived, rebuilt idempotently from (attempt_id, revision), never authoritative. When cell and score disagree, score wins and the cell is rebuilt, and a scheduled comparison over a sample proves it rather than assuming it. Carry tenant_id in the table and in every cache key — a projection keyed on assignment_id alone is exactly the shape that renders one district's grades inside another's page.

Worked solution (40 min)

Write the derived query and measure it on a seeded section with EXPLAIN (ANALYZE, BUFFERS), recording buffers and runtime at a cold and a warm cache.
Multiply out the term-end scenario: sections opening per minute times cells per section times buffers per cell, and compare against the connection and buffer budget you have.
Draft gradebook_cell with source_attempt_id and source_score_revision, and write the rebuild as an idempotent upsert keyed on the cell key.
List every invalidation event and, for each, the cells it touches; find the one with the widest fan-out and state its cost.
Write the divergence check as a query comparing a sample of cells against a freshly derived value.

Expected result: A stated decision with numbers behind it: derive on the live path at this size, roll up separately for district analytics, and a projection design held in reserve whose staleness is detectable by comparing source_score_revision rather than by trusting a TTL.

Verify it yourself

The measured warm-cache runtime for the derived grid is at least an order of magnitude under the 300 ms budget, or the decision flips and you say so.
Every invalidation event in the list maps to a concrete set of cell keys, with no event resolved as 'rebuild everything'.
The divergence query over a sample returns zero rows on a quiet system and non-zero immediately after a regrade with the consumer stopped.
No query or cache key in the design can be written without tenant_id, including the repair job's.

Follow-up

An instructor edits one score by hand. How long until the grid shows it, and what does the instructor see in the meantime?
As sections per tenant grow, what breaks first: the rebuild job, the invalidation fan-out, or the read you were trying to protect?
Would you materialise the column totals and the class mean as well, and what does that do to the invalidation set?

Explain why the grading queue index stopped serving its ORDER BY

Medium · indexing · partial index · planner statistics

attempt holds 2 billion rows and 99.7% are state='graded'. The grading queue runs SELECT attempt_id FROM attempt WHERE tenant_id = $1 AND state IN ('submitted','grading') ORDER BY submitted_at LIMIT 100 against index ix_attempt_queue (tenant_id, state, submitted_at). At the bell the query takes 30 seconds and EXPLAIN shows a Sort above a bitmap heap scan, even though the queue is only a few thousand rows. Explain why that index cannot serve the ordering, give the replacement definition, and say how the replacement behaves as rows leave the queue.

Approach

Start from what the index actually orders. Its tuples are sorted by (tenant_id, state, submitted_at), so with two state values the scan yields all 'grading' rows in submitted_at order, then all 'submitted' rows in submitted_at order. The query wants one submitted_at order across both, which that physical order does not provide, so the planner must sort the whole matching set and the LIMIT cannot stop the scan early. With a single state constant the same index satisfies the ordering perfectly — the plan changed because of the IN list, not because of the data.
Replace it with a partial index that moves the predicate out of the key: CREATE INDEX CONCURRENTLY ix_attempt_queue ON attempt (tenant_id, submitted_at) WHERE state IN ('submitted','grading'). Ordering is now satisfied directly, the LIMIT stops after 100 index tuples, and the index covers roughly 0.3% of rows so it stays resident in cache instead of competing with the table.
State the precondition, because it is the part people skip: the planner uses a partial index only when it can prove the query's predicate implies the index predicate. A literal IN list matching the definition proves; state = ANY($1) with the list bound as a parameter does not, so the queue's state set has to be literal in the SQL, not passed in.
Fix the estimate separately from the access path. With five states the planner already has each one in its most-common-values list, so single-column selectivity is fine; what it gets wrong is the dependence between tenant_id and state, because a bell means one tenant owns nearly the whole queue while the planner multiplies the two selectivities as if independent. CREATE STATISTICS ON tenant_id, state FROM attempt corrects it, and you ANALYZE explicitly after a bulk transition rather than waiting for autovacuum, whose analyze threshold of 50 + 0.1 x reltuples needs 200 million changed rows on this table.
Account for the lifecycle. A row leaves the queue by UPDATE state='graded', so its new version is not in the partial index at all while the old index entry remains until vacuum reclaims it. The index therefore churns in proportion to throughput rather than to depth: size it and vacuum it against how many attempts pass through per day, not against how many sit in the queue at once.
Keep the alternative in your pocket for when the partial index is not acceptable: two explicit scans merged, (SELECT ... WHERE state='submitted' ORDER BY submitted_at LIMIT 100) UNION ALL (SELECT ... WHERE state='grading' ORDER BY submitted_at LIMIT 100) ORDER BY submitted_at LIMIT 100, which keeps the existing index and pushes the LIMIT into both branches. Confirm whichever you choose with EXPLAIN on your major version rather than from memory, since array-key handling in b-tree scans has changed between releases.

Follow-up

The queue drains and refills eight times a day. What is the vacuum strategy for this index, and what do you monitor to know it is losing?
A second consumer claims rows with FOR UPDATE SKIP LOCKED. Does the plan survive, and what does skipping do to the ordering guarantee?
Support wants oldest-first fairness across tenants rather than within one. Does the same index serve that query?

How do you approach the migration of legacy data into a new CRM environment?

Medium

Approach

Say what you would check first and why it is the highest-information step.
State your assumptions explicitly before working the problem.
Work from the requirement backwards to the design.

Follow-up

How would you know your answer was wrong?
What assumption would you test first?

How do you ensure the code you write is maintainable and well-documented for future developers?

Medium

Approach

Say what you would check first and why it is the highest-information step.
State your assumptions explicitly before working the problem.
Work from the requirement backwards to the design.

Follow-up

What assumption would you test first?
How would you know your answer was wrong?

Shape the launch bootstrap response for one round trip

Medium · response shape · caching · cache invalidation · stampede

At the start of a class period the embedded frontend needs, before first paint: the person's role in the section, the assignment policy, their current attempt if one exists, and the content manifest URL. Today that is four GETs. The caller renders inside someone else's page, so a failure is a blank frame, and it retries twice before giving up. Design one bootstrap response: its shape, what is embedded versus linked, its cache-control and validators, and how an enrollment change invalidates it. Tens of thousands of these arrive within sixty seconds.

Approach

Decide embed versus link by lifetime, not convenience. Policy and role change on human timescales and are safe to embed; the attempt mutates every twenty seconds and must be linked or embedded with its own version so a stale copy is detectable; a short-TTL signed media URL must not sit in a cacheable envelope at all, because the signature's expiry and the envelope's max-age are different clocks and the shorter one wins.
Split into two documents along the varies-by boundary rather than shipping one: a section-scoped envelope identical for every learner in the section, and a small private per-person document carrying role and the attempt handle. One shared fill amortised over thirty learners is the difference between thirty cold origin reads per section at the bell and one.
Key and label them honestly. The per-person document is Cache-Control: private with a cache key carrying tenant, section and person; the shared document may be cached more widely only because it contains no person id, no role and no signed URL. A shared cache key that forgets the tenant dimension is a cross-tenant leak that no test will catch, since the response it serves is entirely plausible.
Derive the ETag from the underlying versions (content_version_id, assignment updated_at, and a section roster generation counter) so a conditional request answers 304 without re-rendering. Bumping the generation on the enrollment-changed event invalidates every derived key at once, which is what makes invalidation a single write instead of an enumeration of keys.
Protect the cold key: single-flight per key plus a short stale-while-revalidate window. Thirty thousand launches in sixty seconds against a cold shared key is thirty thousand identical origin reads, and the step is too short for a reactive autoscaler to help.
Design partial failure into the shape. If the content service is slow but the attempt store is healthy, return what you have with an explicit per-part status rather than a 500; the frontend can render the assignment and poll for the rest, where a 500 leaves it nothing to draw inside a frame it does not own.

Worked solution (30 min)

List every field first paint needs and label each with its lifetime and its varies-by dimensions (tenant, section, person, role, content version).
Cut along that boundary: anything varying by person leaves the section-scoped document, and anything with its own expiry leaves the cached envelope.
Write the ETag input tuple and prove that a due-date edit, a content republish and an enrollment change each change it.
Do the arithmetic for the step: thirty thousand launches over sixty seconds across roughly a thousand sections is about a thousand shared fills plus thirty thousand small private reads, against a hundred and twenty thousand reads for the four-GET version.
Add single-flight and measure origin reads with five hundred concurrent requests against one deliberately cold key.

Expected result: Two documents, a section-scoped ETag-validated envelope with a defensible max-age and a small private per-person document, a cache key carrying every varies-by dimension, invalidation by a section generation counter, and single-flight on the shared key, so first paint costs one round trip plus at most one conditional revalidation.

Verify it yourself

Five hundred concurrent cold requests for one section produce one origin fill, not five hundred.
Changing an enrollment changes the ETag of the affected documents without deleting keys one by one.
No response body that a shared cache may store contains a person id, a role, or a signed URL.

Follow-up

A learner opens the same assignment in two tabs. Which part of your response must not be cached in the browser, and what breaks if it is?
Give max-age a number and defend it against an instructor who edits the due date two minutes before the period starts.
What does the bootstrap return for a person holding two roles in the same section?

School progress report slows tenfold after a repository refactor

Easy · n+1 queries · latency regression · query batching

A school-wide progress report went from 1.1 s to 9.5 s after a release and now trips a 10 s gateway timeout for the largest schools. Application CPU is flat. pg_stat_statements shows statements per request up from 3 to about 4,900 for a 2,400-learner school, with mean execution time unchanged at 0.2 ms and rows per statement unchanged. Measured database round trip is 1.1 ms. The release introduced a repository that loads enrollments, then fetches the person and the latest attempt per enrollment. Give an ordered diagnostic checklist, the root cause, and the fix.

Approach

Order the checklist by what the evidence already rules out. First, split the added time between application and database: take a pg_stat_statements delta across the release boundary and compare total_exec_time to the request-time delta. Second, sort that delta by calls, not by total time. Third, only if a single statement dominates do you look at plans or indexes. Here mean_exec_time and rows per statement are unchanged and calls are up 1,600x, so plans and indexes are not the defect and you stop reading EXPLAIN output.
Confirm the shape by checking what the call count tracks. An N+1 shows one normalised query whose calls scale with learners per school rather than with requests per second, and whose mean_exec_time is small enough that it never appears in a slow-query log. Two such queries here, at roughly 2 per enrollment, matches 4,900 statements for 2,400 learners.
Do the arithmetic before proposing anything: the cost is serial round trips, 4,900 x (1.1 ms round trip + 0.2 ms execution) = about 6.4 s of the 8.4 s regression. The remaining 2 s is per-row object mapping. This tells you a faster database or a bigger instance cannot recover the time, because almost all of it is waiting.
Collapse to three statements: one scan of enrollment for the school's sections, one SELECT over person keyed on the distinct person_ids, and one latest-attempt query. Write that third one as SELECT DISTINCT ON (assignment_id, person_id) ... WHERE tenant_id = $1 AND assignment_id = ANY($2) ORDER BY assignment_id DESC, person_id DESC, attempt_no DESC, and hydrate in memory. All three ORDER BY columns descend for a reason: a backward b-tree scan reverses every key column at once, so the existing UNIQUE (tenant_id, assignment_id, person_id, attempt_no) index serves that ordering with no sort node, given the equality predicate on the leading tenant_id that makes it constant. The ordering that reads more naturally, ORDER BY assignment_id, person_id, attempt_no DESC, is mixed-direction and that index cannot produce it -- it costs a full sort, or on PostgreSQL 13+ an incremental sort on the ascending (assignment_id, person_id) prefix. If you want the ascending group order in the plan itself, the price is a second index on (tenant_id, assignment_id, person_id, attempt_no DESC); the report is small enough after grouping that ordering it during hydration is cheaper than carrying that index.
Get two domain details right in the rewrite. Latest attempt is by attempt_no, not by started_at, because a resumed attempt keeps its original started_at. And every batched statement keeps its tenant_id predicate, because batching by id alone is exactly the shape that lets a repository-level tenant guard be bypassed.
Add a per-request statement-count budget asserted in an integration test so the next accidental lazy load fails the build rather than a term-end report.

Follow-up

The report is read far more often than the underlying data changes. What would you materialise, and what event invalidates it?
A district asks for the same report across 40 schools. Does your three-statement version still hold, and what is the new bound?

Suggested practice checklist

Roughly ninety minutes on weeknights with one longer weekend block. The plan cuts scope rather than compressing everything, on the assumption that one thing finished per night beats four half-started.

Day 1 — Fix the scope and take a cold baseline

Read the role description and write the three things the loop will almost certainly test, then write an explicit not-doing list and keep it visible all week.
Take one twenty-five-minute coding problem and one fifteen-minute design prompt cold, and write the single sentence naming what blocked each, because those two sentences decide where the remaining evenings go.
Set the week's rule: one thing finished every night, including the night you only have forty minutes.

Deliverable: A one-page scope with a not-doing list and two cold attempts, each carrying one sentence on what blocked it.

Day 2 — One pattern, written three times from blank

Choose the single pattern most likely to appear in your loop and write it three times from an empty file rather than editing the previous attempt.
On the third pass, write the invariant as a comment before the loop body and the complexity before the first line of code.
Stop at ninety minutes even if the third version is imperfect, and write the one thing you would fix given another hour.

Deliverable: Three independent implementations of the same pattern plus a note on what changed between them.

Day 3 — One design, only to the depth you can defend

Take one system shape and go only as far as requirements, interface and data model, refusing to draw a box you could not survive a follow-up about.
Attach one number to each non-functional requirement, deriving it rather than asserting it, and write the assumption the number rests on.
Write the one tradeoff you are choosing against and the observation that would make you reverse it.

Deliverable: One design at interface-and-schema depth with derived numbers and one written reversible tradeoff.

Day 4 — Only the fundamentals you will have to defend

Write, in under two hundred words each, the answers to the two questions that follow almost any implementation: why this structure and not the obvious alternative, and what happens to this code at a hundred times the input.
Write what an index actually costs: faster lookups on the indexed columns against a write that now maintains a second structure, plus the cases where the planner declines to use it anyway, low selectivity, or a predicate wrapping the column in a function.
Delete any answer you cannot deliver aloud in under a minute, since an answer that needs reading is not an answer you have.

Deliverable: Three written answers, each under two hundred words and each timed aloud.

Day 5 — Your own work, timed

Write a ninety-second and a four-minute version of your main project and time both aloud rather than reading them.
Prepare the two follow-ups that always come: what you would do differently, and how you knew it worked.
Put one number in the first sentence and be ready to say exactly where it came from and what it excludes.

Deliverable: Two timed narratives with one defensible number in the opening line.

Day 6 — The one full rehearsal, in the weekend block

Run a sixty-minute mock covering a coding round and a design round in one sitting with no break, because sustained attention is the thing evenings have not trained.
Immediately afterwards, and before hearing any feedback, write the three moments you lost the thread.
Spend the rest of the block only on those three moments, and on nothing you merely feel shaky about.

Deliverable: Mock notes naming three failure moments with a specific fix written under each.

Day 7 — Taper

Write the twenty-minute warm-up you will actually do on the morning: one problem you can already solve from a blank file, one design you can narrate, and nothing you have never seen.
Re-read only your own notes from this week and open no new material.
Write the logistics down: the editor or shared document you will be working in, whether execution and lookups are permitted, and the sentence you will use when you do not know something.

Deliverable: A one-page card holding the design structure, the project numbers, and the logistics.

Know where your numbers came from

Bring the two or three numbers the story rests on and know how they were collected. A p99 whose timer starts inside your handler excludes the time a request spent queued, so it can sit flat while users wait longer. Give the window, the percentile and what the measurement left out, or drop the number.

Can you describe your experience with Salesforce object relationships and security models?

Medium · behavioural and engineering judgement

Approach

Close with what you would do differently, concretely.
State the situation in two sentences and spend the rest on the reasoning.
Give the blast radius: what could have broken, and what you measured.

Follow-up

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

Why are you interested in applying your engineering skills specifically to the academic sector?

Medium · behavioural and engineering judgement

Approach

Pick a story where you made the decision, not one where you watched it.
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?
What would you do differently if you ran that again?

Describe a time you had to explain a highly technical issue to a non-technical stakeholder.

Medium · behavioural and engineering judgement

Approach

Close with what you would do differently, concretely.
State the situation in two sentences and spend the rest on the reasoning.
Name the disagreement and how you resolved it with evidence.

Follow-up

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

How do you manage your time when balancing multiple small-scale contracts or projects?

Medium · behavioural and engineering judgement

Approach

Give the blast radius: what could have broken, and what you measured.
Close with what you would do differently, concretely.
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?
How did you know your change caused the improvement?