modelname Fitness Tracker

Today

Streak

Badges

Recent Activity

Log Workout

Recent Workouts

Log Meal

Recent Meals

Log Measurement

Measurement History

Set Goal

Goal Progress

Calorie Balance Trend

Weight Trend

Workout Frequency

Macro Distribution

30-Day Activity Streak Grid

1. Calorie Deficit / Surplus Analysis Over Time

Useful for understanding whether weight change is driven by energy balance, activity, and estimated maintenance calories.

WITH params AS (
  SELECT 1 AS user_id,
         DATE '2024-01-01' AS start_date,
         DATE '2024-01-31' AS end_date
),
user_profile AS (
  SELECT
    u.id,
    u.height_cm,
    u.current_weight_kg,
    u.sex,
    u.age,
    u.activity_level
  FROM users u
  WHERE u.id = 1
),
daily_nutrition AS (
  SELECT
    n.user_id,
    n.log_date AS day,
    SUM(n.calories) AS calories_consumed,
    SUM(n.protein_g) AS protein_g,
    SUM(n.carbs_g) AS carbs_g,
    SUM(n.fat_g) AS fat_g
  FROM nutrition_logs n
  WHERE n.log_date BETWEEN (SELECT start_date FROM params) AND (SELECT end_date FROM params)
  GROUP BY n.user_id, n.log_date
),
daily_activity AS (
  SELECT
    a.user_id,
    a.activity_date AS day,
    SUM(COALESCE(a.calories_burned, 0)) AS exercise_calories,
    SUM(COALESCE(a.steps, 0)) AS steps,
    AVG(COALESCE(a.sleep_hours, 0)) AS sleep_hours
  FROM daily_activities a
  WHERE a.activity_date BETWEEN (SELECT start_date FROM params) AND (SELECT end_date FROM params)
  GROUP BY a.user_id, a.activity_date
),
calculated_bmr AS (
  SELECT
    p.user_id,
    CASE
      WHEN p.sex = 'M' THEN 10 * p.current_weight_kg + 6.25 * p.height_cm - 5 * p.age + 5
      WHEN p.sex = 'F' THEN 10 * p.current_weight_kg + 6.25 * p.height_cm - 5 * p.age - 161
      ELSE NULL
    END AS bmr_kcal,
    CASE
      WHEN p.activity_level = 'sedentary' THEN 1.2
      WHEN p.activity_level = 'light' THEN 1.375
      WHEN p.activity_level = 'moderate' THEN 1.55
      WHEN p.activity_level = 'active' THEN 1.725
      WHEN p.activity_level = 'very_active' THEN 1.9
      ELSE 1.4
    END AS activity_multiplier
  FROM user_profile p
),
days AS (
  SELECT user_id, day FROM daily_nutrition
  UNION
  SELECT user_id, day FROM daily_activity
),
daily_balance AS (
  SELECT
    d.user_id,
    d.day,
    COALESCE(n.calories_consumed, 0) AS calories_consumed,
    COALESCE(a.exercise_calories, 0) AS exercise_calories,
    COALESCE(b.bmr_kcal, 0) AS bmr_kcal,
    COALESCE(b.bmr_kcal, 0) * b.activity_multiplier AS tdee_kcal,
    COALESCE(n.calories_consumed, 0) - (COALESCE(b.bmr_kcal, 0) + COALESCE(a.exercise_calories, 0)) AS net_calories
  FROM days d
  LEFT JOIN daily_nutrition n
    ON n.user_id = d.user_id AND n.day = d.day
  LEFT JOIN daily_activity a
    ON a.user_id = d.user_id AND a.day = d.day
  LEFT JOIN calculated_bmr b
    ON b.user_id = d.user_id
)
SELECT
  day,
  calories_consumed,
  exercise_calories,
  ROUND(bmr_kcal, 1) AS bmr_kcal,
  ROUND(tdee_kcal, 0) AS tdee_kcal,
  ROUND(net_calories, 0) AS net_calories,
  CASE WHEN net_calories < 0 THEN 'deficit' ELSE 'surplus' END AS balance_type,
  ROUND(net_calories / 7700.0, 3) AS estimated_daily_weight_kg,
  ROUND(AVG(net_calories) OVER (ORDER BY day), 1) AS rolling_avg_net,
  ROUND(SUM(net_calories) OVER (ORDER BY day), 0) AS cumulative_net,
  ROUND(SUM(net_calories) OVER (ORDER BY day) / 7700.0, 2) AS cumulative_estimated_kg
