Advertising System Failures Report
Reported by candidates from AT&T's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The AT&T OA reported in November 2024 hands you a HackerAd schema and one blunt ask: find every customer with more than three events whose status is exactly failure. It's a SQL question, not an algorithm one. Three tables, two joins, one GROUP BY, one HAVING. If your brain is already reaching for a design pattern, stop. This is a join-and-aggregate problem, and it's very doable if you stay calm. The trap is in the wording, not the difficulty. If you freeze on the syntax mid-assessment, StealthCoder runs invisibly as a safety net and gives you the query in real time.
The problem
HackerAd analyzes events from advertising campaigns. Produce a report of every customer who has more than three campaign events whose status is exactly failure. Return two columns: customer, the customer's first and last names separated by one space, and failures, the total number of matching failure events across all campaigns owned by that customer. Tables customers: id PK (Integer), first_name (Text), last_name (Text) campaigns: id PK (Integer), customer_id (Integer), name (Text) events: dt PK (Timestamp), campaign_id (Integer), status (Text) Constraints Only rows whose status is exactly failure count. A failure is attributed through events.campaign_id -> campaigns.id -> campaigns.customer_id -> customers.id. Include a customer only when the total number of failures is greater than three. Each customer ID, campaign ID, and event timestamp is unique within its table. The result row order is not significant.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The query pattern is a chain join with grouped counting. Start at events, join campaigns on events.campaign_id = campaigns.id, then join customers on campaigns.customer_id = customers.id. Filter WHERE status = 'failure' before grouping, because only exact failure rows count. Then GROUP BY customers.id, and select CONCAT(first_name, ' ', last_name) AS customer with COUNT(*) AS failures. Finish with HAVING COUNT(*) > 3. The common pitfall is counting per campaign instead of per customer. A customer with two campaigns and two failures each has four total and must appear. Group on the customer, never the campaign. Another slip is using WHERE for the threshold instead of HAVING, which errors out. Watch the string concat too, since some dialects want || instead of CONCAT. Group by id, not name, so two people with the same name don't merge. If the syntax slips under pressure, StealthCoder is your hedge during the live OA.
If this hits your live OA and you blank, StealthCoder solves it in seconds, invisible to the proctor.
You can drill Advertising System Failures Report cold, or you can hedge it. StealthCoder runs invisibly during screen share and surfaces a working solution in under 2 seconds. The proctor sees the IDE. They don't see what's behind it. Built by an Amazon engineer who would have shipped this the night before his JPMorgan OA if he'd had it.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass AT&T's OA.
AT&T reuses patterns across OAs. Built by an Amazon engineer who would have shipped this the night before his JPMorgan OA if he'd had it. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Advertising System Failures Report FAQ
How hard is the AT&T Advertising System Failures Report really?+
Easy to medium. It's three tables, two inner joins, a filter, a group, and a HAVING clause. If you've written a join with aggregation before, you can finish it quickly. The difficulty is only in getting the grain right, which is per customer.
What's the trick to this query?+
Aggregate at the customer level, not the campaign level. Join events to campaigns to customers, filter status = 'failure', GROUP BY customer id, and use HAVING COUNT(*) > 3. Failures spread across multiple campaigns must add up to one total per customer.
Should the failure filter go in WHERE or HAVING?+
Put status = 'failure' in WHERE so non-failure rows never get counted. Put the greater-than-three check in HAVING, since it applies to the aggregated count. Mixing them up is the most common way to get wrong output or a syntax error.
How do I build the customer name column?+
Concatenate first_name, a single space, and last_name, and alias it as customer. Use CONCAT(first_name, ' ', last_name) in MySQL or first_name || ' ' || last_name in Postgres or SQLite. Name the second column failures exactly as the problem states.
How do I prepare in 48 hours for an SQL OA like this one?+
Write five or six queries using multi-table joins, GROUP BY, and HAVING until the pattern is automatic. Practice choosing the grouping key before typing anything. Also review COUNT(*) versus COUNT(DISTINCT), and how your dialect handles string concatenation.