Coding questions · Kneron
Count IoT devices matching date and threshold
This question evaluates handling of paginated REST APIs, JSON parsing and nested-data filtering, month/year date parsing, and numeric threshold comparisons, reflecting skills in data retrieval and programmatic data manipulation.
Implement function numDevices(statusQuery, threshold, dateStr) that returns the number of IoT devices from a paginated REST API that were added in the month and year given by dateStr (format MM-YYYY) and have operatingParams.rootThreshold > threshold. Query all pages of: https://jsonmock.hackerrank.com/api/iot_devices/search?status=<statusQuery>&page=<pageNumber> (replace placeholders). The API returns JSON with fields: page, per_page, total, total_pages, data (array of devices). Each device has: id, timestamp (UTC ms when added), status, operatingParams { rotorSpeed, slack, rootThreshold }, asset { id, alias }, optional parent { id, alias }. Count and return the number of matching devices.
Count IoT devices by month/year and threshold
You are given a table of IoT devices. Each row represents a device with the timestamp when it was added, its status, and operating parameters.
Write an SQL query that returns the number of devices that:
- have a given status (for example, 'RUNNING'),
- have operating parameter root_threshold greater than a given numeric threshold,
- and were added in the month and year specified by a given string in the format 'MM-YYYY'.
For the sample data below and for the specific values:
- status = 'RUNNING'
- threshold = 40
- month/year string = '05-2025'
your query should return the count of such devices as a single row with a single column named device_count.
Table iot_devices
| column | type | constraints |
|---|---|---|
| id | INT | PRIMARY KEY, NOT NULL |
| added_at | TIMESTAMP | NOT NULL |
| status | VARCHAR(20) | NOT NULL |
| rotor_speed | INT | NOT NULL |
| slack | DECIMAL(6,3) | NOT NULL |
| root_threshold | INT | NOT NULL |
| asset_id | INT | NOT NULL |
| asset_alias | VARCHAR(100) | NOT NULL |
| parent_id | INT | |
| parent_alias | VARCHAR(100) |
Sample data:
| added_at | asset_alias | asset_id | id | parent_alias | parent_id | root_threshold | rotor_speed | slack | status |
|---|---|---|---|---|---|---|---|---|---|
| 2025-05-05 10:00:00 | Pump-A | 101 | 1 | Line-1 | 1001 | 50 | 1200 | 0.5 | RUNNING |
| 2025-05-20 15:30:00 | Pump-B | 102 | 2 | Line-1 | 1001 | 45 | 1300 | 0.45 | RUNNING |
| 2025-05-10 09:15:00 | Valve-A | 103 | 3 | Line-2 | 1002 | 35 | 1100 | 0.6 | RUNNING |
| 2025-05-08 12:00:00 | Motor-A | 104 | 4 | Line-1 | 1001 | 60 | 1000 | 0.55 | STOPPED |
| 2025-04-10 08:00:00 | Pump-C | 105 | 5 | Line-3 | 1003 | 55 | 1250 | 0.52 | RUNNING |
| 2024-05-10 08:00:00 | Compressor-A | 106 | 6 | Line-4 | 1004 | 70 | 1400 | 0.48 | RUNNING |
| 2025-05-30 18:45:00 | Valve-B | 107 | 7 | Line-2 | 1002 | 80 | 900 | 0.51 | IDLE |
| 2025-06-01 01:00:00 | Pump-D | 108 | 8 | Line-3 | 1003 | 90 | 1350 | 0.495 | RUNNING |
Expected output
A single-row, single-column result with the count of devices that have status = 'RUNNING', root_threshold > 40, and were added in May 2025 (month/year '05-2025'). For the sample data, devices with id 1 and 2 match, so the count is 2.
| device_count |
|---|
| 2 |
Hints
- Parse the 'MM-YYYY' string to a date using a function like TO_DATE and treat it as the first day of that month.
- Filter added_at between the start of the month (inclusive) and the start of the next month (exclusive) to capture all rows in that month.