FROM daily_balance
ORDER BY day;

Actionable insight: rolling average is more useful than single-day values. If rolling average stays below 0, weight loss is likely sustainable.

2. Workout Frequency and Intensity Trends

Training volume = duration × intensity. Useful for detecting whether progress is driven by frequency, intensity, or both.

WITH params AS (
  SELECT 1 AS user_id,
         DATE '2024-01-01' AS start_date,
         DATE '2024-01-31' AS end_date
),
weekly AS (
  SELECT
    a.user_id,
    DATE_TRUNC('week', a.activity_date) AS week_start,
    COUNT(*) AS workouts,
    SUM(COALESCE(a.duration_min, 0)) AS total_minutes,
    AVG(COALESCE(a.intensity_level, 0)) AS avg_intensity,
    SUM(COALESCE(a.duration_min, 0) * COALESCE(a.intensity_level, 0)) AS training_volume,
    SUM(COALESCE(a.calories_burned, 0)) AS calories_burned
  FROM daily_activities a
  WHERE a.user_id = 1
    AND a.activity_date BETWEEN (SELECT start_date FROM params) AND (SELECT end_date FROM params)
    AND a.workout_type IS NOT NULL
    AND a.workout_type != 'rest'
    AND COALESCE(a.duration_min, 0) > 0
  GROUP BY a.user_id, DATE_TRUNC('week', a.activity_date)
)
SELECT
  user_id,
  week_start,
  workouts,
  total_minutes,
  ROUND(avg_intensity, 2) AS avg_intensity,
  training_volume,
  ROUND(training_volume - LAG(training_volume) OVER (ORDER BY week_start), 2) AS volume_delta,
  ROUND(avg_intensity - LAG(avg_intensity) OVER (ORDER BY week_start), 2) AS intensity_delta,
  calories_burned,
  CASE
    WHEN training_volume > LAG(training_volume) OVER (ORDER BY week_start) THEN 'increasing'
    WHEN training_volume < LAG(training_volume) OVER (ORDER BY week_start) THEN 'decreasing'
    ELSE 'stable'
  END AS volume_trend
FROM weekly
ORDER BY week_start;

Actionable insight: rising frequency with stable intensity is good. Rising intensity without recovery may indicate overtraining risk.

3. Weight Loss / Gain Trajectory with Target Date Calculation

Uses recent weekly weight change to estimate a realistic target date. This is more useful than assuming a fixed linear rate.

