Quick Overview

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.

Count IoT devices matching date and threshold

Company: Kneron

Role: Software Engineer

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Online Assessment

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.

Overview: 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.

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.

Tables

iot_devices(id INT, added_at TIMESTAMP, status VARCHAR(20), rotor_speed INT, slack DECIMAL(6,3), root_threshold INT, asset_id INT, asset_alias VARCHAR(100), parent_id INT, parent_alias VARCHAR(100))

Hints

  1. Parse the 'MM-YYYY' string to a date using a function like TO_DATE and treat it as the first day of that month.
  2. 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.

Loading coding console...