Interview guides · Academy Sports + Outdoors

Academy Sports + Outdoors Software Engineer Interview Questions & Guide 2026

Academy Sports + Outdoors software engineer prep on databases: indexes, query plans, and the anomalies an isolation level still permits.

The 60-second brief

A Software Engineer at Academy Sports + Outdoors plays a vital role in building the digital infrastructure that powers one of the nation’s leading sporting goods retailers. You are not just writing code; you are directly contributing to the omnichannel ecosystem that connects millions of customers with the gear they need for their active lifestyles. Whether you are working on identity management systems, application operations, or customer-facing platforms, your work ensures that the digital shopping experience is seamless, secure, and highly performant.

The loop does not sample the job evenly, and arguing about that in the room costs you. Daily work is mostly incremental change inside code someone else wrote, while the loop samples narrow slices of it; prepare for the slices and save the realism argument for your questions at the end.

Focus areas

Diff a roster snapshot before applying any deletes
Validate a signed launch without trusting the client
Sandbox untrusted learner code with hard limits

Overview

This role requires a blend of technical precision and business-minded problem-solving. You will work within diverse, collaborative teams to solve complex challenges related to high-scale retail operations. Because Academy Sports + Outdoors is continuously innovating its digital footprint, you will be expected to adapt to evolving technologies and contribute to high-impact projects that directly influence the company’s ability to compete in the retail market. It is an environment that values professional growth, technical rigor, and a genuine passion for the brand.

The culture at Academy Sports + Outdoors is described by candidates as genuine and professional, with a strong emphasis on providing respectful and timely feedback throughout the hiring process.

The Academy Sports + Outdoors interview process

Academy Sports + Outdoors 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

Hiring Manager Interview

Expect the estimate to be probed harder than the outcome. A manager asking how long something will take is checking whether your number covers the parts that reliably go wrong: migrating the rows that already exist, and the second service that has to change at the same time. A single confident number is the weak answer. The strong one states what the estimate assumes, names the unknown that could double it, and says what you would cut first if the date were fixed. Scope is the variable you control; hours are not.

What to demonstrate

Whether an estimate arrives with its assumptions attached, including what it excludes and how much of it is waiting on review, environments or another team
Whether you can name work you cut and the person you told, since a cut nobody heard about surfaces later as a surprise rather than a decision
How you order two tasks that both look urgent, and whether that order survives being told the deadline moved in by a week
Whether you separate what must be done before launch from what must be done before the thing can be left running unattended

How to prepare

Take the last three things you shipped and write down, from memory, the estimate you gave and the time it actually took. Bring the largest miss and why it missed; a candidate who has never missed has never committed to a date.
Against your current queue, write who is blocked until each item lands. Anything with nobody behind it becomes your worked example of what you would drop and how you would say so.
Rehearse the fixed-date answer aloud: which slice ships, which slice is deferred and written down somewhere it will be found, and the one thing you would refuse to cut because removing it makes the release unsafe rather than smaller.

Multi-Round Technical Assessment

The same problem is scored by two different mechanisms depending on the format, and preparing for one does not cover the other. With a person watching, partial progress is visible and a hint is a correction you can absorb; silence is the expensive failure, because nobody can read a half-written function. With an automated grader there is no partial credit for what you were about to do, nobody to ask, and the worked examples in the prompt are the entire specification. Read them as a contract, down to whether an empty result should be an empty list or no output at all.

What to demonstrate

In a live session, whether your commentary tracks what your hands are doing, and whether a hint redirects you or gets defended against
In an automated one, whether you cover the cases the examples do not show, since the hidden cases are where the score moves
Whether you manage the clock on purpose: abandoning an approach that is not converging while there is still time to write something simpler that finishes

How to prepare

Have someone hand you a problem and feed you one deliberately wrong hint. Practise testing it against a concrete case instead of accepting or rejecting it on authority.
Do one timed run a week in a plain browser editor with autocomplete, linting and your own snippets switched off, which is closer to what these environments give you
For the automated format, write the harness before the solution: a main that feeds the worked examples plus an empty and a single-element case and prints expected against actual, so a wrong submission is caught by you first