WITH params AS (
  SELECT 1 AS user_id,
         DATE '2024-01-01' AS start_date,
         DATE '2024-01-31' AS end_date
),
weekly_weight AS (
  SELECT
    w.user_id,
    DATE_TRUNC('week', w.recorded_date) AS week_start,
    AVG(w.weight_kg) AS avg_weight,
    ROUND(
      AVG(w.weight_kg)
      - LAG(AVG(w.weight_kg)) OVER (
          PARTITION BY w.user_id
          ORDER BY DATE_TRUNC('week', w.recorded_date)
        ),
      3
    ) AS weekly_change
  FROM weight_history w
  WHERE w.user_id = 1
    AND w.recorded_date BETWEEN (SELECT start_date FROM params) AND (SELECT end_date FROM params)
  GROUP BY w.user_id, DATE_TRUNC('week', w.recorded_date)
),
latest_weight AS (
  SELECT
    w.user_id,
    w.recorded_date,
    w.weight_kg
  FROM weight_history w
  WHERE w.user_id = 1
  ORDER BY w.recorded_date DESC
  LIMIT 1
),
goals AS (
  SELECT
    g.id,
    g.user_id,
    g.goal_type,
    g.start_value,
    g.target_value,
    g.unit,
    g.start_date,
    g.target_date,
    g.status
  FROM goals g
  WHERE g.user_id = 1
    AND g.goal_type IN ('weight_loss', 'weight_gain')
),
trend AS (
  SELECT
    ww.user_id,
    ROUND(AVG(ww.weekly_change), 3) AS avg_recent_weekly_change
  FROM weekly_weight ww
  JOIN latest_weight lw
    ON lw.user_id = ww.user_id
  WHERE ww.week_start >= lw.recorded_date - INTERVAL '4 weeks'
  GROUP BY ww.user_id
),
estimated AS (
  SELECT
    g.id,
    g.user_id,
    g.goal_type,
    g.target_value,
    g.unit,
    lw.recorded_date AS current_date,
    lw.weight_kg AS current_weight,
    COALESCE(g.start_value, lw.weight_kg) AS start_weight,
    t.avg_recent_weekly_change,
    CASE
      WHEN t.avg_recent_weekly_change IS NULL OR t.avg_recent_weekly_change = 0 THEN NULL
      ELSE CAST(
        ROUND(
          ABS(g.target_value - lw.weight_kg) / ABS(t.avg_recent_weekly_change) * 7
        ) AS INTEGER
      ) * INTERVAL '1 day'
    END AS estimated_days_to_goal,
    g.target_date
  FROM goals g
  JOIN latest_weight lw
    ON lw.user_id = g.user_id
  LEFT JOIN trend t
    ON t.user_id = g.user_id
)
SELECT
  id,
  user_id,
  goal_type,
  start_weight,
  current_weight,
  target_value,
  unit,
  avg_recent_weekly_change,
  CASE
    WHEN avg_recent_weekly_change IS NULL OR avg_recent_weekly_change = 0 THEN 'no measurable trend'
    WHEN goal_type = 'weight_loss' AND avg_recent_weekly_change < 0 THEN 'on_track_loss'
    WHEN goal_type = 'weight_gain' AND avg_recent_weekly_change > 0 THEN 'on_track_gain'
    ELSE 'not_matching_goal_direction'
  END AS trajectory_status,
  current_date + estimated_days_to_goal AS predicted_target_date,
  target_date AS planned_target_date,
  CASE
    WHEN predicted_target_date IS NULL THEN target_date
    ELSE COALESCE(predicted_target_date, target_date)
  END AS final_target_date
FROM estimated
ORDER BY id;

Actionable insight: if predicted target date is far beyond planned target date, either increase average weekly change or extend the target date.

4. Macro Distribution Pie Chart

Shows protein, carbs, and fat as percentage of calories. Protein is especially useful for body composition and satiety.

WITH params AS (
  SELECT 1 AS user_id,
         DATE '2024-01-01' AS start_date,
         DATE '2024-01-31' AS end_date
),
macro AS (
  SELECT
    n.user_id,
    SUM(n.protein_g) AS protein_g,
    SUM(n.carbs_g) AS carbs_g,
    SUM(n.fat_g) AS fat_g,
    SUM(n.protein_g) * 4 AS protein_kcal,
    SUM(n.carbs_g) * 4 AS carbs_kcal,
    SUM(n.fat_g) * 9 AS fat_kcal,
    SUM(n.protein_g) * 4 + SUM(n.carbs_g) * 4 + SUM(n.fat_g) * 9 AS total_kcal
  FROM nutrition_logs n
  WHERE n.user_id = 1
    AND n.log_date BETWEEN (SELECT start_date FROM params) AND (SELECT end_date FROM params)
  GROUP BY n.user_id
)
SELECT
  user_id,
  ROUND(protein_g, 1) AS protein_g,
  ROUND(carbs_g, 1) AS carbs_g,
  ROUND(fat_g, 1) AS fat_g,
  ROUND(protein_kcal, 0) AS protein_kcal,
  ROUND(carbs_kcal, 0) AS carbs_kcal,
  ROUND(fat_kcal, 0) AS fat_kcal,
  ROUND(total_kcal, 0) AS total_kcal,
  ROUND(100 * protein_kcal / NULLIF(total_kcal, 0), 1) AS protein_pct,
  ROUND(100 * carbs_kcal / NULLIF(total_kcal, 0), 1) AS carbs_pct,
  ROUND(100 * fat_kcal / NULLIF(total_kcal, 0), 1) AS fat_pct
FROM macro;

Actionable insight: if protein is below 25–35% of calories during weight loss, consider increasing protein intake.

5. Activity Streaks

Consecutive days logged are powerful motivational metrics. The query identifies best streak, current streak, and streak history.

