Amazon: Alexa Requests and Households by Device Family

Easy
578.

Amazon: Alexa Requests and Households by Device Family

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

ColumnTypeValues
device_idint
household_idint
familyenumEcho 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

ColumnType
request_idint
device_idint
requested_atdatetime
intentvarchar

Primary key: request_id.

One row per voice request; device_id always names a device.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

EchoDevice

device_idhousehold_idfamily
1100Echo Dot
2100Echo Dot
3101Echo Show
4102Echo Dot
5103Fire TV

AlexaRequest

request_iddevice_idrequested_atintent
112025-03-01 07:10:00weather
222025-03-02 21:30:00music
332025-03-05 18:00:00recipe
442025-03-31 23:59:59alarm
552025-04-01 00:00:00video
632025-02-28 20:00:00music
712025-03-15 06:45:00news

Output

familyrequestshouseholds
Echo Dot42
Echo Show11
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
device_idint
household_idint
familyenum(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

ColumnType
request_idint
device_idint
requested_atdatetime
intentvarchar

Primary key: request_id.

One row per voice request; device_id always names a device.

Examples

Example 1

EchoDevice

device_idhousehold_idfamily
1100Echo Dot
2100Echo Dot
3101Echo Show
4102Echo Dot
5103Fire TV

AlexaRequest

request_iddevice_idrequested_atintent
112025-03-01 07:10:00weather
222025-03-02 21:30:00music
332025-03-05 18:00:00recipe
442025-03-31 23:59:59alarm
552025-04-01 00:00:00video
632025-02-28 20:00:00music
712025-03-15 06:45:00news

Output

familyrequestshouseholds
Echo Dot42
Echo Show11

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.family

Another 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 →