Where candidates lose points

The launch works locally and breaks inside the embedding page, because the session cookie is a third-party cookie.

A standards-based launch finishes with a cross-site form POST back to the tool, so the state and session cookies must carry SameSite=None; Secure or the browser will not send them at all: the common Lax default is only released on top-level GET navigations, never on a cross-site POST. Correct attribution is not sufficient either, and the two browser behaviours fail differently. An engine that blocks third-party cookies outright never stores the cookie, so the state check fails on every launch; an engine that partitions them instead keeps the launch working but scopes that session to the one embedding site, so the session established inside the frame is not the session the same user has when they open the tool directly. Local development is same-site, so all of this passes and the failure surfaces only in the embedded context as a generic 'invalid state' error. The fix is a designed fallback rather than a retry: request storage access, or break out to a first-party window for the first launch and bind the session there.

Treating the launch subject identifier as a global user id, or merging identities on email.

The subject claim is stable only within its issuer, so the same human arriving from a second platform is a different subject and must be a second external identity bound to the same person. Email is worse than useless as a merge key here: privacy settings frequently suppress it from the launch entirely, districts recycle addresses between graduating and incoming students, and younger learners often have none. A merge on email eventually joins two children's work into one account, which is both a grade bug and a privacy incident.

Retrying a write that is not safe to repeat

A timeout tells you nothing about whether the server applied the write, so a blind retry of a create or a charge can duplicate it. Either make the operation idempotent, with a caller-supplied key the server deduplicates on or a conditional update, or do not retry it; and use exponential backoff with jitter so the retries of many clients do not synchronise into a second outage.

Naming no test cases at all

State what you would test before being asked: empty input, a single element, all elements equal, the maximum permitted size, and the input that exercises the branch you just wrote. It costs thirty seconds and is much of what separates someone who has shipped code from someone who has only solved puzzles.

Questions & practice

Find peak concurrent attempts to size a pre-warm

Medium · intervals · difference array · capacity planning

You have one district's attempt rows for a single school day: started_at, and an end instant taken as submitted_at when present and server_deadline_at otherwise. There are up to 50,000,000 rows, all inside one local calendar day, and the district's org.time_zone is known. Compute the maximum number of simultaneously in-progress attempts and the second at which it occurs, so capacity can be pre-warmed against the bell schedule. A sort-based sweep is acceptable but is not the best answer here. State your bounds and what happens on a day with a daylight-saving transition.

Approach

Note the structural fact that decides the algorithm: the output domain is one day at one-second resolution, so there are on the order of 86,400 distinct answer positions while there are 50,000,000 inputs. That inverts the usual sweep, because the coordinate space is far smaller than the data.
Allocate a difference array over seconds from local midnight, with one sentinel slot past the end so the decrement for an attempt ending in the final bucket has somewhere to land. For each attempt add 1 at the bucket containing started_at and subtract 1 at the bucket after the one containing the end instant, then prefix-sum once and take the argmax. That is O(N + T) time and O(T) space, which is 86,401 int32 values, about 346 KB — small enough to stay in L2 and to run per section if you want.
Say why this beats the sweep-line answer here. Sorting 2N endpoints is O(N log N) with 100,000,000 entries to materialise; the difference array touches each input twice and never sorts. The sweep only wins when the coordinate space is large or unbounded, which is exactly the condition this problem does not have.
State the bias the bucketing introduces rather than hiding it. Every attempt is counted in full in every second it touches at all, so the bucketed maximum is greater than or equal to the true instantaneous maximum, never less, which is the safe direction for pre-warming. The overcount is not confined to sub-second attempts: two hour-long attempts that merely share a boundary second without ever overlapping, one ending at 10:00:05.2 and the next starting at 10:00:05.8, already put 2 in that bucket against a true instantaneous maximum of 1. The bound that does hold is per bucket. The attempts touching second s split into those that span it entirely, which by definition coexist at every instant of s, and those with an endpoint inside it, so the overcount at s is at most the number of attempts that begin or end within that second. Equality with an exact sweep is guaranteed when no attempt begins or ends inside the peak second — which holds, for instance, on a fixture whose every endpoint lands exactly on a second boundary.
Handle the calendar honestly, and fix the indexing convention before sizing anything. Index buckets by elapsed seconds from the UTC instant of local midnight and size the array from the real UTC span between successive local midnights: in a zone that shifts by an hour that span is 82,800 seconds on the spring-forward day and 90,000 on the fall-back day, not 86,400. A hardcoded 86,400 therefore overruns on the fall-back day, whose last hour indexes up to 89,999, and merely leaves 3,600 dead slots at the tail on the spring-forward day, which is harmless. The opposite convention fails the opposite way: bucketing by local wall-clock seconds-since-midnight always stays in range, but maps the fall-back day's repeated hour onto buckets that already hold the first pass through it, folding two real hours of load into one and understating the peak.
For per-section peaks, do not allocate T buckets per section — 10,000 sections is 3.5 GB. Partition the input by section_id and reuse one array, or keep only sections whose total attempt count clears a threshold, since the rest cannot produce a peak worth pre-warming for.

