Customer Package Delivery Report
AmazonOAProblem statement
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.
Table schema
Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.
packages
One row per customer package in the FastPrep practice schema.
| Column | Type | Nullable | Description |
|---|---|---|---|
| Text | No | Email address associated with the package. | |
| weight | Decimal | No | Exact package weight. |
| status | Text | No | Current delivery status. |
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| Text | No | — | |
| total_packages | Integer | No | — |
| total_weight | Decimal | No | — |
Row order: must match exactly. Numeric tolerance: 0.
Constraints
- Status values are matched to the three values listed above.
total_packagescounts eligible package rows.total_weightsums eligible package weights exactly.- Customers with no eligible packages do not appear.