Readiness Trend Tiles for Wellness Dashboard

Business Context

You work on the analytics team for a wearable-health platform (think WHOOP/Oura-like) with 3M monthly active users. The product team is building an in-app “Readiness & Wellness Trends” dashboard that helps users understand whether their recovery is improving or declining. The dashboard needs a compact dataset that a BI tool (Looker/Tableau) can visualize as: (a) a daily readiness line chart, (b) a 7-day moving average line, and (c) a “trend” indicator (up/down/flat) comparing recent vs prior periods.

The data model is typical of consumer health: one row per user per day with readiness and a few wellness indicators. Users can have missing days (device not worn), and the dashboard should only consider days with a valid readiness score.

Task

Write a SQL query that produces a daily time series for each user over their most recent 14 days of data, including a 7-day moving average and a trend label that can be directly used for visualization.

Requirements

1. Consider only rows where readiness_score is not NULL.
2. For each user_id, find the latest metric_date available and return the 14 most recent days up to that date (not “last 14 calendar days”).
3. Output one row per user_id and metric_date with:
   • readiness_score
   • sleep_hours
   • resting_hr
   • readiness_7d_ma: 7-row moving average of readiness (current day + previous 6 available days)
4. Add trend_label computed per user as:
   • UP if avg readiness over the most recent 7 rows is >= prior 7 rows + 5 points
   • DOWN if it is <= prior 7 rows - 5 points
   • otherwise FLAT
5. Order results by user_id, then metric_date ascending.