Interview guides · AY Tech

AY Tech Software Engineer Interview Guide

AY Tech Software Engineer interview guide: reported behavioral, OOP, DBMS and prime-number questions, BigQuery and SQL topics, plus a 7-day prep plan.

The 60-second brief

Candidates describe AY Tech's Software Engineer as the person who builds and maintains the technical infrastructure behind the company's client solutions. The work turns business requirements into working code, and the reported examples range from data-heavy BigQuery development to application logic. Day to day, candidates report writing code, taking part in code reviews, debugging existing systems, documenting what they build, managing database schemas, and talking with product managers and other engineers to pin down requirements. Some report working with external vendors on integrations. Nothing further about specific products or customers is reported, so confirm team details with the recruiter and do not guess them in an answer.

This guide is for candidates interviewing for a Software Engineer role at AY Tech, at entry level or with prior experience. It covers the reported behavioral questions with answer structures, the three reported fundamentals (a prime-number check, object-oriented concepts, database principles), the data topics linked to the role (BigQuery, data warehousing, large-scale SQL and query optimization), and a seven-day plan.

No fixed round sequence is reported. Candidates describe a path from initial screening to technical evaluation, with conversational questions early and hands-on coding after, over a span of a few weeks.

Focus areas

BigQuery
Problem Solving (Coding Fundamentals)
Communication (Interview & Self-Presentation)

Overview

The reported requirements are narrow and fundamental: fluency in at least one object-oriented language, a working grasp of database management systems, and the ability to solve algorithmic problems against a clock. The reported technical questions match that. One asks for a prime-number check, one asks you to explain the core concepts of OOP, and one asks for the fundamental principles of DBMS. Candidates also report scenarios about tightening a loop for performance and about choosing between two database approaches. Expect to answer these with code, a small example or a trade-off, not with a recited definition.

The data side of the role shows up in the topics candidates list: BigQuery, data warehousing concepts and large-scale SQL with query optimization. BigQuery is a columnar warehouse where cost and speed follow how much data a query scans, so be ready to explain why selecting only the needed columns, filtering on a partition column and avoiding repeated full-table joins matter. Pair that with plain relational fundamentals: keys, normalization, indexes, transactions and joins. The nice-to-have skills candidates mention are HTML, CSS, data-heavy environments and vendor-managed integrations, so one concrete story in any of those areas is worth having.

Half of the reported questions are behavioral: introduce yourself, describe yourself in three words, explain why you want to work there, name a strength and a weakness with how you compensate, and say whether you prefer managing people or ideas. Candidates are also told to review past projects and explain the reasons behind technical decisions, not only the steps. Write each answer as a short structure with one real example, practise it aloud, and keep the stories consistent with each other, because the same project will probably appear in several answers.

Because the format is not reported in detail, spend the first day getting it from the recruiter and then split your time between three things: speaking fluently about yourself and your projects, explaining the fundamentals with code or a concrete example, and writing correct code quickly for small problems.

Where to focus your preparation

Candidates do not report a numbered round sequence, so this guide treats the loop as one preparation block covering the conversational questions and the technical fundamentals they describe.

Preparation focus

Candidates describe the process as moving from initial screening to technical evaluation, with conversational questions early and hands-on coding later, and finishing within a few weeks depending on the team and on how quickly scheduling goes. No round names, order or per-stage format are reported, so treat this block as everything you may meet and confirm the sequence with the recruiter. The conversational questions reported are the five behavioral prompts. The technical questions reported are the prime-number check, the core concepts of OOP and the principles of DBMS, with BigQuery, data warehousing and large-scale SQL query optimization listed as topics. Approach it in two tracks: rehearse spoken answers with one real example each, and rehearse small pieces of code and short explanations until you can produce them cleanly without notes.

What to demonstrate

Whether you can write correct, readable code for a small algorithmic problem such as a primality check
Whether you can explain OOP and database fundamentals with an example rather than a definition
How clearly you present yourself and your past projects, including the reasons behind your technical decisions
Familiarity with the data topics linked to the role: BigQuery, data warehousing and SQL query optimization

How to prepare

