logo

Back to questions

Odd and Even Measurements

Medium

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 NameType
measurement_idinteger
measurement_valuedecimal
measurement_timedatetime

Example Input:

measurement_idmeasurement_valuemeasurement_time
1312331109.5107/10/2022 09:00:00
1352111662.7407/10/2022 11:00:00
5235421246.2407/10/2022 13:15:00
1435621124.5007/11/2022 15:00:00
3464621234.1407/11/2022 16:45:00

Example Output:

measurement_dayodd_sumeven_sum
07/10/2022 00:00:002355.751662.74
07/11/2022 00:00:001124.501234.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.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.