Back to questions
This is the same question as problem #28 in the SQL Chapter of Ace the Data Science Interview!
Assume you're given a table with measurement values obtained from a Google sensor over multiple days with measurements taken multiple times within each day.
Write a query to calculate the sum of odd-numbered and even-numbered measurements separately for a particular day and display the results in two different columns. Refer to the Example Output below for the desired format.
Effective April 15th, 2023, the question and solution for this question have been revised.
Based on the results,
The dataset you are querying against may have different input & output - this is just an example!
To solve this question, we follow these steps:
Step 1: Ordering and Partitioning
We first order the measurements based on their measurement time and partition them by day using the window function. This helps us establish the order of measurements within each day:
It is important to use in the ORDER BY clause of the window function to ensure that the measurements are ordered within each day based on the actual measurement's time. This will ensure that the row numbering () is accurate and reflects the chronological order of the measurements within each day.
Showing the first 5 rows of output:
Step 2 & 3: Filtering and Summing
To filter for odd and even numbers, we can use the following two methods:
Note: The modulus operator returns the remainder of a division. When we divide an even number by 2, the remainder is always 0, whereas dividing an odd number will result in a non-zero value.
Finally, we can apply the modulus concept to the aggregate function along with the clause, summing over the corresponding .