Follow-up

Now compute the peak across three districts in different time zones on one shared cluster. What is the coordinate space and does your answer survive?
Some attempts have neither submitted_at nor server_deadline_at because they were abandoned. What end do you pick, and how does the choice bias the number you hand to capacity planning?
You need the top ten peak minutes rather than the single peak. What changes, and what does not?

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.

Worked solution (20 min)

Draw the four-state transition table on paper, including what each state does on comma, quote, CR, LF, EOF and any other byte, before writing a line of code.
Implement it over fixed 1 MB blocks, carrying the in-progress field and the in-progress record across block boundaries.
Build a fixture file containing a field with an embedded comma, a field with an embedded CRLF, a field containing a doubled quote, a leading BOM, and a final record cut off mid-quote.
Assert the parser yields exactly the four good records and then raises on the fifth, naming the byte offset.
Re-run with the block size set to 7 bytes to prove no record depends on a field fitting inside one read.

Expected result: Four records with the embedded comma, embedded CRLF and single literal quote preserved exactly, the first header name free of the BOM, and a raised error identifying the unterminated quoted field with its offset rather than a fifth partial record.

Verify it yourself

Output is identical at block sizes of 7 bytes and 1 MB.
The record count matches a reference RFC 4180 parser over the same fixture.
A file with one stray quote near the start raises within a bounded number of bytes instead of allocating until the process dies.
Every yielded record has the same field count as the header, or the reader says which record did not.

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?

Backfill frozen lateness without converting a hundred million times

Hard · cardinality reduction · time zones · batched backfill

A backfill must set due_at_utc on 1,000,000 assignment rows where it is NULL, from due_at_local plus policy_time_zone, and then set is_late on the 100,000,000 attempt rows beneath them whose is_late is NULL, by comparing submitted_at to that instant. Converting one local time per attempt row is correct and far too slow. Give a plan that is both faster and reproducible when re-run months later, state the speed-up you expect, and define the result for a local due time that is ambiguous or nonexistent across a daylight-saving transition.

Approach

