Your question is SQL Second Value and OOP Concepts. Start with the requirements and the four tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
AVEVA Industrial Intelligence analysts need to compare the first two valid temperature readings for active UK assets. Write a PostgreSQL query that returns the second chronologically recorded reading for each eligible asset.
quality_code = 'GOOD'.recorded_at, then reading_id as a deterministic tie-breaker, and return assets with at least two valid readings.OPEN.| Column | Type | Description |
|---|---|---|
| site_idPK | INT | Industrial site identifier |
| site_name | VARCHAR(100) | Site name |
| country_code | VARCHAR(2) | ISO-style country code |
| Column | Type | Description |
|---|---|---|
| asset_idPK | INT | Asset identifier |
| asset_name | VARCHAR(100) | Asset name |
| site_id | INT | Owning site identifier |
| asset_type | VARCHAR(40) | Industrial asset type |
| commissioned_date | DATE | Date the asset entered service |
| is_active | BOOLEAN | Whether the asset is operational |
| Column | Type | Description |
|---|---|---|
| reading_idPK | INT | Reading identifier |
| asset_id | INT | Measured asset identifier |
| reading_type | VARCHAR(40) | Measurement type |
| recorded_at | TIMESTAMPTZ | Measurement timestamp |
| reading_value | NUMERIC(10,2) | Measured value |
| quality_code | VARCHAR(20) | Measurement quality status |
| Column | Type | Description |
|---|---|---|
| maintenance_event_idPK | INT | Maintenance event identifier |
| asset_id | INT | Maintained asset identifier |
| event_date | DATE | Maintenance event date |
| maintenance_status | VARCHAR(20) | Maintenance state |