Odd and Even Measurements
This is the same question as problem #28 in the SQL Chapter of Ace the Data Science Interview!
Assume you are given the table below containing measurement values obtained from a sensor over several days. Measurements are taken several times within a given day.
Write a query to obtain the sum of the odd-numbered and even-numbered measurements on a particular day, in two different columns.
Note that the 1st, 3rd, 5th measurements within a day are considered odd-numbered measurements and the 2nd, 4th, 6th measurements are even-numbered measurements.
Table:
| Column Name | Type |
|---|---|
| measurement_id | integer |
| measurement_value | decimal |
| measurement_time | datetime |
Example Input:
| measurement_id | measurement_value | measurement_time |
|---|---|---|
| 131233 | 1109.51 | 07/10/2022 09:00:00 |
| 135211 | 1662.74 | 07/10/2022 11:00:00 |
| 523542 | 1246.24 | 07/10/2022 13:15:00 |
| 143562 | 1124.50 | 07/11/2022 15:00:00 |
| 346462 | 1234.14 | 07/11/2022 16:45:00 |
Example Output:
| measurement_day | odd_sum | even_sum |
|---|---|---|
| 07/10/2022 00:00:00 | 2355.75 | 1662.74 |
| 07/11/2022 00:00:00 | 1124.50 | 1234.14 |
Explanation
On 07/11/2022, there are only two measurements. In chronological order, the first measurement (odd-numbered) is 1124.50, and the second measurement(even-numbered) is 1234.14.
Solution
Let's break down this question:
- Order the measurements by 1, 2, 3 based on the measurement's time and partition by day
- Find for odd and even measurements using the output in no. 1.
- Add all the odd and even measurement values and output as two columns.
Step 1
We have to establish which measurements are odd numbered and even numbered. We can do so by using window function and partition the measurements based on the casted to obtain the order of measurement within each day.
Showing the first 5 rows of output:
| measurement_time | measurement_day | measurement_value | measurement_num |
|---|---|---|---|
| 07/10/2022 09:00:00 | 07/10/2022 00:00:00 | 1109.51 | 1 |
| 07/10/2022 11:00:00 | 07/10/2022 00:00:00 | 1662.74 | 2 |
| 07/10/2022 14:30:00 | 07/10/2022 00:00:00 | 1246.24 | 3 |
| 07/11/2022 13:15:00 | 07/11/2022 00:00:00 | 1124.50 | 1 |
| 07/11/2022 15:00:00 | 07/11/2022 00:00:00 | 1234.14 | 2 |
We pull the field just to show you how the casting of to day works.
Bear in mind that you should use in the clause in the window function to ensure that the measurements are ordered within each day based on the actual measurement's time.
Step 2 & 3
Next, we filter for odd and even numbers by using these two methods:
- Using by checking whether the result is 1 for odds or with result as 1 for even number.
- Using to find for odd result and for even results.
P.S. The modulus or modulo operator, % returns the remainder of a division. When we divide an even number with 2, the remainder is always 0, whereas odd number division will result in a non-zero value.
Finally, we apply the above modulus concept onto the statement, ming over the corresponding :
Results:
| measurement_day | odd_sum | even_sum |
|---|---|---|
| 07/10/2022 00:00:00 | 2355.75 | 1662.74 |
| 07/11/2022 00:00:00 | 2377.12 | 2480.70 |
| 07/12/2022 00:00:00 | 2903.40 | 1244.30 |
Solution:
PostgreSQL 14