Name why the naive plan is slow rather than asserting it. A local-to-UTC conversion is a binary search over that zone's transition table plus an object construction, on the order of one to three microseconds in a managed runtime; 100,000,000 of them is a few minutes of pure conversion and, once per-row fetch and allocation are included, hours. Nothing about it is wrong — it is the row count multiplied by a cost that did not have to be paid per row.
Collapse the cardinality. The conversion depends only on (policy_time_zone, due_at_local), and due dates cluster hard on end-of-day instants for school days, so the distinct pair count is on the order of 10,000 against 100,000,000 attempts. Convert the distinct pairs once into a small mapping, then resolve each attempt with a hash probe. That is roughly a 10,000-fold reduction in conversions, and the per-row cost drops from a conversion to a lookup.
Do it in two stages so the second one is a join, not a computation: materialise due_at_utc on the 1,000,000 assignment rows from the mapping, then set is_late on attempts by joining on assignment_id. The attempt stage then performs no time-zone work at all, which is also what makes it re-runnable without the tz database on the path.
Pin reproducibility explicitly. The local-to-UTC mapping is a function of the zone, the local time, and the time-zone database version, because releases correct historical transitions. Record the tzdata version on the backfill run, or a re-run months later can produce a different instant for the same row and a grade will move with no grade event behind it.
Define the two degenerate cases instead of letting a library default decide. An ambiguous local time in a repeated hour maps to two instants; a nonexistent one in a skipped hour maps to none. Pick the rule — the later instant when ambiguous, the first instant after the gap when nonexistent — encode it as an explicit flag rather than relying on a fold default, and assert both in tests. An 11:59 PM due time is outside the affected hour for most zones, so this is rare, which is exactly why it will not be noticed when it is wrong.
Respect the freeze. Update only rows where is_late IS NULL; a row that already carries the flag was decided at submit and must not be recomputed, or the backfill becomes a retroactive regrade. Then chunk the update by primary-key range with a bounded transaction size, because one 100,000,000-row statement holds locks and generates dead tuples for the whole run and cannot be resumed after a failure.

Follow-up

A district is moved to a different school with a different time zone mid-term. Which rows, if any, change, and what does the freeze rule say?
The tzdata version on the backfill host differs from the one in the application image. How would you have found that out before the run rather than after?
late_policy is block for some assignments, so a late submission should not exist. What do you write for an attempt that submitted after the due instant under that policy?

Enforce an attempt cap that two browser tabs cannot defeat

Medium · isolation levels · phantom reads · idempotency

attempt carries UNIQUE (tenant_id, assignment_id, person_id, attempt_no), and assignment.max_attempts SMALLINT. Starting an attempt today reads the learner's existing attempts, computes the next attempt_no, and inserts a row with server_deadline_at = now() + time_limit_seconds. A learner clicks Start in two tabs five milliseconds apart under READ COMMITTED. Give the statements each transaction runs, name the anomaly precisely, and give two fixes: one that changes isolation level and one that is a single conditional statement. Say what the losing path must do about server_deadline_at.

Approach

Trace the interleaving. Under READ COMMITTED each statement takes a fresh snapshot, and both SELECTs run before either INSERT commits, so both transactions see two existing attempts and both compute attempt_no = 3. The unique index rejects one with SQLSTATE 23505, which feels like the cap held; then the retry re-reads, computes 4, and inserts it. The cap is a predicate over rows that do not exist yet — a phantom — and uniqueness on a different key cannot enforce it.
Isolation fix: run the whole start at SERIALIZABLE. Postgres's serializable snapshot isolation takes predicate locks over what the count read and detects the read-write conflict when both transactions insert into that range, aborting one with SQLSTATE 40001. The preconditions matter: every transaction touching that predicate must also be SERIALIZABLE, and the caller needs a retry loop, which is affordable here because the conflict scope is one learner on one assignment.
Single-statement fix, and the cheaper one: put the counter on a row and make the check part of the write. UPDATE attempt_allocation SET attempts_used = attempts_used + 1 WHERE tenant_id = $1 AND assignment_id = $2 AND person_id = $3 AND attempts_used < max_attempts RETURNING attempts_used — zero rows means the cap is reached. Under READ COMMITTED the blocked second UPDATE re-evaluates its WHERE against the updated row after the lock is released, so it correctly returns nothing; the identical statement under REPEATABLE READ raises 40001 instead of re-evaluating, so the retry loop is mandatory there and unnecessary here.
Handle the double click as what it is — one intent — with idempotency rather than concurrency control: accept a client-supplied start key and add UNIQUE (tenant_id, assignment_id, person_id, start_key). The second tab conflicts, the handler returns the existing attempt, and the cap never sees two requests at all. Concurrency control is the backstop for genuinely distinct starts.
Freeze server_deadline_at at the winning insert and make the losing path return the winner's stored value. Recomputing now() + time_limit_seconds on the loser, or on any subsequent resume, hands the learner a fresh clock on every reload, which is the precise mechanism by which a timed assessment leaks time.
Say what you would not do: SELECT ... FOR UPDATE on the assignment row serialises every learner in the section behind a single row at the bell, converting a per-learner conflict into a section-wide queue at exactly the wrong moment.

