| [89156c1] | 1 | DB <- pi_{du.user_id, u.username, u.email}
|
|---|
| 2 | (discipline_users du bowtie_{du.user_id = u.user_id} users u)
|
|---|
| 3 |
|
|---|
| 4 | TC <- tasks t leftouterjoin_{t.custom_tracking_id = c.custom_tracking_id} custom_tracking_categories c
|
|---|
| 5 | TM0 <- alpha_{COALESCE(t.discipline_user_id, c.user_id)->owner_user_id}(TC)
|
|---|
| 6 | TM1 <- sigma_{t.discipline_user_id IS NOT NULL OR t.custom_tracking_id IS NOT NULL}(TM0)
|
|---|
| 7 | TM <- gamma_{owner_user_id;
|
|---|
| 8 | COUNT(*)->total_tasks_defined,
|
|---|
| 9 | COUNT_IF(t.custom_tracking_id IS NULL)->core_tasks,
|
|---|
| 10 | COUNT_IF(t.custom_tracking_id IS NOT NULL)->custom_tasks,
|
|---|
| 11 | COUNT_DISTINCT(COALESCE(t.custom_tracking_id,'core'))->task_category_span}(TM1)
|
|---|
| 12 |
|
|---|
| 13 | ADC0 <- sigma_{YEAR(dc.date)=Y}(daily_completion dc)
|
|---|
| 14 | ADC <- alpha_{COALESCE(dc.procent,0)->procent,
|
|---|
| 15 | CASE(procent>=80,1,0)->strong_day}(ADC0)
|
|---|
| 16 |
|
|---|
| 17 | DCS <- gamma_{user_id;
|
|---|
| 18 | COUNT(*)->tracked_days,
|
|---|
| 19 | AVG(procent)->avg_completion_percent,
|
|---|
| 20 | PERCENTILE_CONT_0_5(procent)->median_completion_percent,
|
|---|
| 21 | COUNT_IF(procent=100)->perfect_days,
|
|---|
| 22 | COUNT_IF(procent>=80)->strong_days,
|
|---|
| 23 | STDDEV_SAMP(procent)->completion_variability}(ADC)
|
|---|
| 24 |
|
|---|
| 25 | SDS0 <- sigma_{strong_day=1}(ADC)
|
|---|
| 26 | SDS1 <- omega_{PARTITION BY user_id ORDER BY date; ROW_NUMBER()->rn}(SDS0)
|
|---|
| 27 | SDS2 <- alpha_{date - rn -> grp}(SDS1)
|
|---|
| 28 | LSS0 <- gamma_{user_id, grp; COUNT(*)->streak_len}(SDS2)
|
|---|
| 29 | LSS <- gamma_{user_id; MAX(streak_len)->longest_strong_day_streak}(LSS0)
|
|---|
| 30 |
|
|---|
| 31 | ATE0 <- ADC0 leftouterjoin_{ADC0.daily_completion_id = tdc.daily_completion_id} task_daily_completion tdc
|
|---|
| 32 | ATE <- gamma_{ADC0.user_id; COUNT(tdc.task_id)->completed_task_events}(ATE0)
|
|---|
| 33 |
|
|---|
| 34 | R0 <- DB
|
|---|
| 35 | leftouterjoin_{DB.user_id = TM.owner_user_id} TM
|
|---|
| 36 | leftouterjoin_{DB.user_id = DCS.user_id} DCS
|
|---|
| 37 | leftouterjoin_{DB.user_id = ATE.user_id} ATE
|
|---|
| 38 | leftouterjoin_{DB.user_id = LSS.user_id} LSS
|
|---|
| 39 | R1 <- alpha_{COALESCE(total_tasks_defined,0)->total_tasks_defined_nz,
|
|---|
| 40 | COALESCE(core_tasks,0)->core_tasks_nz,
|
|---|
| 41 | COALESCE(custom_tasks,0)->custom_tasks_nz,
|
|---|
| 42 | COALESCE(task_category_span,0)->task_category_span_nz,
|
|---|
| 43 | COALESCE(tracked_days,0)->tracked_days_nz,
|
|---|
| 44 | COALESCE(avg_completion_percent,0)->avg_completion_percent_nz,
|
|---|
| 45 | COALESCE(median_completion_percent,0)->median_completion_percent_nz,
|
|---|
| 46 | COALESCE(perfect_days,0)->perfect_days_nz,
|
|---|
| 47 | COALESCE(strong_days,0)->strong_days_nz,
|
|---|
| 48 | COALESCE(completion_variability,0)->completion_variability_nz,
|
|---|
| 49 | COALESCE(completed_task_events,0)->completed_task_events_nz,
|
|---|
| 50 | COALESCE(longest_strong_day_streak,0)->longest_strong_day_streak_nz,
|
|---|
| 51 | COALESCE(strong_days/NULLIF(tracked_days,0),0)->strong_day_ratio,
|
|---|
| 52 | (COALESCE(avg_completion_percent,0)*0.45 +
|
|---|
| 53 | COALESCE(longest_strong_day_streak,0)*2.00 +
|
|---|
| 54 | COALESCE(completed_task_events,0)*0.35)->discipline_composite_score}(R0)
|
|---|
| 55 | R <- omega_{ORDER BY discipline_composite_score DESC, user_id ASC;
|
|---|
| 56 | DENSE_RANK()->discipline_annual_rank}(R1)
|
|---|
