Rolling 7-Day SQL Ranking

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.

Requirements

1. Join missions, instruments, and observations.
2. Include only nominal observations with non-NULL signal values from February 1 through February 10, 2025.
3. Aggregate multiple observations for an instrument on the same date before calculating the rolling average. Treat the rolling window as the current date and preceding six calendar days.
4. Rank instruments by rolling average within each mission_group and date, with the highest average ranked first. Use RANK() so ties share a rank.
5. Return mission_group, observation_date, instrument_name, rolling_7_day_avg, and group_rank, ordered by group, date, rank, and instrument name.