Question: Odd and Even Measurements
"You have a table of sensor measurements. Separate the data into odd and even measurement IDs and calculate the average measurement value for each group."

Step-by-Step Explanation:

You have a table called sensor_measurements with:

measurement_id (number assigned to each measurement)
measurement_value (value recorded by the sensor)

You need to:

Separate measurements into two groups:
- Odd measurement IDs (like 1, 3, 5, 7, etc.)
- Even measurement IDs (like 2, 4, 6, 8, etc.)
Calculate the average measurement value for each group.

Sample Data (`sensor_measurements`):

measurement_id	measurement_value
1	10.5
2	12.0
3	11.5
4	13.0
5	9.5

Step 1: Identify Odd and Even measurement_id

We can check if a number is odd or even by using:

MOD(measurement_id, 2)
- If result = 0 → Even number
- If result = 1 → Odd number

Example:

MOD(1,2) = 1 → Odd
MOD(2,2) = 0 → Even
MOD(3,2) = 1 → Odd
MOD(4,2) = 0 → Even

Step 2: Group Measurements into "Odd" and "Even"

Create two groups:

Group where MOD(measurement_id,2) = 0 → Even
Group where MOD(measurement_id,2) = 1 → Odd

Step 3: Calculate the Average for Each Group

For each group (odd and even), calculate the average of measurement_value.

For example:

Odd IDs (1, 3, 5):
- Values: 10.5, 11.5, 9.5
- Average = (10.5 + 11.5 + 9.5) ÷ 3 = 10.5
Even IDs (2, 4):
- Values: 12.0, 13.0
- Average = (12.0 + 13.0) ÷ 2 = 12.5

SQL Query:

SELECT 
  CASE 
    WHEN MOD(measurement_id, 2) = 0 THEN 'Even'
    ELSE 'Odd'
  END AS group_type,
  AVG(measurement_value) AS average_value
FROM 
  sensor_measurements
GROUP BY 
  group_type;

Explanation of SQL:

CASE WHEN MOD(measurement_id,2) = 0 THEN 'Even' ELSE 'Odd' END
→ This labels each row as "Odd" or "Even".
AVG(measurement_value) calculates the average for each group.
GROUP BY group_type groups by "Odd" and "Even".