Odd/Even Measurements
ASKED IN INTERVIEWTesla
Problem Statement
Tesla sensors take multiple measurements throughout the day. To analyze the data, you need to sum the measurements based on their sequence order within each day.
Write an SQL query to calculate the sum of measurements for odd-numbered observations and even-numbered observations for each day.
Rules:
- Within each day, measurements are ordered by measurement_time ascending.
- The first measurement of the day is sequence 1 (Odd), the second is 2 (Even), etc.
- Output columns: measurement_day, odd_sum, and even_sum.
- Results must be sorted by measurement_day in ascending order.
Tests your understanding of
Window Functions, Conditional Aggregation and Sequencing
Input Tables
measurements
| measurement_id(INTEGER) | measurement_value(DECIMAL) | measurement_time(TIMESTAMP) |
|---|---|---|
| 1 | 10.5 | 2023-01-01 09:00:00 |
| 2 | 20 | 2023-01-01 11:00:00 |
| 3 | 5.5 | 2023-01-01 13:00:00 |
| 4 | 15 | 2023-01-02 08:30:00 |
| 5 | 30 | 2023-01-02 10:00:00 |
| 6 | 2 | 2023-01-03 09:00:00 |
| 7 | 8 | 2023-01-03 10:00:00 |
| 8 | 12.5 | 2023-01-03 12:00:00 |
| 9 | 1.5 | 2023-01-03 14:00:00 |
sensors
| sensor_id(INTEGER) | sensor_type(VARCHAR) |
|---|---|
| 1 | Pressure |
| 2 | Temperature |
sensor_mapping
| measurement_id(INTEGER) | sensor_id(INTEGER) |
|---|---|
| 1 | 1 |
| 2 | 2 |
Expected Output
| measurement_day(DATE) | odd_sum(DECIMAL) | even_sum(DECIMAL) |
|---|---|---|
| 2023-01-01 | 16 | 20 |
| 2023-01-02 | 15 | 30 |
| 2023-01-03 | 14.5 | 9.5 |
Tags
MediumASKED IN INTERVIEWWindow FunctionsConditional AggregationSequencing
15-20 min
50%