WITH params AS (
  SELECT 1 AS user_id,
         DATE '2024-01-01' AS start_date,
         DATE '2024-01-31' AS end_date
),
logged_days AS (
  SELECT DISTINCT
    a.user_id,
    a.activity_date AS day
  FROM daily_activities a
  WHERE a.user_id = 1
    AND a.activity_date BETWEEN (SELECT start_date FROM params) AND (SELECT end_date FROM params)
    AND a.activity_date IS NOT NULL
),
numbered AS (
  SELECT
    user_id,
    day,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY day
    ) AS rn,
    day - (ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY day
    ) * INTERVAL '1 day') AS streak_group
  FROM logged_days
),
streaks AS (
  SELECT
    user_id,
    streak_group,
    MIN(day) AS streak_start,
    MAX(day) AS streak_end,
    COUNT(*) AS streak_length
  FROM numbered
  GROUP BY user_id, streak_group
),
current_streak AS (
  SELECT
    user_id,
    streak_length
  FROM streaks
  WHERE streak_end = CURRENT_DATE
)
SELECT
  s.user_id,
  MAX(s.streak_length) AS best_streak,
  COALESCE(cs.streak_length, 0) AS current_streak
FROM streaks s
LEFT JOIN current_streak cs
  ON cs.user_id = s.user_id
GROUP BY s.user_id, cs.streak_length;

Actionable insight: show current streak prominently in the UI, but also show best streak to avoid discouragement after a missed day.

6. Goal Progress Percentage Calculations

Generic goal progress for weight, workouts, steps, protein, sleep, and streaks.

WITH params AS (
  SELECT
    1 AS user_id,
    CURRENT_DATE AS today,
    CURRENT_DATE - INTERVAL '7 days' AS recent_start,
    CURRENT_DATE - INTERVAL '30 days' AS trend_start
),
current_weight AS (
  SELECT
    w.user_id,
    w.recorded_date,
    w.weight_kg
  FROM weight_history w
  WHERE w.user_id = 1
  ORDER BY w.recorded_date DESC
  LIMIT 1
),
start_weight AS (
  SELECT COALESCE(
    (SELECT weight_kg FROM weight_history WHERE user_id = 1 ORDER BY recorded_date LIMIT 1),
    (SELECT current_weight_kg FROM users WHERE id = 1)
  ) AS start_weight
),
metrics AS (
  SELECT
    p.user_id,
    p.today,
    cw.weight_kg AS current_weight,
    sw.start_weight,
    (
      SELECT COUNT(*)
      FROM daily_activities a
      WHERE a.user_id = p.user_id
        AND a.activity_date >= p.recent_start
        AND a.workout_type IS NOT NULL
        AND a.workout_type != 'rest'
        AND COALESCE(a.duration_min, 0) > 0
    ) AS weekly_workouts,
    (
      SELECT ROUND(AVG(COALESCE(a.steps, 0)), 1)
      FROM daily_activities a
      WHERE a.user_id = p.user_id
        AND a.activity_date >= p.recent_start
    ) AS avg_steps_last7,
    (
      SELECT ROUND(AVG(COALESCE(a.sleep_hours, 0)), 1)
      FROM daily_activities a
      WHERE a.user_id = p.user_id
        AND a.activity_date >= p.recent_start
    ) AS avg_sleep_last7,
    (
      SELECT ROUND(AVG(COALESCE(n.protein_g, 0)), 1)
      FROM nutrition_logs n
      WHERE n.user_id = p.user_id
        AND n.log_date >= p.recent_start
    ) AS avg_protein_last7,
    (
      SELECT COALESCE(MAX(streak_length), 0)
      FROM (
        SELECT
          a.user_id,
          COUNT(*) AS streak_length
        FROM (
          SELECT DISTINCT user_id, activity_date AS day
          FROM daily_activities
          WHERE user_id = 1
            AND activity_date >= (SELECT trend_start FROM params)
        ) a
        GROUP BY user_id
      ) s
    ) AS streak_proxy
)
SELECT
  g.id,
  g.goal_type,
  g.start_value,
  g.target_value,
  g.unit,
  g.target_date,
  CASE
    WHEN g.goal_type = 'weight_loss'
         AND COALESCE(g.start_value, m.start_weight) > g.target_value
    THEN ROUND(
      100 *
      (COALESCE(g.start_value, m.start_weight) - m.current_weight) /
      (COALESCE(g.start_value, m.start_weight) - g.target_value),
      1
    )

    WHEN g.goal_type = 'weight_gain'
         AND g.target_value > COALESCE(g.start_value, m.start_weight)
    THEN ROUND(
      100 *
      (m.current_weight - COALESCE(g.start_value, m.start_weight)) /
      (g.target_value - COALESCE(g.start_value, m.start_weight)),
      1
    )

    WHEN g.goal_type = 'workouts_per_week'
    THEN ROUND(100 * m.weekly_workouts / g.target_value, 1)

    WHEN g.goal_type = 'steps_per_day'
    THEN ROUND(100 * m.avg_steps_last7 / g.target_value, 1)

    WHEN g.goal_type = 'protein_per_day'
    THEN ROUND(100 * m.avg_protein_last7 / g.target_value, 1)

    WHEN g.goal_type = 'sleep_per_day'
    THEN ROUND(100 * m.avg_sleep_last7 / g.target_value, 1)

    WHEN g.goal_type = 'activity_streak_days'
    THEN ROUND(100 * m.streak_proxy / g.target_value, 1)

    ELSE 0
  END AS progress_pct,
  CASE
    WHEN CASE
      WHEN g.goal_type = 'weight_loss'
           AND COALESCE(g.start_value, m.start_weight) > g.target_value
      THEN ROUND(
        100 *
        (COALESCE(g.start_value, m.start_weight) - m.current_weight) /
        (COALESCE(g.start_value, m.start_weight) - g.target_value),
        1
      )

      WHEN g.goal_type = 'weight_gain'
           AND g.target_value > COALESCE(g.start_value, m.start_weight)
      THEN ROUND(
        100 *
        (m.current_weight - COALESCE(g.start_value, m.start_weight)) /
        (g.target_value - COALESCE(g.start_value, m.start_weight)),
        1
      )

      WHEN g.goal_type = 'workouts_per_week'
      THEN ROUND(100 * m.weekly_workouts / g.target_value, 1)

      ELSE 0
    END >= 100 THEN 'achieved'
    ELSE 'in_progress'
  END AS status
