Your question is Rolling 7-Day SQL Ranking. Start with the requirements and the three 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.
NASA's Deep Space Network receives daily quality-controlled signal measurements from instruments supporting missions such as Artemis II and Europa Clipper. Write a PostgreSQL query that calculates each instrument's rolling 7-day signal average and ranks instruments within their mission group for each observation date.
nominal observations with non-NULL signal values from February 1 through February 10, 2025.mission_group and date, with the highest average ranked first. Use RANK() so ties share a rank.mission_group, observation_date, instrument_name, rolling_7_day_avg, and group_rank, ordered by group, date, rank, and instrument name.| Column | Type | Description |
|---|---|---|
| mission_idPK | INT | Unique NASA mission identifier |
| mission_name | VARCHAR(100) | Mission name |
| mission_group | VARCHAR(50) | Research group used for ranking |
| Column | Type | Description |
|---|---|---|
| instrument_idPK | INT | Unique instrument identifier |
| mission_id | INT | Owning mission |
| instrument_name | VARCHAR(120) | Instrument name |
| Column | Type | Description |
|---|---|---|
| observation_idPK | INT | Unique observation identifier |
| instrument_id | INT | Instrument producing the observation |
| observation_date | DATE | UTC observation date |
| signal_value | NUMERIC(10,2) | Measured signal value |
| quality_flag | VARCHAR(20) | Measurement quality classification |