Ask the recruiter for the sequence, whether coding is live, and which language you may use, then adjust the plan to the answers
Write a prime check in your strongest language, covering 0, 1, 2, negatives and large inputs, and state its complexity
Prepare one example class hierarchy for OOP and one two-table schema with a query for DBMS, so every concept has something concrete behind it
Write STAR outlines for the five behavioral questions and rehearse them aloud until they run without notes

Where candidates lose points

Writing the primality check as a loop from 2 to n, or forgetting that 0, 1 and negative numbers are not prime

Handle the edge cases first, then test divisors only up to the square root of n, skipping even numbers after 2. State the O(sqrt n) cost and mention a sieve if the task is to list primes in a range.

Reciting the four OOP principles as definitions without showing them in code, or mixing up encapsulation and abstraction

Keep one small example, such as a base class with two subclasses and a private field. Point to the exact line that shows each principle and say what breaks if it is removed.

Answering the DBMS question with a list of terms (ACID, normalization, keys) and no example

Tie each principle to a two-table scenario: a transfer that must commit or roll back, a duplicated column that normalization removes, a foreign key that blocks an orphan row. Add one sentence on what an index costs.

Giving a weakness that is a disguised strength, or a real weakness with no way of compensating for it

Name a genuine, non-core weakness, describe the habit or check you built, and say what changed because of it. Rehearse it aloud so it does not sound scripted.

Explaining how a project was built but not why the technical decisions were made

For each project, write down the alternative you rejected, the reason, and the consequence. Be ready to describe a choice between two database approaches in those terms.

Questions & practice

Track a rolling failure rate per destination for circuit decisions

Easy · sliding window · ring buffer · circuit breaker

The egress service delivers about 1,500 webhooks per second across roughly 40,000 destinations, each call bounded by a 10 second timeout. Maintain, per destination, the failure rate over the trailing 60 seconds so a caller can ask before dispatch whether the circuit should open. Attempts arrive as (destination_id, finished_at_ms, outcome). Requirement: amortised O(1) per attempt, with total memory bounded by the destination count rather than by traffic. Give the structure, its exact memory, and the rule that stops a destination with three attempts from opening a circuit.

Approach

Name the exact-deque version and then reject it as the default. Holding timestamps and advancing a tail pointer past anything older than now minus 60 seconds is a correct two-pointer window at amortised O(1) per attempt, but its memory tracks in-window traffic, so one destination in a retry storm holds hundreds of thousands of entries while thousands of quiet destinations hold none.
Use a ring of 60 one-second buckets per destination, each bucket a pair of counters for attempts and failures. On an attempt, advance the ring by the elapsed whole seconds, zeroing at most min(elapsed, 60) buckets, then increment the head. That is amortised O(1) with a fixed footprint per destination.
State the footprint: 60 buckets times two 4-byte counters is 480 bytes of payload per destination, so 40,000 destinations is roughly 20 to 25 MB with per-entry overhead, bounded by the catalogue rather than by the rate. The cost is granularity, since the oldest bucket ages out in whole seconds, which is far tighter than the decision needs.
Require a minimum sample before the circuit may open. A destination with three attempts and three failures reads as 100 percent and is not evidence; a floor of roughly 20 attempts in the window makes the ratio meaningful, and below that floor use a run of consecutive failures as the trigger instead.

Follow-up

The fleet is 30 instances and each sees roughly a thirtieth of a destination's traffic. Where does the rate actually live, and what does a per-instance answer get wrong?
A destination answers in 9.5 seconds and succeeds. It is not failing but it is consuming your per-destination concurrency. What signal should open the circuit here?

Merge partitioned event streams into one ordered feed with bounded lateness

Hard · k-way merge · watermarks · out-of-order streams

The read-model service consumes 64 log partitions carrying about 4,000 events per second in total. Each partition is ordered within itself, but partitions drift by up to 30 seconds, and the activity feed must present a tenant's events in occurred_at order. Produce the merge. State its complexity, the buffer it requires in events and in bytes, what happens when one partition is idle, and what you do with an event that arrives after you have already emitted its position. Payloads average 1 KB.

Approach