Worked solution (25 min)

Reproduce with two sessions interleaved by hand under READ COMMITTED and record that a cap of 3 yields 4 rows after the retry.
Re-run both sessions at SERIALIZABLE and observe one abort with 40001 rather than a duplicate-key error.
Replace the read-then-insert with the conditional UPDATE ... WHERE attempts_used < max_attempts RETURNING, and re-run under READ COMMITTED.
Add the start_key unique constraint and fire fifty identical start requests, asserting one attempt row and fifty identical responses.

Expected result: Exactly max_attempts rows per (assignment, person) under every interleaving. The conditional UPDATE returns zero rows once the cap is reached, the double click consumes one attempt, and every response carries the same server_deadline_at.

Verify it yourself

A concurrency harness at 50 threads never produces attempt_no greater than max_attempts.
Under READ COMMITTED the second conditional UPDATE returns zero rows; switching that same test to REPEATABLE READ produces 40001 instead, which confirms the re-evaluation semantics you relied on.
Every response for one start_key carries a byte-identical server_deadline_at, and reloading does not move it.
No transaction in the test holds a lock on the assignment row, so two learners in the same section start concurrently without waiting.

Follow-up

A grader voids attempt 2. Does the cap free a slot, and what does the next attempt_no become?
How many times do you retry on 40001, with what backoff, and what does the learner see while it retries?
The allocation row is now a second write on the hottest path of the day. At what section size does it become the contended thing, and what would you do then?

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?

Explain a scenario where you had to choose between two competing technical solutions.

Medium

Approach

Work from the requirement backwards to the design.
Clarify what is being asked and what a complete answer contains.
Say what you would check first and why it is the highest-information step.

Follow-up

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

Describe a complex project you led and the technical hurdles you overcame.

Medium

Approach

Clarify what is being asked and what a complete answer contains.
Work from the requirement backwards to the design.
Say what you would check first and why it is the highest-information step.

Follow-up

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

Publish grades outward exactly once under retries and regrades

Medium · outbox · idempotency · ordering · retries

score_publication holds publication_id, tenant_id, attempt_id, target (lti_ags, webhook, sis_export), idempotency_key with UNIQUE (tenant_id, target, idempotency_key), score_given, score_maximum, score_timestamp, state (pending, in_flight, succeeded, failed_retryable, dead), attempt_count and next_attempt_at. The receiving endpoints are third-party: they rate-limit, they time out after having committed, and they can be down for days. Design the relay: how a row is claimed, what happens when the process dies after the HTTPS call but before the row is marked, how the idempotency key is derived, what stops a stale retry from overwriting a regrade, and which rows a claim is allowed to select. next_attempt_at is the only time column on the row; say what it means in each state.

Approach

