Customer Package Delivery Report
Reported by candidates from Amazon's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The fastest way to fail the Amazon Customer Package Delivery Report, reported in July 2026, is to filter after you group or to botch the status list. It's a single-table SQL aggregation: filter, group by email, count, sum, sort. Nothing exotic. That's exactly why people rush it and lose points on details. If you blank on the exact clause order during the live OA, StealthCoder runs invisibly on your desktop and hands you the query as a safety net. But this one is short enough that you should be able to write it cold after reading the next paragraph.
The problem
Amazon's shipping team needs a report of customer packages that are still in the delivery process. FastPrep provides one packages table where each row represents one package. Return email, total_packages, and total_weight for each customer. Include only packages whose status is created, shipped, or on hold. Sort by total_weight descending. When two customers have the same total weight, sort by email ascending so the result is deterministic. Practice-schema note: the reported interview prompt did not include its original table DDL. This exercise uses the single-table schema shown below to make the reported aggregation executable without inventing customer identifiers or joins. Tables packages: email (Text), weight (Decimal), status (Text) Constraints Status values are matched to the three values listed above. total_packages counts eligible package rows. total_weight sums eligible package weights exactly. Customers with no eligible packages do not appear.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The pattern is filtered aggregation. Use WHERE status IN ('created', 'shipped', 'on hold') to drop ineligible rows before grouping. Then GROUP BY email, select COUNT(*) as total_packages and SUM(weight) as total_weight. Finish with ORDER BY total_weight DESC, email ASC. The tie-break on email is what makes the output deterministic, and skipping it is the classic miss. The first-attempt mistakes are filtering in HAVING when WHERE is cleaner, forgetting the 'on hold' value has a space, and rounding the sum. The prompt says to sum weights exactly, so don't cast or round. Customers with no eligible packages vanish on their own because WHERE removes their rows before grouping. No joins are needed. If you freeze in the live OA, StealthCoder is the hedge that reads the schema and returns the query, but the whole solution is about five lines.
If you see this problem in your OA tomorrow, the play is to recognize the pattern in 30 seconds. StealthCoder buys you that recognition.
You can drill Customer Package Delivery 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 passed his OA cold and still thinks the filter is broken.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass Amazon's OA.
Amazon reuses patterns across OAs. Built by an Amazon engineer who passed his OA cold and still thinks the filter is broken. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Customer Package Delivery Report FAQ
How hard is the Amazon Customer Package Delivery Report really?+
Easy. It's one table, one filter, one GROUP BY, two aggregates and a two-key sort. The difficulty is accuracy, not concept. Most lost points come from the status list, the tie-break sort, or accidentally rounding the weight sum.
What's the trick to this query?+
Filter first with WHERE status IN ('created', 'shipped', 'on hold'), then group by email. Compute COUNT(*) and SUM(weight), and order by total_weight DESC, email ASC. The second sort key is the part people forget.
Should I use WHERE or HAVING for the status filter?+
WHERE. Status is a row-level column, so you filter before grouping. HAVING is for conditions on aggregates. Using WHERE also means customers with no eligible packages never show up, which matches the requirement.
Do I need a join or a customers table?+
No. The schema is a single packages table with email, weight and status. Email identifies the customer. Don't invent a join or customer ID, because the prompt explicitly avoids that.
How do I prepare for this in 48 hours?+
Write the query from memory three times with different filters. Practice WHERE vs HAVING, COUNT(*) vs COUNT(column), and multi-key ORDER BY. Then check NULL handling and exact decimal sums. That covers nearly every SQL aggregation question in an OA.