Merge with a min-heap over the 64 partition heads keyed on (occurred_at, event_id): O(log P) per event and O(n log P) overall. The tie-break on event_id is what makes the output deterministic when two partitions carry the same millisecond, which matters because the feed is paginated and a non-deterministic order reorders pages under the reader.
Emitting the heap head is only correct once every partition has produced everything up to that timestamp, so the emit condition is a watermark: the minimum across partitions of the highest occurred_at seen, less the allowed lateness. Events are held until the watermark passes them, which is what turns individually ordered streams into a jointly ordered one.
Size the buffer from the lateness rather than guessing: 4,000 events per second times 30 seconds is 120,000 buffered events, and at 1 KB each about 120 MB of heap. That number is the real price of the ordering guarantee and belongs in front of whoever asked for it.
Handle the idle partition explicitly, because it fails the feed rather than corrupting it: a partition with no traffic never advances its own maximum, so the watermark freezes and output stops entirely. Either every partition emits a periodic idle marker carrying the broker's current time, or the watermark falls back to wall clock for a partition silent beyond a threshold.

Follow-up

The lateness budget is raised to five minutes. What is the new buffer, and what besides memory changes?
The consumer restarts. Where does it resume from, and what does the feed look like for the first 30 seconds?

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

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.
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.
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.
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?

Stop tag and share joins from fanning out a page

Medium · join fan-out · semi-joins · many-to-many

resource_tag is (resource_id, tag_id) with PK (resource_id, tag_id); resource_share is (resource_id, shared_with_user_id, permission). The tagged-and-shared listing inner-joins resource to both, filters tenant_id, tag_id = ANY($2) and shared_with_user_id = $3, orders by updated_at DESC and takes 50. Pages come back with fewer than 50 distinct resources and the total in the header is far too high. Explain the row multiplication, rewrite both the page query and the count query so each is correct, and name the index each one needs. PostgreSQL 16.

Approach

Do the arithmetic against the predicates that are actually there. An inner join emits one row per matching child row, and both joins are filtered: tag_id = ANY($2) admits only the requested tags, shared_with_user_id = $3 admits one user's share rows. So a resource holding three of the requested tags and shared with $3 once yields three rows, not one — the multiplier is its count of matching tags times its share rows for that single user, and that second factor is 1 unless the table admits duplicate (resource_id, shared_with_user_id) pairs. LIMIT 50 then limits rows rather than resources, and COUNT(*) counts pairs — the header is the product, not the population.
Reject DISTINCT as the fix. It deduplicates after the product has been built, so the planner must materialise and sort the fanned-out set before the LIMIT can apply, and it leaves any SUM or AVG in the same select list wrong.
Rewrite both filters as semi-joins, keeping resource as the only row source: AND EXISTS (SELECT 1 FROM resource_tag rt WHERE rt.resource_id = r.resource_id AND rt.tag_id = ANY($2)) and the same shape against resource_share. A semi-join stops at the first match per resource and preserves the driving index order, so ORDER BY updated_at DESC, resource_id DESC LIMIT 50 still stops after 50 rows.
Count with the same predicates and no join at all: SELECT count(*) FROM resource r WHERE r.tenant_id = $1 AND r.status = 'active' AND EXISTS (...) AND EXISTS (...). Nothing multiplies a resource, so the number is the population.

Follow-up

The filter changes from 'any of these tags' to 'all of these tags'. Rewrite it and state what it costs relative to the ANY form.
A resource can be shared with the same user twice under different permissions. Does your count change, and should it?

Explain why the owner filter ignores the listing index

Medium · composite index · query plans · sort elimination

The only index on resource is (tenant_id, status, updated_at DESC, resource_id DESC). A new endpoint returns one user's resources across all statuses, newest created first: WHERE tenant_id = $1 AND owner_user_id = $2 ORDER BY created_at DESC LIMIT 20. On a tenant with 2M rows it takes 900 ms and EXPLAIN shows a sort above a large scan. Explain precisely why the existing index cannot serve it, give the index that can, and state which of these the new index still will not help: owner_user_id alone across tenants; the same query ordered by updated_at. PostgreSQL 16.

Approach

