irisk-full.sql

with recursive
  TS(this_week, prev_week) as (
    select date('now', '-280 days', 'weekday 1') as this_week,
           date('now', '-280 days', '-7 days', 'weekday 1') as prev_week
     union all
    select date(this_week, '+7 days', 'weekday 1') as this_week,
           date(prev_week, '+7 days', 'weekday 1') as prev_week
      from TS
     where strftime('%s', this_week) < strftime('%s', 'now')),
  SE(week, rDist, rDuration, rTss, bDist, bDuration, bTss, sDist, sDuration, sTss) as (
    select date(TS.start_time, 'unixepoch', 'localtime', '-6 days', 'weekday 1') as week,
           total(TS.run_distance) / 1000.0 as rDist,
           total(TS.run_time) / 3600.0 as rDuration,
           total(TS.run_effort) as rTss,
           total(TS.bike_distance) / 1000.0 as bDist,
           total(TS.bike_time) / 3600.0 as bDuration,
           total(TS.bike_effort) as bTss,
           total(TS.swim_distance) / 1000.0 as sDist,
           total(TS.swim_time) / 3600.0 as sDuration,
           total(TS.swim_effort) as sTss
      from V_TRIATHLON_SESSIONS TS
     where TS.start_time between strftime('%s', date('now', 'localtime', '-280 days', 'weekday 1'), 'utc')
       and strftime('%s', date('now', 'localtime', '+7 days', 'weekday 1'), 'utc')
     group by week),
  LD(this_week, next_week, -- NOTE: next_week will actually be this week in the next iteration!
     rDistLoad, rDurationLoad, rTssLoad,
     bDistLoad, bDurationLoad, bTssLoad,
     sDistLoad, sDurationLoad, sTssLoad) as (
       select date('now', '-280 days', 'weekday 1') as this_week,
              date('now', '-280 days', '+7 days', 'weekday 1') as next_week,
              0, 0, 0,
              0, 0, 0,
              0, 0, 0
        union all
       select date(this_week, '+7 days', 'weekday 1') as this_week,
              date(next_week, '+7 days', 'weekday 1') as next_week,
              0.75 * rDistLoad + 0.25 * ifnull((select rDist from SE where week = next_week), 0),
              0.75 * rDurationLoad + 0.25 * ifnull((select rDuration from SE where week = next_week), 0),
              0.75 * rTssLoad + 0.25 * ifnull((select rTss from SE where week = next_week), 0),
              0.75 * bDistLoad + 0.25 * ifnull((select bDist from SE where week = next_week), 0),
              0.75 * bDurationLoad + 0.25 * ifnull((select bDuration from SE where week = next_week), 0),
              0.75 * bTssLoad + 0.25 * ifnull((select bTss from SE where week = next_week), 0),
              0.75 * sDistLoad + 0.25 * ifnull((select sDist from SE where week = next_week), 0),
              0.75 * sDurationLoad + 0.25 * ifnull((select sDuration from SE where week = next_week), 0),
              0.75 * sTssLoad + 0.25 * ifnull((select sTss from SE where week = next_week), 0)
         from LD
        where strftime('%s', this_week) < strftime('%s', 'now')),
  IR(week,
     rDist, rDistLoad, rDuration, rDurationLoad, rTss, rTssLoad,
     bDist, bDistLoad, bDuration, bDurationLoad, bTss, bTssLoad,
     sDist, sDistLoad, sDuration, sDurationLoad, sTss, sTssLoad) as (
       select TS.this_week as week,
              round(ifnull(SE.rDist, 0), 1) as rDist,
              round(ifnull(LD.rDistLoad, 0), 1) as rDistLoad,
              round(ifnull(SE.rDuration, 0), 2) as rDuration,
              round(ifnull(LD.rDurationLoad, 0), 2) as rDurationLoad,
              round(ifnull(SE.rTss, 0), 0) as rTss,
              round(ifnull(LD.rTssLoad, 0), 0) as rTssLoad,
              round(ifnull(SE.bDist, 0), 0) as bDist,
              round(ifnull(LD.bDistLoad, 0), 0) as bDistLoad,
              round(ifnull(SE.bDuration, 0), 2) as bDuration,
              round(ifnull(LD.bDurationLoad, 0), 2) as bDurationLoad,
              round(ifnull(SE.bTss, 0), 0) as bTss,
              round(ifnull(LD.bTssLoad, 0), 0) as bTssLoad,
              round(ifnull(SE.sDist, 0), 2) as sDist,
              round(ifnull(LD.sDistLoad, 0), 2) as sDistLoad,
              round(ifnull(SE.sDuration, 0), 1) as sDuration,
              round(ifnull(LD.sDurationLoad, 0), 1) as sDurationLoad,
              round(ifnull(SE.sTss, 0), 0) as sTss,
              round(ifnull(LD.sTssLoad, 0), 0) as sTssLoad
         from TS left join SE on TS.this_week = SE.week, LD
        where TS.prev_week = LD.this_week)
select IR.week as week,
       IR.rDist as rDist,
       IR.rDistLoad as rDistLoad,
       round(IR.rDistLoad * 1.2, 1) as rDistYellow,
       round(IR.rDistLoad * 1.6, 1) as rDistRed,
       IR.rDuration as rDuration,
       IR.rDurationLoad as rDurationLoad,
       round(IR.rDurationLoad * 1.2, 1) as rDurationYellow,
       round(IR.rDurationLoad * 1.6, 1) as rDurationRed,
       IR.rTss as rTss,
       IR.rTssLoad as rTssLoad,
       round(IR.rTssLoad * 1.2, 1) as rTssYellow,
       round(IR.rTssLoad * 1.6, 1) as rTssRed,
       IR.bDist as bDist,
       IR.bDistLoad as bDistLoad,
       round(IR.bDistLoad * 1.2, 1) as bDistYellow,
       round(IR.bDistLoad * 1.6, 1) as bDistRed,
       IR.bDuration as bDuration,
       IR.bDurationLoad as bDurationLoad,
       round(IR.bDurationLoad * 1.2, 1) as bDurationYellow,
       round(IR.bDurationLoad * 1.6, 1) as bDurationRed,
       IR.bTss as bTss,
       IR.bTssLoad as bTssLoad,
       round(IR.bTssLoad * 1.2, 1) as bTssYellow,
       round(IR.bTssLoad * 1.6, 1) as bTssRed,
       IR.sDist as sDist,
       IR.sDistLoad as sDistLoad,
       round(IR.sDistLoad * 1.2, 1) as sDistYellow,
       round(IR.sDistLoad * 1.6, 1) as sDistRed,
       IR.sDuration as sDuration,
       IR.sDurationLoad as sDurationLoad,
       round(IR.sDurationLoad * 1.2, 1) as sDurationYellow,
       round(IR.sDurationLoad * 1.6, 1) as sDurationRed,
       IR.sTss as sTss,
       IR.sTssLoad as sTssLoad,
       round(IR.sTssLoad * 1.2, 1) as sTssYellow,
       round(IR.sTssLoad * 1.6, 1) as sTssRed
  from IR
 where strftime('%s', IR.week) > strftime('%s', date('now', '-140 days', 'weekday 1'))
 order by week;