cswork / libraryOpen practice workspace

Interview guides · Amherst Holdings

Amherst Holdings Software Engineer Interview Guide

Amherst Holdings Software Engineer interview guide: recruiter screen, verbal technical phone screen, panel, plus C#, SQL and Salesforce prep.

The 60-second brief

Candidates report that Amherst Holdings operates in real estate investment and financial services. The Software Engineer openings they describe fall into three tracks: web development (C#, Node.js, JavaScript and React), data warehousing and SQL, and enterprise work on Salesforce. Product Managers are among the people candidates meet in the panel.

This guide is for Software Engineer candidates, described as mid-to-senior, interviewing at Amherst Holdings on any of the three tracks candidates mention: web (C#, Node.js, JavaScript, React), data warehousing and SQL, or enterprise Salesforce. It covers each reported round, the technical questions reported for the loop, the behavioral and resume questions, and a seven-day plan built around speaking your answers aloud.

Candidates report three rounds over roughly 3-5 weeks: a Recruiter Screen, a Technical Phone Screen with several developer leads, and a Panel Interview with a mix of stakeholders. The phone stages are described as verbal, with no shared screen or compiler.

Focus areas

  • RESTful microservices
  • C#
  • LINQ

Overview

The reported questions split into four groups. Web questions ask for exact C# LINQ filtering, React data-loading lifecycle calls, the ground-up design of a RESTful microservice and a view on serverless infrastructure. Data questions ask for an advanced SQL construct and how it works internally, a financial data warehouse schema, accounting principles from a data angle, and tuning a slow query on a massive dataset. Salesforce questions cover lookup versus master-detail relationships, formula versus roll-up summary fields, and Flow versus Apex triggers. The last group is behavioral: your first job after college, conflict with a Product Manager over timelines, strict Agile, and balancing autonomy with collaboration.

Which of those groups dominates depends on the team. Candidates say the focus moves between web development, data warehousing and enterprise Salesforce, so the first useful step is to find out your track from the recruiter and weight your preparation accordingly. SQL questions are reported for every track, so it is the safest area to over-prepare.

Two features of the loop shape how you should practise. First, candidates describe the phone stages as verbal whiteboarding: you explain code and syntax aloud with no shared editor. Reading solutions silently does not build that skill; saying the full LINQ expression, the useEffect call or the window-function clause out loud does. Second, candidates report detailed questions about jobs held years ago, which means a written timeline of every role and its tools is part of the preparation, not an afterthought.

Because the questions are set in financial data, practise answers that cover money handling: exact decimal types rather than floating point, append-only ledger-style records with corrections posted as new entries, reproducible reports for a closed period, and clear grain in every fact table. Candidates also report team leads asking basic Linux commands (cat, grep, awk) as technical trivia, so keep a few one-liners ready.

The Amherst Holdings interview process

Candidates describe three stages, with gaps of one to three weeks between them. The descriptions below separate what is reported from how to prepare for each stage.

Recruiter Screen

Candidates describe the first stage as a recruiter conversation that combines behavioral questions with a walk through your resume. Because later stages are reported to return to older jobs in detail, use this call to rehearse a clean role-by-role account. It is also a good moment to ask which team track the opening sits on (web, data warehousing or Salesforce), since candidates say the technical focus changes with the team, and what the following stages cover.

What to demonstrate

  • Your career narrative, role by role, as it appears on your resume
  • Behavioral basics, since the stage is described as a behavioral and resume screen

How to prepare

  • Write a timeline of every role with dates, team, title and the daily tools, including your first job, so the early-career question has a ready answer.
  • Prepare short stories for the Product Manager conflict question and the autonomy-versus-collaboration question; both are reported behavioral topics.
  • Ask the recruiter which track the role belongs to (web, data warehouse or Salesforce) and plan the rest of the week around it.

Technical Phone Screen

Candidates describe a long technical phone interview run by several developer leads, with verbal whiteboarding: you state your exact solution and syntax aloud without a shared screen or compiler. Reported example formats are writing a LINQ query and explaining React component logic, and candidates also mention explaining SQL verbally. Prepare by speaking complete answers, naming the actual methods, keywords and clauses rather than describing them loosely.

What to demonstrate

  • Working command of language syntax when spoken instead of typed (C#, LINQ, React, SQL)
  • Understanding of how the framework or database behaves underneath the syntax

How to prepare

  • Say aloud, with no editor open, a LINQ filter on a collection and the React data-loading flow, until both come out in full sentences with exact method names.
  • Pick one advanced SQL construct you have used (a window function, a recursive CTE, a lateral join) and practise explaining aloud what the engine does with it, including how you would read the execution plan.
  • Rehearse a spoken slow-query walk-through on a large financial table: read the plan, check indexes and row estimates, reduce the data scanned, then consider partitioning or pre-aggregation.

Panel Interview

Candidates describe a long panel made of separate sessions: Product Managers on agile methods and conflict resolution, developers on big-picture application design, team leads on architecture and server tools, and a VP of Engineering or CTO covering behavioral and personality alignment. Candidates also report team leads including basic Linux commands in their technical trivia. Candidates do not say which of the reported technical questions come up in which session. Prepare one design you can walk end to end and a set of behavioral stories that you can tell in a clear sequence.

What to demonstrate

  • Application and architecture design reasoning, from API shape to data storage
  • Server tools, including basic Linux commands raised by team leads
  • Collaboration with Product Managers, including handling disagreements over timelines
  • Behavioral alignment shown through stories about autonomy, teamwork and Agile practice

How to prepare

  • Rehearse designing a RESTful microservice from nothing: resources and verbs, versioning, validation, idempotent writes, error format, persistence, auth and observability.
  • Sketch a financial warehouse schema with a clear grain, fact tables, dimensions and an append-only treatment for corrections, or on the enterprise track the Salesforce decision rules (lookup vs master-detail, formula vs roll-up summary, Flow vs Apex).
  • Memorise a handful of cat, grep and awk one-liners for filtering lines and summing columns in a log or CSV.
  • Have three behavioral stories ready: Product Manager timeline conflict, strict Agile, and autonomy versus collaboration.

Where candidates lose points

Describing LINQ, React or SQL loosely instead of giving the exact code, when the phone stage is verbal

Practise saying the full answer aloud with no editor: the Where call with a lambda testing both first names (or Contains on a small array), where the component fetches inside useEffect, and the exact SQL clause. Run each answer until you can say method and keyword names without hesitation.

Vague recall of early jobs when asked for the applications and tools you used

Build a written timeline of every role with dates, team and daily tools before the recruiter screen. Candidates report detailed questions about roles held years ago, so review the older entries as carefully as the recent ones.

Answering the slow-query question by listing indexes without reading the execution plan

Open with the plan: check scan types, row estimates against actuals, joins and sorts that spill. Then name the fix that matches the finding (a covering or composite index, narrower predicates, partition pruning, pre-aggregation) and the way you would confirm the improvement.

Treating financial data like any other data: floating-point amounts, updates in place, and a warehouse fact table with an unclear grain

Use exact decimal types, state the grain in one sentence, keep ledger-style entries append-only with corrections posted as new rows, and know why debits equal credits. These show you understand the domain candidates say comes up.

Naming Salesforce features without the rule for choosing between them

For each pair, state the deciding property: master-detail ties child to parent for ownership, cascade delete and roll-up summaries, while lookup is looser and optional; formula fields compute on read while roll-ups aggregate child records; Flow suits declarative automation while Apex handles logic Flow cannot express cleanly.

Questions & practice

In C#, using LINQ, how would you retrieve a list of customers with the first name "Bill" or "Steve" from an ar

Medium · Web Development & Architecture

In C#, using LINQ, how would you retrieve a list of customers with the first name "Bill" or "Steve" from an array?

Approach

  1. Say the method syntax out loud precisely: customers.Where(c => c.FirstName == "Bill" || c.FirstName == "Steve").ToList(), then give the query-syntax equivalent: from c in customers where c.FirstName == "Bill" || c.FirstName == "Steve" select c.
  2. Explain deferred execution: Where returns a lazy IEnumerable<Customer> that does nothing until enumerated, so ToList() or ToArray() materialises it once; enumerating the lazy query twice repeats the filter.
  3. Give the scalable version: put the names in a HashSet<string> and filter with names.Contains(c.FirstName), which keeps the scan O(n) with O(1) membership checks however many names are added.
  4. Cover the edge cases: string == is null-safe, so a null FirstName is simply excluded; for case-insensitive matching use string.Equals(c.FirstName, "Bill", StringComparison.OrdinalIgnoreCase) or a HashSet built with StringComparer.OrdinalIgnoreCase.
  5. Mention the non-LINQ alternative for an array, Array.FindAll(customers, predicate), and that Select can project just the fields needed (for example c => c.Id).

Follow-up

  • If customers were an Entity Framework IQueryable instead of an array, what SQL would names.Contains(c.FirstName) produce, and would the OrdinalIgnoreCase comparer still apply?
  • How would you return the matches ordered by last name and grouped by first name?
  • What happens if the source array is modified between building the query and calling ToList()?

Find overlapping job attempts and peak concurrency from lease records

Medium · sweep line · intervals · leases

A day of job_run history yields about 50,000,000 attempt records: (job_run_id, job_type, attempt, started_at, finished_at which is NULL when the worker died, lease_expires_at). Leases expire on a clock, so a job that outran its lease ran twice. Produce (a) every job_run_id whose attempts overlapped in wall-clock time and (b) the peak number of simultaneously running attempts per job_type with the minute it occurred. Target O(n log n). State how you treat a NULL finished_at and what clock skew does to your answer.

Approach

  1. Define the interval before sorting anything: an attempt occupies [started_at, COALESCE(finished_at, lease_expires_at)). finished_at is observed and lease_expires_at is only a promise, so every attempt without a finish contributes an estimate and the whole result is a lower bound on overlap rather than an exact count.
  2. For peak concurrency, sweep: emit 2n endpoints, sort by (timestamp, kind) with ends ordered before starts at equal timestamps, then walk the sequence maintaining a counter per job_type and record each type's maximum with its timestamp. O(n log n) dominated by the sort, O(n) space, or O(1) extra if the sort is external and the walk streams.
  3. For overlap detection, do not compare attempts pairwise. A single global sort by (job_run_id, started_at) gives both the grouping and the order; within a group, keep the maximum end seen so far and report an overlap exactly when the next start is less than that running maximum, which is one linear pass after the sort.
  4. Half-open intervals matter and are easy to get wrong: with closed intervals an attempt ending at the same millisecond another begins reads as concurrency two, and across 50,000,000 records that artefact swamps the real signal.

Follow-up

  • A handler is not idempotent and you have found 400 overlapping jobs. Which of them actually caused damage, and what would you query to find out?
  • Peak concurrency for one job_type is 4 against a configured cap of 4. Is the cap working, or is the data hiding attempts that never started?

Archive a resource graph without breaking live references or recursing

Medium · graph traversal · topological order · tenant isolation

Resources reference other resources within a tenant; for the largest tenant the reference table holds up to 2,000,000 nodes and 8,000,000 edges. Archiving a resource must archive everything reachable from it that nothing outside the set still references, refuse when a live external referrer exists, and terminate when references form cycles, which they legitimately do. Produce the archive order and the refusal list, targeting O(V+E). Say what stops the traversal crossing a tenant boundary, and why recursion is the wrong control structure at this size.

Approach

  1. Load the subgraph with the tenant predicate on both endpoints of the edge, not only on the side you started from. Scoping the left table alone is the classic cross-tenant leak: one mis-entered edge then pulls another tenant's resources into the traversal and, worse, into the archive.
  2. Traverse iteratively with an explicit stack. A 2,000,000-node graph can hold a chain deep enough to exhaust a native stack in the low tens of thousands of frames, and that failure is a process crash rather than an error you can return.
  3. Treat cycles as data rather than corruption: compute strongly connected components with Tarjan in O(V+E) using its own explicit stack, then condense. The condensation is a DAG, so a topological order over it gives the archive order, and every member of a component archives in one transaction because no order within a cycle is valid.
  4. Decide refusals with reverse edges. A candidate is archivable only if every in-edge originates inside the candidate set, so build the transpose or count in-degrees restricted to the visited set, and emit each blocked resource with the id of the external referrer, which is the only part of the answer an operator can act on.

Follow-up

  • The graph is read in one query and the archive writes a minute later. What can change in between, and how do you make the write safe?
  • The candidate set is 400,000 resources. Is that one transaction, and if not, what does a half-finished archive look like to a reader?

Explain a specific, advanced SQL construct you have used recently. How does it work under the hood?

Medium · Database & Data Engineering

Approach

  1. Pick one construct you have really used and name it precisely, for example a running balance with SUM(amount) OVER (PARTITION BY account_id ORDER BY posted_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), or ROW_NUMBER() to keep the latest row per account.
  2. Explain the mechanics: the engine sorts rows by the partition and order keys (or reads them in order from a matching index), then streams through each partition computing the value without collapsing rows, unlike GROUP BY; in SQL Server the plan shows Segment and Sequence Project for ROW_NUMBER and a Window Spool or batch-mode Window Aggregate for running sums.
  3. State the evaluation order: window functions are computed after WHERE, GROUP BY and HAVING, so to filter on ROW_NUMBER() = 1 you wrap the query in a CTE or subquery.
  4. Name the frame trap: with ORDER BY and no frame clause the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so rows with the same posted_at are peers and get the same running total; ROWS gives a strict row-by-row balance.
  5. Give the index that removes the sort, a composite index on (account_id, posted_at) including amount. If a CTE comes up, state the engine rules: SQL Server inlines non-recursive CTEs; PostgreSQL 12+ inlines a CTE only when it is non-recursive, side-effect free and referenced once, materializes one referenced more than once unless marked NOT MATERIALIZED, and MATERIALIZED forces materialization.

Follow-up

  • How does a recursive CTE execute, and how do you stop it looping on cyclic data?
  • Compare ROW_NUMBER, RANK and DENSE_RANK on tied values.
  • How else could you get the latest transaction per account, and which index would each approach need?

Walk me through how you would optimize a slow-running query on a massive financial dataset.

Medium · Database & Data Engineering

Approach

  1. Reproduce first with the real parameters, then read the actual plan (SQL Server actual execution plan with SET STATISTICS IO, TIME ON; PostgreSQL EXPLAIN (ANALYZE, BUFFERS)) instead of guessing.
  2. Compare estimated and actual row counts: a large gap points to stale statistics or parameter sniffing; then look for full scans of the big table, key lookups repeated per row, and sorts or hash joins spilling to tempdb or disk.
  3. Check sargability: rewrite WHERE YEAR(posted_at) = 2024 as posted_at >= '2024-01-01' AND posted_at < '2025-01-01', avoid leading-wildcard LIKE, and fix implicit conversions such as an NVARCHAR parameter compared with a VARCHAR column.
  4. Fix with the cheapest effective change: a composite index with equality columns first and the range column last, INCLUDE columns to make it covering, and acknowledge the write and storage cost; for very large history, date partitioning for partition elimination, a columnstore index or a pre-aggregated summary table.
  5. Verify the result: same output rows, lower logical reads and duration on production-like data, and a check that the new index does not slow the load jobs that write the table.

Follow-up

  • The query ran fast yesterday and is slow today with no code change: what do you check?
  • When would you choose not to add an index even though it speeds the query up?
  • How do you keep a heavy reporting query from blocking transactional writes on the same database?

In React, what specific API calls would you make, and at what point in the component lifecycle, to load a comp

Medium · Web Development & Architecture

In React, what specific API calls would you make, and at what point in the component lifecycle, to load a component after data is returned?

Approach

  1. For a function component, fetch inside useEffect(() => { ... }, [id]): the effect runs after React commits the render, so the first render shows a loading state and the data render follows once setState is called with the result.
  2. Hold three pieces of state (data, loading, error) and render conditionally: a spinner while loading, an error message on failure, and the child component only once data exists, e.g. if (!data) return <Spinner />.
  3. Define an async function inside the effect rather than making the effect callback async, because the callback must return either nothing or a cleanup function, not a Promise.
  4. Return a cleanup that calls AbortController.abort() and pass controller.signal to fetch, so a fast change of id or an unmount does not let a stale response overwrite newer state.
  5. For class components, name componentDidMount for the initial fetch and componentDidUpdate with a prevProps comparison for refetching; componentWillMount is deprecated (UNSAFE_) and the constructor should not trigger side effects.

Follow-up

  • Why does the effect run twice in development under StrictMode, and how does your cleanup make that harmless?
  • How would you share and cache this data across several components (React Query or SWR, a context, or a parent fetch)?
  • How does React.lazy with Suspense differ from this pattern when the thing being loaded is the component's code rather than its data?

How do you design a RESTful microservice from the ground up?

Medium · Web Development & Architecture

Approach

  1. Start from the boundary: one service owns one business capability and its own data store, and no other service reads its tables directly; name the resources as nouns, e.g. GET /properties/{id}/leases, POST /leases.
  2. Define the HTTP contract: correct verbs and status codes (201 Created with a Location header, 400/422 for validation, 404, 409 for conflicts), cursor-based pagination on list endpoints, and a versioning scheme (/v1 path or a header) fixed before the first consumer arrives.
  3. Handle retries safely: GET, PUT and DELETE are idempotent by definition, POST is not, so accept an Idempotency-Key header and store the key with the result to return the same response on a replay.
  4. Plan service-to-service calls: timeouts on every outbound call, retries with exponential backoff and jitter only for idempotent operations, and a circuit breaker; publish domain events through a transactional outbox when other services must react to a write.
  5. Finish with operability: authentication (OAuth2/JWT), input validation at the edge, structured logs with a correlation id, metrics and tracing, health and readiness endpoints, and a containerised build deployed through CI (ASP.NET Core or Node.js both fit).

Follow-up

  • How do you keep two services consistent when one business operation writes to both, without a distributed transaction?
  • How would you ship a breaking change to a response shape while existing clients still call the old one?
  • What happens to your service when a downstream dependency starts responding in seconds instead of milliseconds?

How would you design a schema for a financial data warehouse?

Medium · Database & Data Engineering

Approach

  1. Declare the grain before any table: for example one fact row per general-ledger journal line, plus a separate periodic snapshot fact with one row per account per day or month for balances.
  2. Lay out a star schema: transaction fact with additive amounts and foreign keys to conformed dimensions for date, account (with the chart-of-accounts hierarchy), legal entity, counterparty, asset or property, and currency; explain that balances are semi-additive (summable across accounts, never across dates).
  3. Store money as DECIMAL (for example DECIMAL(19,4)), never float; keep the transaction-currency amount, the reporting-currency amount and the FX rate used, so reports can be reproduced.
  4. Track history: SCD Type 2 on dimensions whose attributes change (surrogate key, valid_from, valid_to, is_current), and treat posted facts as immutable with corrections arriving as reversing entries; add an effective date and a recorded date if as-of restatement reporting is needed.
  5. Cover loading and trust: staging, validation, then the dimensional layer; partition the facts by accounting period; reconcile each load to the source ledger (debits equal credits, period totals match) and keep a load batch id and source key on every row.

Follow-up

  • A backdated correction arrives after the period has closed: how does your schema let you report both the original and the restated figure?
  • Why a star schema rather than a normalised 3NF model or a snowflake here, and what do you give up?
  • How would you design the ETL so a failed mid-run load can be retried without double-counting?

What is your familiarity with accounting principles from a data perspective?

Medium · Database & Data Engineering

Approach

  1. Start with double entry: every journal entry has two or more lines whose debits equal credits, so the invariant I enforce in data is that the signed amounts of an entry sum to zero, and the trial balance sums to zero across all accounts.
  2. Name the account types and their normal balances: assets and expenses are debit-normal; liabilities, equity and revenue are credit-normal; the accounting equation is Assets = Liabilities + Equity, which is why the sign convention in a table matters.
  3. Explain accrual versus cash basis as a data problem: revenue is recognised when earned, not when cash arrives, so a record needs a transaction date, a posting date and an accounting period, and reports must say which one they filter on (rent billed but unpaid sits in accounts receivable).
  4. Describe the ledger structure: subledgers such as AR and AP roll up into control accounts in the general ledger, and a reconciliation job checks that each subledger total matches its control account at period end.
  5. Cover period close and corrections: posted entries are never edited; errors are fixed with reversing or adjusting entries in an open period, which keeps the audit trail intact.

Follow-up

  • How would you model journal entries so that an unbalanced entry cannot be posted?
  • A revenue report from the data team and one from finance disagree for the same month: where do you look first?
  • How do you store debits and credits: one signed amount column or separate debit and credit columns, and why?

What is the exact difference between a lookup relationship and a master-detail relationship in Salesforce?

Medium · Enterprise & Salesforce (Role-Specific)

Approach

  1. Master-detail is tight coupling: the detail cannot exist without its master (the field is always required), deleting the master deletes its details, and the detail has no owner of its own because ownership and sharing are inherited from the master.
  2. Lookup is loose coupling: optional by default, each record keeps its own owner and sharing, and on delete of the parent the default is to clear the field, or you can block deletion of a parent that is still referenced.
  3. Roll-up summary fields (COUNT, SUM, MIN, MAX) are available only on the master side of a master-detail; an object can have at most two master-detail relationships, which is how a junction object models many-to-many.
  4. Conversions have rules: a lookup can become master-detail only if every existing child record has the field populated, and a master-detail can become a lookup only after the master's roll-up summary fields are removed; reparenting a detail is off unless 'Allow reparenting' is enabled.
  5. Close with the choice: I use master-detail when the child is meaningless without the parent and needs roll-ups or inherited security, and lookup when the records have independent lifecycles or owners.

Follow-up

  • How would you model a many-to-many relationship, and which master controls the junction record's ownership?
  • You need a SUM of child amounts across a lookup relationship: what are your options?
  • What breaks for existing users if you convert a lookup to master-detail on an object with records owned by different people?

When would you use a formula field versus a roll-up summary field?

Medium · Enterprise & Salesforce (Role-Specific)

Approach

  1. A formula field is read-only and computed when the record is read, not stored; it works on the same record and on parent fields through cross-object references, but it cannot aggregate child records.
  2. A roll-up summary field is stored on the master of a master-detail relationship and computes COUNT, SUM, MIN or MAX over detail records, with optional filter criteria; it recalculates when detail records change.
  3. Choose a formula for per-record derivations such as days overdue with TODAY(), a status label or a parent field shown on the child; choose a roll-up for totals and counts of children such as the sum of line amounts on a parent.
  4. State the limits: roll-ups need master-detail (not lookup), objects have a cap on roll-up fields (25 by default), and a roll-up cannot summarise a formula that uses cross-object references or time-dependent functions like NOW() or TODAY().
  5. Mention side effects: a formula value changing does not update the record, so it fires no triggers or record-triggered flows, while a roll-up recalculation updates the master and can cause row-lock contention when many details under one master are loaded at once.

Follow-up

  • The relationship is a lookup but the business needs a child total on the parent: what do you build instead?
  • Why would a report filtered on a complex formula field be slow, and what could you do about it?
  • How would you show a count of only open child records, and what happens to it when a child's status changes?

Explain a scenario where you would choose to build a Flow instead of writing an Apex trigger.

Medium · Enterprise & Salesforce (Role-Specific)

Approach

  1. Give a concrete Flow case: when a record's status changes to Closed, stamp a closed date and create a follow-up task; a before-save record-triggered flow handles same-record field updates without extra DML, and an after-save flow creates the related record.
  2. Give the Apex case for contrast: logic across several objects with Map-based bulk processing, a callout to an external system, partial-success handling with Database.update(records, false), or complex recursion control are cleaner and testable in an Apex trigger with a handler class.
  3. Name the shared constraint: both run under the same transaction governor limits (100 synchronous SOQL queries, 150 DML statements), and a flow with a Get Records or Update element inside a loop hits them on bulk loads just as unbulkified Apex does.
  4. Cover maintainability and testing: an admin can maintain a flow without a deployment of code, while Apex requires test classes with at least 75% org-wide coverage to deploy but gives precise assertions; Process Builder and Workflow Rules are retired paths, so new declarative automation belongs in Flow.
  5. Mention ordering: before-save flows run before Apex before triggers, so mixing several flows and triggers on one object needs a deliberate order (Flow Trigger Explorer, one trigger per object) to avoid conflicting updates.

Follow-up

  • How would you let a flow use logic that only exists in Apex (an @InvocableMethod)?
  • A data load fails with 'Too many SOQL queries: 101' after a new flow went live: how do you find and fix it?
  • How do you test a record-triggered flow compared with an Apex trigger before deploying to production?

Listing latency scales with page size, not with filters

Easy · n+1 · latency · query batching

The tenant listing endpoint reads resource filtered by tenant_id and status, ordered by updated_at DESC, and returns each row plus the owner's display name from app_user and the actor of that resource's latest resource_revision. p99 is 55 ms at 10 rows per page and 1.4 s at 200. Database telemetry shows 401 statements per request, each under 1 ms, and nothing in the slow-query log. Diagnose the cause and give the fix, stating the statement count per request and the p99 you expect afterwards.

Approach

  1. Read the counters before forming a theory. 401 statements for 200 rows is one driver query plus two per row, and sub-millisecond execution with an empty slow-query log rules out a bad plan. The time is round trips, which is why it is invisible in every per-query metric and scales with rows returned rather than with filter selectivity.
  2. Name the two per-row statements from their normalised text: a single-row app_user lookup by user_id, and a resource_revision lookup by resource_id ordered by version DESC LIMIT 1. Confirm by dropping those two response fields and watching the statement count fall to one. That locates the calls in the serialisation layer, not the repository.
  3. Check that the arithmetic accounts for the whole gap. Measure one round trip to the replica in isolation; 400 trips at roughly 3 ms of network plus 0.2 ms of execution is about 1.3 s on top of a 55 ms baseline, which matches. If the multiplication had fallen short, the N+1 would only be part of the story and you would keep looking.
  4. Batch both lookups. Collect owner_user_ids and resource_ids from the driver query, then issue WHERE tenant_id = $1 AND user_id = ANY($2) for the users, and PostgreSQL's SELECT DISTINCT ON (resource_id) ... WHERE resource_id = ANY($2) ORDER BY resource_id, version DESC for the latest revision, which the UNIQUE (resource_id, version) index serves directly. On an engine without DISTINCT ON, use a lateral join or a row_number window. Three statements per request at any page size.

Follow-up

  • The page size is capped at 200 today. What breaks first if it is raised to 2,000, and is it still this bug?
  • How do you stop the next N+1 from reaching production, given that no individual query is slow and the endpoint's tests pass?

Suggested practice checklist

Seven days built around the three reported rounds: map the loop, drill spoken code, then SQL and design, then Salesforce and domain basics, then behavioral stories, ending with a full verbal run-through.

Day 1 — Map the loop and rebuild your work history

  • Write the three reported stages (Recruiter Screen, Technical Phone Screen, Panel Interview) and the question groups you expect: web, data and SQL, Salesforce, behavioral.
  • Build a role-by-role timeline for every job, with dates, team, title and daily tools, starting with your first job after college.
  • Write a spoken answer for the first-job question using the order: team purpose, tools by category, one thing you built, what carried over.

Deliverable: A one-page loop map, a full job timeline, and a written first-job answer.

Day 2 — Spoken C#, LINQ and React

  • Say aloud, without an editor, the LINQ expression that returns customers named Bill or Steve from an array, then give the Contains variant and explain deferred execution and when ToList runs it.
  • Explain the React flow for loading a component after data returns: initial state, fetching inside useEffect with a dependency array, a loading flag, and cleanup; then compare it with componentDidMount in class components.
  • Record yourself giving both answers and replay them, listing every place you used a vague word instead of a method name.

Deliverable: Two recorded spoken answers and a list of vague spots to fix.

Day 3 — REST microservice design and the serverless trade-off

  • Design a REST microservice end to end on paper: resources and verbs, status codes, error format, versioning, pagination, validation, idempotency keys for writes, persistence and authentication.
  • Write a decision table for serverless: three workloads that suit functions, three that do not, and the reason for each.
  • Prepare a serverless story from your own experience, or an honest description of the closest thing you have used.

Deliverable: A one-page microservice design, a serverless decision table and a written serverless story.

Day 4 — SQL depth and the financial warehouse

  • Pick one advanced SQL construct and write a short explanation of how the engine processes it, with a worked example on a financial table.
  • Write the slow-query walk-through: read the plan, compare estimates with actuals, check index use, cut scanned data, consider partitioning or pre-aggregation, then re-measure.
  • Sketch a financial warehouse schema with a stated grain, fact and dimension tables, and an append-only treatment for corrections.

Deliverable: A SQL construct explanation, a slow-query checklist, a schema sketch and notes from the practice question.

Day 5 — Accounting for data, Salesforce and Linux basics

  • Write a half-page on accounting from a data angle: ledger and double-entry, debits equal credits, period close, and why corrections are new entries rather than edits.
  • Write the deciding rules for lookup versus master-detail, formula versus roll-up summary, and Flow versus Apex trigger, each with a one-line example.
  • Practise five cat, grep and awk one-liners, for example counting matching lines and summing a column.

Deliverable: An accounting summary, three Salesforce decision rules with examples, and five tested command one-liners.

Day 6 — Behavioral stories

  • Write the Product Manager timeline-conflict story with the situation, your estimate, the options you offered and the result.
  • Write the strict Agile answer naming the ceremonies, artifacts and one retrospective change.
  • Write the autonomy-and-collaboration story, marking the decision you made alone and the one you took to others.
  • Tell each story aloud and cut anything that does not carry the decision or the outcome.

Deliverable: Three written stories and a spoken version of each.

Day 7 — Full verbal run-through

  • Run a phone-screen simulation with no editor: LINQ, React, SQL construct and slow query, answered aloud in sequence.
  • Run a panel simulation: microservice design, warehouse schema, a few Linux one-liners, then the behavioral stories in order.
  • List the three weakest answers and rework them once.

Deliverable: One full spoken run of both stages and a short list of reworked answers.

Resume, Agile and stakeholder stories

Candidates report behavioral content throughout the loop: the recruiter screen is partly behavioral, Product Managers in the panel cover agile methods and conflict, and a senior engineering leader covers behavioral and personality alignment. Reported questions cover timeline conflict with product, Agile practice and autonomy versus collaboration, so prepare stories for each. Add detail about your early career, since reported questions return to old jobs.

  1. Give an example of how you balance working autonomously with collaborating with your team.

What is your experience with serverless cloud infrastructures, and when would you choose not to use them?

Medium · Web Development & Architecture

Approach

  1. Open with a factual inventory: which workloads you ran as functions (an HTTP endpoint, a queue consumer, a scheduled job, a file-arrival handler), the runtime, and how you deployed and monitored them. If your experience is limited, say so and describe the nearest thing you built or evaluated; an inflated claim invites questions you cannot answer.
  2. Give your decision rules. Functions suit bursty or low-volume traffic and event-driven glue. Argue against them for steady high throughput (per-invocation billing overtakes dedicated compute), jobs that run past the platform's execution limit, latency-sensitive paths where cold starts hurt, and relational databases with limited connections, since many concurrent instances each open their own.
  3. Add the operational costs: harder local debugging and tracing across many small functions, provider-specific triggers that create lock-in, and the need for idempotent handlers because events can be delivered more than once.
  4. Tie it to a financial-data example: a multi-hour reconciliation or a ledger service with strict transactional behavior and predictable latency belongs on a long-running service, while a small file-arrival trigger that kicks off a load suits a function.
  5. Close with one real decision, what you measured (cost, latency, error rate) and what you would change.

Follow-up

  • How do you handle cold-start latency on a user-facing endpoint?
  • How would you run a job that exceeds the function time limit?
  • How do you make a handler safe when the same event arrives twice?

Tell me about your very first job out of college. What specific applications and tools did you use on a daily

Medium · Behavioral & Experience Deep Dive

Tell me about your very first job out of college. What specific applications and tools did you use on a daily basis?

Approach

  1. Rebuild the facts before the interview: employer, dates, team, title and the daily stack by category (language, editor, source control, ticketing, database, build and deploy tools, reporting tools). Candidates report detailed questions on older roles, so write it down instead of trusting memory.
  2. Answer in a fixed order: one sentence on what the team did, the tools grouped by purpose, then one concrete thing you built or fixed with them. A bare list of tool names sounds recited; a specific task makes it credible.
  3. Be exact about depth. If you used a tool occasionally, say so; a loose claim about a tool invites a technical follow-up that costs you more than the honest answer would.
  4. Finish with the bridge: one habit or skill from that job that shows up in your work now, such as reading SQL execution plans or writing release notes.

Follow-up

  • Which of those tools would you still choose today, and why?
  • What was the hardest thing to learn in that role?
  • What did you ship in your first months, and how did you know it worked?

How do you handle conflict with a Product Manager regarding project timelines?

Medium · Behavioral & Experience Deep Dive

Approach

  1. Choose a real disagreement where a Product Manager wanted a date you judged risky. State the situation in two sentences, then spend the answer on your reasoning and actions.
  2. Show you replaced opinion with evidence: you broke the work into tasks, gave an estimate with its uncertainty, and named what drove the risk (an unknown integration, data quality, testing effort).
  3. Offer options, not a refusal: reduce scope to hold the date, release in phases, add capacity, or move the date. Ask what constraint sits behind the date, since the PM may be answering to a stakeholder.
  4. Report the outcome and what you tracked afterward. If the date slipped or held, say how the PM and stakeholders learned of it. Mistakes that sink this story: casting the PM as the villain, or concluding that you simply gave in without ever raising the risk.
  5. Name one thing you would do differently, such as raising the risk a sprint earlier.

Follow-up

  • What would you do if the date and the scope were both fixed?
  • How did you know your estimate was reliable?
  • How did you tell stakeholders about the change?

What experience do you have working in a strict Agile environment?

Medium · Behavioral & Experience Deep Dive

Approach

  1. Describe your actual practice concretely: the ceremonies you took part in (sprint planning, daily standup, backlog refinement, review, retrospective), your role in each, and the artifacts (stories with acceptance criteria, a definition of done, point estimates).
  2. Since the question says strict, show you can work inside rules: how you handled scope added mid-sprint, stories that carried over, and tickets blocked by another team.
  3. Give one example where the process helped, such as a retrospective that led to smaller stories and fewer carry-overs, and one where it got in the way and how you adapted without breaking the process.
  4. Connect it to working with Product Managers: how you wrote or reviewed acceptance criteria and agreed priorities. Candidates report a panel session with Product Managers on agile methods.
  5. If your background is looser than strict Agile, say so and describe the nearest elements you used, rather than claiming a framework you did not follow.

Follow-up

  • How do you handle a story that grows after the sprint starts?
  • What is one change a retrospective produced on your team?
  • How do you estimate work that contains unknowns?

We expect engineers to be highly autonomous but also collaborative. Can you give an example of how you balance

Medium · Behavioral & Experience Deep Dive

We expect engineers to be highly autonomous but also collaborative. Can you give an example of how you balance both?

Approach

  1. Define both halves with specifics. Autonomy: you own the breakdown and make decisions inside a boundary. Collaboration: you bring others in at set points, such as an API contract review, a pull request, or an early warning about risk.
  2. Pick one project and show the line you drew. Name a decision you made alone and recorded (a library choice, an internal refactor) and one you deliberately took to others because it was costly to reverse (a schema, a public API contract, a data migration).
  3. Show the mechanisms: a short design note, a clear pull request description, written status updates for the Product Manager or a data team, so the others were informed without extra meetings.
  4. Include a miss. A time you decided alone and should have asked, or waited for approval you did not need, and what you changed.
  5. The mistake to avoid is the lone hero who bypasses review, or the engineer who needs sign-off on everything.

Follow-up

  • How do you decide between escalating and deciding yourself?
  • Tell me about an independent decision that turned out wrong.
  • How do you keep partner teams informed without constant meetings?