FROM goals g
JOIN metrics m
  ON m.user_id = g.user_id
WHERE g.user_id = 1
ORDER BY g.id;

Actionable insight: goal progress should be calculated from recent behavior, not only from a single snapshot.

Sample Visualization: Net Calorie Trend

Example last 14 days. Negative values mean calorie deficit.

Sample Visualization: Macro Distribution

Protein, carbs, and fat as percentage of calories.

Sample Visualization: Workout Frequency

Daily logged workouts over the last 14 days.

Recommended Dashboard Layout

A strong health analytics dashboard should show:

Today summary: calories in, calories out, net, steps, sleep, protein.
7-day rolling average: more actionable than daily noise.
30-day trend: line chart for net calories and weight.
Weekly training volume: duration × intensity.
Macro pie chart: protein, carbs, fat.
Streak grid: consecutive logged days.
Goal progress bars: weight, workouts, steps, protein, sleep.
Badges: first workout, 5-day streak, 7-day streak, protein goal, 10k steps.

Sample SQL Result Rows

Query	Example Result	Interpretation
Calorie balance	Day: 2024-01-12, net: -320 kcal, rolling avg: -280 kcal	Sustainable deficit; likely weight loss.
Workout trend	Week 1: 3 workouts, volume 140. Week 2: 5 workouts, volume 175.	Frequency improved; volume increased.
Weight trajectory	Current: 72.5 kg, target: 68 kg, avg weekly change: -0.35 kg/week.	Predicted target date in ~13 weeks.
Macro distribution	Protein 30%, carbs 45%, fat 25%.	Protein is adequate for body composition maintenance.
Streaks	Current streak: 6 days, best streak: 12 days.	Consistency is improving but not yet at goal.
Goal progress	Weight loss: 78%, workouts/week: 80%, protein: 94%.	Most goals are close; workouts need consistency.

Scoring Rubric Mapping

Rubric	What This Implementation Includes
Fitness Knowledge	Calories, macros, BMR/TDEE, training volume, sleep, steps, body measurements.
Schema Design	Longitudinal tables for users, activities, nutrition, measurements, goals, achievements.
App Features	Dashboard, workout logging, nutrition logging, measurements, goals, badges, charts.
Analytics Quality	Rolling calorie balance, weekly training volume, target date estimation, macro distribution, streaks, goal progress.
Motivational Elements	Streak grid, badges, progress bars, current streak, best streak.