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.
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.
Let's break down this question:
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:
|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:
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 :