SQL Interview Question | Agung Pambudi

The topic of odd and even measurements is discussed in the context of a Google SQL interview question. The key takeaways from this discussion include the utilization of window functions such as ROW_NUMBER(), the concise use of aggregate functions with the CASE clause, and the conversion of datetime type by employing DATE_TRUNC and CAST methods.

SQL ✅

WITH cte AS (
    SELECT
        DATE_TRUNC('day', measurement_time) AS measurement_day,
        measurement_value,
        row_number() OVER (
            PARTITION BY CAST(measurement_time AS DATE)
            ORDER BY
                measurement_time
        ) AS measurement_num
    FROM
        measurements
)
SELECT
    measurement_day,
    SUM(
        CASE WHEN measurement_num % 2 = 1 THEN measurement_value ELSE 0 END
    ) as odd_sum,
    SUM(
        CASE WHEN measurement_num % 2 = 0 THEN measurement_value ELSE 0 END
    ) as even_sum
FROM
    cte
GROUP BY
    measurement_day
ORDER BY
    1

References:

Data Lemur. (n.d.). Odd and Even Measurements. Retrieved from https://datalemur.com/questions/odd-even-measurements