Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.