Write the score row and the publication rows in one transaction. That is the entire point of an outbox: a queue publish after the commit can be lost, and a queue publish before the commit can announce a transaction that then rolls back. There must be no window in which a grade exists with no recorded intent to deliver it.
Claim with a single conditional statement rather than a read then a write, and let next_attempt_at carry two meanings: the backoff time for a row waiting to be sent, and the lease deadline for a row already out. UPDATE score_publication p SET state='in_flight', attempt_count = attempt_count + 1, next_attempt_at = now() + :lease FROM (SELECT publication_id FROM score_publication WHERE state IN ('pending','failed_retryable','in_flight') AND next_attempt_at <= now() ORDER BY next_attempt_at FOR UPDATE SKIP LOCKED LIMIT :n) c WHERE p.publication_id = c.publication_id RETURNING p.*. Both halves are load-bearing. Drop in_flight from the state list and that state becomes a one-way door: a row a process marked in_flight before dying matches no claim again, stalls permanently, and holds its tenant's backlog behind it. Drop the lease write and the row is re-claimable the instant it is claimed, because its next_attempt_at is already in the past, so every publication goes out once per polling worker. SKIP LOCKED is what lets N relay processes share one table without contending on the same head rows; the lease is what makes a crash recoverable. They are separate mechanisms and the design needs both. Index for the predicate with a partial index on next_attempt_at restricted to those three states, or the ORDER BY degrades into a scan over every succeeded row the table has ever accumulated.
Accept that the transport is at-least-once and make the receiver's side idempotent instead of chasing exactly-once on the sender. A process that dies after the call and before the mark leaves the row in_flight with a lease deadline now in the past, and the next claim picks it up and re-sends it. Size :lease above the HTTP timeout plus a margin: a lease shorter than the call reclaims a request that is still running and sends the same grade twice concurrently. That duplicate is harmless only if the key is derived from (attempt_id, score revision) and nothing else. A UUID minted per retry, or a key containing a clock reading, makes every retry a fresh write at the receiver and turns one grade into several. Increment attempt_count at claim time rather than at completion, so a publication that kills its worker on every attempt walks its own counter to the cap and reaches dead, rather than being reclaimed forever.
Treat ordering as a separate failure from duplication. A retry of an old score can land after a regrade's newer score, so score_timestamp must strictly increase per line item and learner; the AGS profile directs receivers to reject a non-increasing timestamp with a conflict, but a sender cannot assume every implementation honours it. Also suppress superseded rows at claim time, so a pending publication that is no longer the current revision for that attempt and target is retired rather than sent.
Classify responses into distinct actions instead of one retry rule. A timeout is an unknown outcome and must be retried with the same key. A conflict on a stale timestamp is success-equivalent and should be marked, not retried. A rate-limit response honours a retry hint when present and otherwise backs off exponentially with jitter under a per-tenant concurrency cap. An expired credential is retryable only after refresh. A permanent rejection, such as a line item that no longer exists, is terminal and moves to dead with last_error_code recorded, because a row retrying forever looks identical to a healthy system on every dashboard.

Worked solution (35 min)

Write the transaction that inserts the score and its publication rows together, and state precisely what is lost if the publish happens outside it.
Write the skip-locked claim, name every state its predicate selects, set the next lease deadline in that same statement, then trace a process that dies immediately after the HTTPS call through to the claim that picks the row up again.
Derive the idempotency key from named columns and show two separate retries producing an identical key.
Build the response classification table: timeout, stale-timestamp conflict, rate limit with and without a hint, credential expiry, line item gone, and server error, with the resulting state and next_attempt_at for each.
Trace a regrade racing a stale retry and show which score the receiver ends up holding.

Expected result: Score and outbox rows committed together; a skip-locked claim whose predicate includes in_flight rows whose lease has expired, writing the next lease deadline in the same statement and sizing it above the HTTP timeout; an idempotency key derived from attempt id and score revision; a strictly increasing ordering token plus suppression of superseded pending rows; and a classified response table where only unknown or transient outcomes retry while everything else reaches a terminal state.

Verify it yourself

Kill the relay at each of three points (after claim, after send, after mark) and confirm the receiver ends with the same grade in all three, and that the killed-after-send row is re-selected by your claim predicate instead of sitting in_flight forever.
Confirm two retries of one publication produce byte-identical idempotency keys.
Confirm a stale retry landing after a regrade cannot leave the receiver holding the older score.

Follow-up

A district's endpoint is down for four days and 200,000 publications accumulate. What happens when it returns, and what protects it from your entire backlog arriving at once?
An instructor regrades one attempt three times inside a minute. How many outbound calls does the receiver see, and which score does it hold at the end?
What does a support engineer see for a stuck tenant, and what exactly does a safe manual replay of a dead row do?

Roster apply deadlocks against launches on term-start mornings

Hard · deadlock · lock contention · transaction chunking · lock ordering

