SQL for Rolling Productivity Targets

Amazon Services operations leaders need to identify recurring shift patterns whose productivity remains below target. Write a PostgreSQL query that evaluates each shift date against a rolling 30-day window.

Requirements

1. Join shift assignments, productivity records, and shift targets.
2. Calculate productivity as total completed units divided by total labor hours for each site, shift code, and date.
3. For each shift date, calculate the rolling 30-day average productivity, observation count, and count of below-target observations.
4. Return only shift dates with at least three observations in the window where every observation is below target. Round productivity and rolling averages to two decimal places.