| 1 | TB <- pi_{tu.user_id, u.username, u.email, tu.gender, tu.age, tu.weight}
|
|---|
| 2 | (training_users tu bowtie_{tu.user_id = u.user_id} users u)
|
|---|
| 3 |
|
|---|
| 4 | TBM <- TB x M
|
|---|
| 5 | TSY <- sigma_{YEAR(ts.date)=Y}(training_sessions ts)
|
|---|
| 6 | MS0 <- TBM leftouterjoin_{TBM.user_id = ts.training_user_id AND TBM.month_no = MONTH(ts.date)} TSY
|
|---|
| 7 | MS <- gamma_{user_id, month_no;
|
|---|
| 8 | COUNT(ts.training_id)->sessions_count,
|
|---|
| 9 | SUM(COALESCE(ts.duration,0))->total_duration_minutes,
|
|---|
| 10 | SUM(COALESCE(ts.calories,0))->total_calories,
|
|---|
| 11 | AVG(COALESCE(ts.duration,0))->avg_session_duration,
|
|---|
| 12 | AVG(COALESCE(ts.calories,0))->avg_session_calories}(MS0)
|
|---|
| 13 |
|
|---|
| 14 | MR <- omega_{PARTITION BY user_id ORDER BY total_calories DESC, month_no ASC;
|
|---|
| 15 | DENSE_RANK()->peak_calorie_month_rank,
|
|---|
| 16 | DENSE_RANK(PARTITION BY user_id ORDER BY sessions_count DESC, month_no ASC)->peak_sessions_month_rank}(MS)
|
|---|
| 17 |
|
|---|
| 18 | AMS <- sigma_{sessions_count>0}(MS)
|
|---|
| 19 | AMS1 <- omega_{PARTITION BY user_id ORDER BY month_no;
|
|---|
| 20 | ROW_NUMBER()->rn}(AMS)
|
|---|
| 21 | AMS2 <- alpha_{month_no - rn -> grp}(AMS1)
|
|---|
| 22 | LS0 <- gamma_{user_id, grp; COUNT(*)->streak_len}(AMS2)
|
|---|
| 23 | LS <- gamma_{user_id; MAX(streak_len)->longest_active_month_streak}(LS0)
|
|---|
| 24 |
|
|---|
| 25 | AT <- gamma_{user_id;
|
|---|
| 26 | SUM(sessions_count)->annual_sessions,
|
|---|
| 27 | SUM(total_duration_minutes)->annual_duration_minutes,
|
|---|
| 28 | SUM(total_calories)->annual_calories,
|
|---|
| 29 | AVG(total_duration_minutes)->avg_monthly_duration,
|
|---|
| 30 | AVG(total_calories)->avg_monthly_calories,
|
|---|
| 31 | COUNT_IF(sessions_count>0)->active_months,
|
|---|
| 32 | REGR_SLOPE(total_calories, month_no)->calories_trend_slope,
|
|---|
| 33 | REGR_SLOPE(total_duration_minutes, month_no)->duration_trend_slope}(MS)
|
|---|
| 34 |
|
|---|
| 35 | PM <- gamma_{user_id;
|
|---|
| 36 | MAX_IF(month_no, peak_calorie_month_rank=1)->peak_calorie_month_no,
|
|---|
| 37 | MAX_IF(month_no, peak_sessions_month_rank=1)->peak_sessions_month_no}(MR)
|
|---|
| 38 |
|
|---|
| 39 | R0 <- TB bowtie_{TB.user_id=AT.user_id} AT
|
|---|
| 40 | bowtie_{TB.user_id=PM.user_id} PM
|
|---|
| 41 | leftouterjoin_{TB.user_id=LS.user_id} LS
|
|---|
| 42 | R1 <- alpha_{active_months/12.0->consistency_ratio,
|
|---|
| 43 | COALESCE(longest_active_month_streak,0)->longest_active_month_streak_nz,
|
|---|
| 44 | COALESCE(calories_trend_slope,0)->calories_trend_slope_nz,
|
|---|
| 45 | COALESCE(duration_trend_slope,0)->duration_trend_slope_nz}(R0)
|
|---|
| 46 | R <- omega_{ORDER BY annual_calories DESC,
|
|---|
| 47 | active_months DESC,
|
|---|
| 48 | longest_active_month_streak_nz DESC,
|
|---|
| 49 | user_id ASC;
|
|---|
| 50 | DENSE_RANK()->training_annual_rank}(R1)
|
|---|
| 51 |
|
|---|
| 52 |
|
|---|
