Analyzing Time Series Data with FIRST_VALUE and LAST_VALUE

Many applications, such as financial analysis, sensor data monitoring, and website traffic tracking, involve time series data. This challenge focuses on using the FIRST_VALUE and LAST_VALUE window functions in SQL to efficiently extract the first and last values within a defined time window for each record in a dataset. This is crucial for calculating trends, identifying anomalies, and performing other time-based analyses.

Problem Description

You are given a table named sensor_readings containing sensor data. The table has the following columns:

• sensor_id (INTEGER): Unique identifier for the sensor.
• timestamp (TIMESTAMP): The timestamp of the reading.
• value (NUMERIC): The sensor reading value.

Your task is to write a SQL query that retrieves the sensor_id, timestamp, value, the first_value of the value for each sensor_id within a rolling 30-minute window, and the last_value of the value for each sensor_id within the same rolling 30-minute window. The window should be defined by the timestamp column. The results should be ordered by sensor_id and then by timestamp.

Key Requirements:

• Use FIRST_VALUE to determine the initial value within the 30-minute window for each sensor.
• Use LAST_VALUE to determine the final value within the 30-minute window for each sensor.
• The window function should consider only readings for the same sensor_id.
• Handle cases where a sensor might have fewer than 30 minutes of data. In such cases, the FIRST_VALUE and LAST_VALUE should reflect the first and last readings available for that sensor.

Expected Behavior:

The query should return a table with the following columns:

• sensor_id (INTEGER)
• timestamp (TIMESTAMP)
• value (NUMERIC)
• first_value (NUMERIC) - The first value for the sensor within the 30-minute window.
• last_value (NUMERIC) - The last value for the sensor within the 30-minute window.

Examples

Example 1:

Input:
sensor_readings table:
sensor_id | timestamp             | value
----------|-----------------------|-------
1         | 2024-10-26 10:00:00 | 10
1         | 2024-10-26 10:15:00 | 12
1         | 2024-10-26 10:30:00 | 15
2         | 2024-10-26 10:05:00 | 20
2         | 2024-10-26 10:20:00 | 22

Output:
sensor_id | timestamp             | value | first_value | last_value
----------|-----------------------|-------|-------------|------------
1         | 2024-10-26 10:00:00 | 10    | 10          | 15
1         | 2024-10-26 10:15:00 | 12    | 10          | 15
1         | 2024-10-26 10:30:00 | 15    | 10          | 15
2         | 2024-10-26 10:05:00 | 20    | 20          | 22
2         | 2024-10-26 10:20:00 | 22    | 20          | 22

Explanation: For sensor 1, the 30-minute window is from 10:00:00 to 10:30:00. The first value is 10, and the last value is 15. For sensor 2, the 30-minute window is from 10:05:00 to 10:20:00. The first value is 20, and the last value is 22.

Example 2:

Input:
sensor_readings table:
sensor_id | timestamp             | value
----------|-----------------------|-------
1         | 2024-10-26 10:00:00 | 10
1         | 2024-10-26 10:15:00 | 12

Output:
sensor_id | timestamp             | value | first_value | last_value
----------|-----------------------|-------|-------------|------------
1         | 2024-10-26 10:00:00 | 10    | 10          | 12
1         | 2024-10-26 10:15:00 | 12    | 10          | 12

Explanation: Sensor 1 only has two readings within a 30-minute window. The first value is 10, and the last value is 12.

Constraints

• The timestamp column will always be in a valid timestamp format.
• The sensor_id will be a positive integer.
• The value will be a numeric value.
• The database system supports FIRST_VALUE and LAST_VALUE window functions.
• The query should be efficient enough to handle a table with up to 1,000,000 rows.

Notes

• Consider using the OVER clause with a PARTITION BY clause to define the window for each sensor.
• The window frame clause is not required for this problem, as we are looking for the absolute first and last values within the window.
• The 30-minute window is relative to each timestamp. It's not a fixed window starting at a specific time.
• Focus on using FIRST_VALUE and LAST_VALUE correctly within the window function. The ordering of the results is important.
• Test your solution with various edge cases, such as sensors with very few readings or sensors with readings spread out over a long period.