In this article
Before you start
This page is one of the build tasks in the interview process guide, which maps rounds and who scores what.
Why interviewers ask this
An agent that leaks one customer's email address is a failed product, however accurate its totals are. The take-home tests whether a candidate sees that before the interviewer says it.
Candidates have reported a sales-insights version of this task at Cohere: answer subscription and revenue questions, refuse requests for emails, phone numbers and card tokens, pass no raw rows to the model, and be evaluated on accuracy, safety and reasoning. Candidates have also reported related tasks: cleaning a dataset with SQL and Python and judging its compliance (Anthropic), and a production SQL analytics pipeline with token counting, validation, observability and benchmarking (GenAI Labs). Other reported variants turn a spreadsheet of performance projections into views for two audiences (Cerebras) and turn project-management data into business answers with two cooperating models (Skylark Drones). These are reports from candidates, not public company statements.
A weak answer shows a working chart. A strong answer shows the controls that fail safely. The forward deployed and applied AI engineer rounds use the same build-and-defend format.
The analytics agent from first principles
- Aggregate-only output
- Aggregate-only output is a design rule that lets the language model see only computed summaries, such as sums, counts and rates, never individual table rows, so a prompt carries no individual row; small or subtractable groups still need suppression.
- Minimum group size
- A minimum group size is a threshold that suppresses any aggregate computed over fewer rows than the threshold, because a summary of one or two people can reveal a person.
In plain words
Picture a bank teller behind glass. Customers ask questions, and the teller answers from a ledger the customer cannot touch. The teller may say totals but not read out one account holder's details. Your agent is the glass plus the rules. The analogy breaks in one place: a teller understands why a rule exists, while a language model can be talked out of a rule.
The analytics agent take-home is a build task in which a candidate makes an agent that turns a question into a safe query, runs it with minimal permissions, and shows the model only a summary of the result.
Three words carry the page. Personally identifiable information (PII) is any data that identifies a person, such as an email address, a phone number or a card token. OWASP (the Open Worldwide Application Security Project) counts PII and financial details as sensitive information. Prompt injection is an input that changes the model's behaviour in ways nobody intended (OWASP, prompt injection risk). Execution accuracy is the share of questions where the agent's query result equals the result of a trusted reference query.
Step 1: Give the model aggregates and suppress small groups
Aggregate-only output means the model never receives table rows. The code runs the query, summarises the result and passes the summary on. In a toy table of 1,000 rows (illustrative), the rows as text take 38,416 characters, while the summary of total revenue, churn rate, average revenue per user (ARPU) and customer count takes 68.1 Sending raw rows would also put every email into the prompt.
Aggregates are not automatically safe. Grouped by plan and region, the toy table has 14 groups, and 2 hold a single customer.1 One group, enterprise plan in one region, has one customer who has churned, so a churn rate of 100% names that customer's status.1 The fix is a minimum group size: with a threshold of 5, both groups are suppressed.1
OWASP lists adding noise to outputs as a further mitigation (OWASP, sensitive information disclosure); the threshold itself, called small-cell suppression, is common engineering practice, not an OWASP rule. Suppression alone is defeated when totals or overlapping queries let a user subtract: the total customer count minus the visible groups gives a hidden group back. Counters include complementary suppression (hide a second cell), noise on outputs, and query auditing.
You now know what the model may see. Next you control what the user may ask.
Text description of the diagram
- Sending the toy table's 1,000 rows as text takes 38,416 characters.
- The aggregate summary takes 68 characters.
Step 2: Refuse PII requests in code, not only in the prompt
A code gate refuses personal-data requests before the model sees them. A rule in the system prompt is a soft control: OWASP warns that such restrictions may not always be honored and can be bypassed through prompt injection (OWASP, sensitive information disclosure).
A simple gate is a pattern match for words such as email, phone and card token, plus a column allow-list that never exposes those columns. Test it. On ten illustrative prompts, five asking for PII and five benign, a keyword pattern caught 4 of the 5 and wrongly blocked 1 of the 5 benign ones, a question about customers who contacted support by email.1 The miss was a paraphrase with no keyword. So precision and recall are both 0.8 here, and a stronger design adds a classifier or a second check on the generated query.1
Input: the user's question. Output: allow or refuse, with a plain reason. You now have a gate on the way in and a rule for what comes out.
Step 3: Validate the SQL and run it with a read-only identity
A validator checks model-written SQL before a read-only database runs it. Treat the generated query as untrusted output: a hallucination or an injected instruction can both produce a damaging action (OWASP, excessive agency).
The toy validator accepts one statement, only a read, only allow-listed columns (plan, region, mrr and churned), and no wildcard. Of six illustrative queries, it accepted 2: a grouped revenue sum and a grouped average.1 It rejected a wildcard read, an email read, a delete and a two-statement query.1 In production, parse the SQL with a real parser, because simple text checks miss cases; that is also why the read-only identity stays the backstop.
The validator is not the backstop. OWASP's example is an agent that needs read access to one table and should not insert, update or delete, enforced through the permissions of the identity it connects with (OWASP, excessive agency, mitigation 4). Use both: permissions are the hard limit, the validator gives readable errors. Narrow tools, such as a fixed set of aggregate queries, are safer than free-form SQL.
Step 4: Log every step and score the agent on a small eval set
Observability means every request leaves a record: the question, the gate decision, the query, the validator verdict, the usage figures the application programming interface (API) returns and the answer. For token counting, log what the provider's response reports rather than a guess.
Then benchmark. Write a small eval set by hand with reference answers. On three illustrative questions, the agent matched the reference on 2, an execution accuracy of 2 of 3.1 The miss was a churn rate computed as churned customers divided by active customers (0.1062) instead of by all customers (0.096), a definition error no safety control catches.1 Evaluate refusals too: count correct refusals and wrongly refused benign questions.
| Check | Toy result (illustrative data) |
|---|---|
| Active customers, total revenue (units) | 904, 37,790 |
| Churn rate, revenue per user (ARPU) | 9.6%, 41.8 |
| Groups below minimum size 5 | 2 of 14 |
| PII gate, precision and recall | 0.8 and 0.8 |
| SQL validator, queries accepted | 2 of 6 |
| Execution accuracy | 2 of 3 |
Here monthly recurring revenue (MRR) is the sum of the monthly price over active customers, and average revenue per user (ARPU) is MRR divided by active customers.1
Step-by-step description
- The model sees summaries, and small groups are dropped.
- Code refuses personal-data requests.
- SQL is checked, then run read-only.
- Every step is logged and scored.
- A code gate refuses personal-data requests before the model sees them.
- A validator checks model-written SQL before a read-only database runs it.
Knowing it is half the job. Saying it is the other half.
Interview Clozo is an AI interview copilot for real interview rounds: it hears the question and puts a strong answer on your screen, and you say it in your own words. See the features or how it works.
Start Free Trial →The Staff-level answer
How would you build an agent that answers subscription and revenue questions without leaking personal data?
I'd use four gates. First, a deterministic filter in code refuses requests for emails, phone numbers and card tokens before the model sees them, because a prompt rule alone can be bypassed. Second, the model only sees aggregates, with a minimum group size so a group of one customer is suppressed. Third, any SQL the model writes is parsed, checked against an allow-list of columns, and run under a read-only database identity. Fourth, I log every step and score the agent on a small hand-written eval set for accuracy, refusal correctness and reasoning. The trade-off is that keyword refusals miss paraphrases and block innocent questions, so I measure both.
What makes it Staff-level: each control comes with its failure mode and the measurement that exposes it.
The rubric
| Dimension | Weak answer | Strong answer | Staff answer |
|---|---|---|---|
| Correctness | Demo answers a few questions | Checks results against reference queries | Defines metrics once and tests edge definitions such as churn |
| Safety | Prompt says "do not reveal PII" | Code gate and aggregates only | Adds group-size suppression and permissions as the hard limit |
| Trade-offs | Does not mention any | Notes false refusals | Measures precision and recall of refusals |
| Evaluation | Looks at output by eye | Small eval set with scores | Separate scores for accuracy, refusal and reasoning |
| Communication | Lists features | Explains each gate | Orders gates by what fails worst |
Three variants of this question
How do you build a prompt-to-chart system with a SQL backend?
Have the model emit a small chart specification (chart type, grouping, measure, filter) instead of drawing code. Validate it against a schema, build the aggregate query from the specification, and render from the result. For template generation, fill fixed slots with escaped values so model text never runs as markup.
How do you extract a clean dataset with SQL and Python and judge whether it is compliant?
Check types, nulls, duplicates and units, then list which columns are PII and whether the analysis needs them at all. Drop or aggregate what you do not need. Name the ethical trade-off: a richer dataset helps analysis but raises the cost of a leak.
How do you build a production SQL analytics pipeline with token counting, validation, observability and benchmarking?
Reuse the four steps: log token usage from each API response, validate every query, trace each request from question to answer, and run the eval set on every change. Gate releases on the scores.
Common mistakes
- "The system prompt says never reveal PII, so we are covered." (wrong) OWASP says such restrictions may be bypassed. (right) "A code gate refuses PII, and the prompt is a second layer."
- "I only send aggregates, so nothing can leak." (wrong) A group of one is a person. (right) "I suppress groups below a minimum size and check that totals cannot be used to subtract them back out."
- "My SQL validator makes permissions unnecessary." (wrong) A parser can miss a case. (right) "The database identity is read-only; the validator adds readable errors."
- "The demo works on my five questions." (wrong) That is not a benchmark. (right) "I score a hand-written eval set and report refusals separately."
Follow-up questions
Is telling the model to refuse personal data enough?
No. OWASP says prompt restrictions may not always be honored and can be bypassed (OWASP, sensitive information disclosure), so refusal must also happen in code.
Are aggregates always safe to send to the model?
No. A group of one customer reveals that customer, as the toy table shows with 2 of 14 groups below the threshold.1 Use a minimum group size, but know that suppression alone can be subtracted back out using totals or overlapping queries, so add complementary suppression, noise (differential privacy) or query auditing for stronger protection.
Does a SQL validator replace database permissions?
No. Permissions are the hard backstop and the validator is a second layer. OWASP says to enforce read-only access through the database identity the agent uses (OWASP, excessive agency).
How do you evaluate the agent?
Write a small set of questions with reference answers and compare result sets (execution accuracy). Score refusals separately, counting both missed PII requests and wrongly blocked benign ones.
What if the minimum group size changes?
A higher threshold suppresses more groups, which protects more people but hides more detail. It does not remove the subtraction risk, so pair it with complementary suppression or noise, and choose it with the data owner as a policy choice.
More build tasks and questions of this kind are in the AI engineer interview questions, and the AI-assisted coding interview covers how such rounds are graded. For neighbouring build tasks see the agent take-home and the retrieval-augmented generation (RAG) take-home; for one company's process see how the Anthropic interview process works.
- The take-home is graded on accuracy, safety and reasoning, so build the guardrails as carefully as the answers.
- The model sees aggregates only, and groups below a minimum size are suppressed.
- PII refusal lives in code, because prompt rules can be bypassed.
- Validate generated SQL and run it under a read-only identity.
- Log every step and score a small eval set, refusals included.
Sources
- OWASP Top 10 for LLM Applications 2025 (v2.0) — OWASP GenAI Security Project, 14 Nov 2024
- LLM02:2025 Sensitive Information Disclosure — OWASP GenAI Security Project, 18 Nov 2024
