Back to questions
This is the same question as problem #28 in the SQL Chapter of Ace the Data Science Interview!
Assume you are given the table containing measurement values obtained from a Google 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. Refer to the Example Output below for the output format.
Definition:
Column Name | Type |
---|---|
measurement_id | integer |
measurement_value | decimal |
measurement_time | datetime |
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 |
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 |
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.
The dataset you are querying against may have different input & output - this is just an example!
Let's break down this question:
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:
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