Amazon: Alexa Requests and Households by Device Family
The Alexa devices team compares engagement across device families for the monthly business review.
- Difficulty: Easy
- Topics: Joins, Aggregation, Dates
- Dialect: MySQL
- Problem: #578
Problem statement
The Alexa devices team compares engagement across device families for the monthly business review.
For March 2025, return one row per device family that received at least one request: family, requests (the number of requests made in March from devices of that family) and households (the number of distinct households that made them). Families with no March requests are left out. Return the rows in any order.
A practice scenario set at Amazon: the tables and data are invented, and this is not a question from, or endorsed by, Amazon.
Tables
Table: EchoDevice
| Column | Type |
|---|---|
| device_id | int |
| household_id | int |
| family | enum(Echo Dot, Echo Show, Echo Studio, Fire TV) |
Primary key: device_id.
One row per registered device; a household may own several devices, of the same family or different ones.
Table: AlexaRequest
| Column | Type |
|---|---|
| request_id | int |
| device_id | int |
| requested_at | datetime |
| intent | varchar |
Primary key: request_id.
One row per voice request; device_id always names a device.
Examples
Example 1
EchoDevice
| device_id | household_id | family |
|---|---|---|
| 1 | 100 | Echo Dot |
| 2 | 100 | Echo Dot |
| 3 | 101 | Echo Show |
| 4 | 102 | Echo Dot |
| 5 | 103 | Fire TV |
AlexaRequest
| request_id | device_id | requested_at | intent |
|---|---|---|---|
| 1 | 1 | 2025-03-01 07:10:00 | weather |
| 2 | 2 | 2025-03-02 21:30:00 | music |
| 3 | 3 | 2025-03-05 18:00:00 | recipe |
| 4 | 4 | 2025-03-31 23:59:59 | alarm |
| 5 | 5 | 2025-04-01 00:00:00 | video |
| 6 | 3 | 2025-02-28 20:00:00 | music |
| 7 | 1 | 2025-03-15 06:45:00 | news |
Output
| family | requests | households |
|---|---|---|
| Echo Dot | 4 | 2 |
| Echo Show | 1 | 1 |
How to solve Amazon: Alexa Requests and Households by Device Family
Each request needs its device's family and household, so the query starts with an inner join from AlexaRequest to EchoDevice. The March filter is a half-open range on the timestamp: >= '2025-03-01' and < '2025-04-01' keeps a request at 23:59:59 on the 31st and drops one at midnight on 1 April, with no need to know how many days March has.
Grouping by family, COUNT(*) counts requests while COUNT(DISTINCT household_id) counts households: a household with two Echo Dots talking all month is one household but many requests. Families with no March requests produce no joined rows and so no group — the statement wants them left out, so an inner join is right.
The alternative filters with YEAR() and MONTH() inside the join condition. It gives the same answer but cannot use an index on requested_at, which the range form can.
Reference solution (MySQL)
SELECT d.family, COUNT(*) AS requests, COUNT(DISTINCT d.household_id) AS households
FROM AlexaRequest r
JOIN EchoDevice d ON d.device_id = r.device_id
WHERE r.requested_at >= '2025-03-01' AND r.requested_at < '2025-04-01'
GROUP BY d.familyAnother way
SELECT d.family, COUNT(r.request_id) AS requests, COUNT(DISTINCT d.household_id) AS households FROM EchoDevice d JOIN AlexaRequest r ON r.device_id = d.device_id AND YEAR(r.requested_at) = 2025 AND MONTH(r.requested_at) = 3 GROUP BY d.family← Amazon: Return Requests With No Delivered Order Behind Them · Amazon: Subscribe & Save Discount Tier per Delivery Month →