Separate the two jobs an index does. For filtering, a composite btree is seekable only on a left prefix, so with no predicate on status the scan can at best range over tenant_id and test owner_user_id per row; PostgreSQL 16 has no btree skip scan to jump the unconstrained column.
For ordering, the index is sorted by (status, updated_at) within a tenant and not by created_at, so the LIMIT cannot stop early: every matching row is read and then sorted. That is the 'Sort Method: top-N heapsort' line, and it is why the plan reads 2M rows to answer with 20.
Derive the replacement from the access path — equality, equality, then the ordering column: CREATE INDEX CONCURRENTLY ON resource (tenant_id, owner_user_id, created_at DESC). The scan seeks to the (tenant, owner) range and walks 20 entries in order, so the Sort node disappears along with the row-read.
Treat INCLUDE (title, status) as conditional, not free. An index-only scan still visits the heap for any row whose page is not marked all-visible, so on a table taking 1.2k writes/second the win depends on autovacuum keeping the visibility map current, and the wider index costs more on every insert.

Follow-up

90% of rows are status='active'. Would a partial index WHERE status = 'active' change your answer, and for which of the three queries?
A dashboard runs this for 40 owners in one page load. What changes about the design?

To find if a number is prime or not.

Medium · Technical Fundamentals

Approach

Settle the small cases first: any n < 2 (including 0, 1 and negatives) is not prime, 2 is prime, and any other even n is composite, so the loop only has to try odd divisors.
Use trial division up to the square root: if n = a * b with a <= b, then a <= sqrt(n), so finding no divisor up to sqrt(n) proves n prime. Loop while i * i <= n (or i <= n / i to avoid overflow in fixed-width integers) instead of calling a floating-point sqrt. That is O(sqrt n) time and O(1) space.
Test the boundaries out loud: perfect squares such as 9, 25 and 49 only fail if the condition is inclusive (<=), and 2, 3 and 1 each exercise a different early return.
Optionally step by 6 and test i and i + 2 (the 6k +/- 1 form), since every prime above 3 has that form. It cuts the work by a constant factor but leaves the complexity at O(sqrt n).
If the question becomes 'all primes up to N', switch to the Sieve of Eratosthenes: start crossing out at p * p, stop once p * p > N, for O(N log log N) time and O(N) memory.

Follow-up

How would you list every prime below 10 million, and how would you cut the sieve's memory (odd-only array, bitset)?
How would you count primes in a range [L, R] where R is around 10^12 but R - L is small (segmented sieve using primes up to sqrt(R))?
Why is O(sqrt n) trial division too slow for 64-bit inputs, and what does Miller-Rabin with a fixed set of bases give you instead?

Can you explain the core concepts of OOPs?

Medium · Technical Fundamentals

Approach