In the first week of term, between 07:45 and 08:15, deadlock errors (SQLSTATE 40P01) appear at 20-60 per minute, two roster runs abort with roster_sync_run.error set, some launches return 500, and read p99 doubles even in minutes with no deadlock. A 10^6-row roster apply started at 02:00 and is still running inside one transaction. The apply updates section metadata first and then that section's enrollment rows in file order; the launch path upserts an lti_launch enrollment and then bumps a counter on section. Give an ordered diagnostic checklist, the cycle, and the fix.

Approach

Order the checklist to separate the two symptoms, because they have different causes and only one is the deadlock. First, pull the deadlock detail from the server log, which names both transactions, both statements and the locks each held and wanted; that is the cycle, printed for you. Second, explain the p99 rise in the minutes with no deadlock at all, which the cycle does not cover. Third, look at transaction duration and lock hold time. Fourth, look at scheduling.
Read the cycle off the two orders: the apply takes section then enrollment; the launch takes enrollment then section. That is a two-resource cycle in opposite order, so any overlap can deadlock. Postgres detects it after a lock wait exceeds deadlock_timeout, default 1 s, and cancels one transaction with 40P01, which is why the errors are capped at a few dozen a minute rather than unbounded.
Explain the second symptom separately: a single transaction over 10^6 enrollment rows holds every row lock it has taken until it commits, because row locks are never released early. Any launch touching those sections waits, and the wait is real even when no cycle forms. That is the doubled p99, and it would exist with perfect lock ordering.
Fix the cycle by removing an edge rather than by reordering both sides. The launch path bumps a counter on section, which is derived data on a latency-critical path; move it to an async aggregate or a cache. With the launch path no longer writing section, the cycle cannot form regardless of the apply's order. Where two writers genuinely must touch both, fix a canonical acquisition order, and have the apply sort its batch by (section_id, person_id) before writing so file order cannot dictate lock order.
Fix the contention by chunking the apply into bounded transactions, a few thousand rows each, committing between chunks. Lock hold time drops from hours to well under a second, an abort loses one chunk instead of a night, and the run stays restartable from the snapshot digest with per-chunk progress recorded. Retry 40P01 at chunk granularity with jittered backoff, which is safe precisely because the apply is idempotent on the digest. Chunked commits must not weaken the destructive-change gate: it is decided on the full diff before the first chunk commits, never per chunk, so a partially applied snapshot cannot deprovision a bounded fraction of a tenant ahead of the decision.
Fix the schedule last, as defence rather than as the remedy: cap per-tenant apply concurrency and hold the apply out of the tenant's bell windows from the term calendar. Scheduling alone would hide the bug until a large tenant overran its window again, which is exactly what happened here.

Follow-up

A chunked apply is no longer atomic. What can a reader observe mid-run, and which of those intermediate states are acceptable?
Chunk 412 of 800 fails and the run restarts. Prove the restart neither double-applies nor skips rows.
Raising deadlock_timeout would reduce the error count. Explain precisely what it would make worse.

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.

'We rewrote it in a better framework' is not a story

A migration is a cost you chose to pay, not an achievement. The story is what the old system made expensive, what you measured before committing, what kept serving traffic during the cutover, and what you would have done if the numbers had come back flat. Without those, a rewrite reads as taste.

How do you handle feedback or constructive criticism during a code review?

Medium · behavioural and engineering judgement

Approach

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

Follow-up

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

How do you handle production issues or performance bottlenecks in your current stack?

Medium · behavioural and engineering judgement

Approach

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.
Pick a story where you made the decision, not one where you watched it.

Follow-up

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

What do you know about Academy Sports + Outdoors and our market position?

Medium · behavioural and engineering judgement

Approach

State the situation in two sentences and spend the rest on the reasoning.
Pick a story where you made the decision, not one where you watched it.
Name the disagreement and how you resolved it with evidence.

Follow-up

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

How does your past experience specifically relate to the requirements of this role?

Medium · behavioural and engineering judgement

Approach

Name the disagreement and how you resolved it with evidence.
Close with what you would do differently, concretely.
Pick a story where you made the decision, not one where you watched it.

Follow-up

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