Unpivot Sensor Readings Into Tall Format

Problem

An IoT platform stores readings in a wide format. Table `readings` has one row per device with columns `device_id`, `Alpha`, `Bravo`, `Delta`, `Echo`, where each non-id column is the reading that device produced at the gauge of that name, or NULL if there is no reading there.

Reshape the data into a tall format with exactly three columns: `device_id`, `gauge`, and `value`. Produce one row per (device, gauge) where a reading actually exists; skip any combination whose value is NULL.

Return the result in any order.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 8 rows with the columns device_id, gauge, value.

Starter code (SQL)

SELECT *
FROM readings;

Related SQL questions

• 3-Consecutive-Week Marketing StreakHard
• 30-Day Rolling Average Trading VolumeHard
• 7-Day Active User Rolling MetricHard
• 7-Day Revenue Moving AverageHard
• Always Mid-Pack BakersHard
• Annual Energy Output Growth by Panel ModelHard