Name the four concepts and define each in one sentence: encapsulation (state is private and changed only through methods that keep invariants), abstraction (callers depend on an interface, not the implementation), inheritance (a subclass reuses and extends a base class in an is-a relationship) and polymorphism (one call resolves to different behaviour depending on the object's type).
Carry one running example through all four, such as an Account class with a private balance, a deposit method that rejects negative amounts, a SavingsAccount subclass that overrides withdraw, and code that holds an Account reference and calls withdraw without knowing the subtype.
Separate the two kinds of polymorphism: overloading (same name, different parameter lists) is resolved at compile time, while overriding is resolved at runtime through dynamic dispatch. In C++ that needs a virtual method; in Java instance methods are virtual by default, and static methods are hidden rather than overridden.
Contrast abstract classes with interfaces in your language: in Java an abstract class can hold fields and constructors but a class extends only one, while a class can implement many interfaces, which can carry default methods since Java 8.
Close with the design trade-off: prefer composition over deep inheritance trees to avoid the fragile base class problem, and keep subclasses substitutable for their base (Liskov substitution), for example a subclass must not throw on an operation the base class supports.

Follow-up

What is the difference between overloading and overriding, and which one is decided at compile time?
When would you choose composition over inheritance? Give an example from your own code.
What is the diamond problem, and how do Java, C++ and Python each deal with it (default-method conflicts, virtual inheritance, the C3 method resolution order)?

What are the fundamental principles of DBMS?

Medium · Technical Fundamentals

Approach

Start from the relational model: data lives in tables of rows and columns, every table has a primary key, foreign keys link tables, and integrity constraints (NOT NULL, UNIQUE, CHECK, referential integrity) keep invalid data out at the database level instead of in application code.
Explain ACID with a concrete transfer between two accounts: atomicity (both updates commit or neither does, implemented with a write-ahead or undo log), consistency (constraints hold before and after), isolation (concurrent transactions do not see each other's partial work) and durability (a committed change survives a crash because the log is flushed to disk before commit returns).
Cover isolation levels and the anomaly each one allows: read uncommitted permits dirty reads, read committed permits non-repeatable reads, repeatable read in the SQL standard still permits phantoms, and serializable prevents all three. Mention that many engines implement this with MVCC rather than only with locks.
Walk through normalization on a small table: 1NF (atomic values, no repeating groups), 2NF (no non-key column depends on only part of a composite key), 3NF (no non-key column depends on another non-key column), BCNF (every determinant is a candidate key). Then name the trade-off: normalized schemas avoid update anomalies, while read-heavy reporting schemas such as a star schema denormalize to reduce joins.
Finish with indexing: a B-tree index turns a lookup from a full scan into O(log n), speeds equality and range filters, but costs extra storage and slows inserts and updates. A composite index on (a, b) serves filters on a alone or on a and b, not on b alone.

Follow-up

Given a table of orders with customer name, customer address and product details in each row, how would you normalize it to 3NF?
Which isolation level prevents phantom reads, and what does it cost in concurrency?
How does this change in a columnar warehouse such as BigQuery, where on-demand cost follows bytes scanned and partitioning and clustering do the work that indexes do in a row store?

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

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.
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.
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.
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

The plan follows what candidates report: a recruiter check on format, then the prime-number, OOP and DBMS fundamentals, the BigQuery and SQL topics, timed coding, behavioral answers and a final mock.

Day 1 — Confirm the format and map the topics

Email the recruiter to ask for the stage sequence, whether coding is live and which languages are allowed.
Write a one-page list of the reported topics: prime check, OOP, DBMS, BigQuery, data warehousing, large-scale SQL, and the five behavioral questions.
Mark each topic strong, shaky or untouched, and choose your language for coding questions.

Deliverable: A recruiter email sent and a one-page topic list with each item rated strong, shaky or untouched.

Day 2 — Prime check and loop performance

Write is_prime in your chosen language with trial division up to the square root, and test 0, 1, 2, 3, 4, a large prime and a negative.
Write a second version that skips even divisors, then a sieve of Eratosthenes for all primes up to n, and note each complexity.
Take a nested loop you have written and rewrite it to remove repeated work, noting the before and after cost.

Deliverable: Two tested prime functions, a sieve, and a short note describing one loop you optimized.

Day 3 — Object-oriented concepts with code

Write one small program that shows encapsulation, inheritance, polymorphism and abstraction, such as a base class with two subclasses and an interface.
Prepare spoken definitions that point at lines in that code, plus a note on when you would choose composition over inheritance.
Say the answer aloud once without notes.

Deliverable: A runnable example program and a spoken explanation that references it.

Day 4 — Database principles, SQL and BigQuery

Write the DBMS answer around an example: ACID with a transfer, normalization with a duplicated column, keys and indexes with a lookup.
Read how BigQuery bills by data scanned and how partitioning and clustering change that, then rewrite one query to scan fewer columns and rows.

Deliverable: A one-page DBMS cheat sheet with examples, two solved SQL exercises and one rewritten BigQuery-style query.

Day 5 — Timed coding and debugging

Solve a second one, then repeat sorting, searching and a simple math function from memory.

Deliverable: Two timed solutions, a note on where time was lost and a written debugging sequence.

Day 6 — Behavioral answers aloud

Write STAR outlines for the introduction, the three-word description, the reason for choosing this company, the strength and weakness, and the people-or-ideas question.
For each of your main projects, write the alternative you rejected and why, so you can explain the reasons behind decisions.
Record yourself answering all five and cut anything vague.

Deliverable: Five written outlines, one list of project decisions with rejected alternatives, and a recording of the five answers.

Day 7 — Full dry run and questions to ask

Run a mixed mock: the introduction, a prime check written live, an OOP explanation, a DBMS explanation and one behavioral answer.
Note what you stumbled on and fix the two weakest answers.
Write two or three questions about the team, the stack and the engineering work to ask at the end.

Deliverable: A completed mock with notes, two repaired answers and two or three questions to ask.

Behavioural preparation

Candidates report that the conversational questions focus on self-awareness, professional history and how you work with a team, and they describe a tip to explain the reasons behind your technical decisions, not only the steps. Prepare one real example for each prompt, keep your stories consistent, and rehearse them aloud.

Tell me about a technical decision on a past project, what you chose and what you rejected.