The avatar for @tashapiro
Tanya Shapiro
Independent Data Analytics Consultant, passionate about data visualization and storytelling · she/her 🏳️‍🌈
Public
Edited
Jun 14, 2023
5 forks
12 stars
3
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
db
select d. *
,dense_rank() over (partition by month_num order by week) as month_week
,substring(day_week, 1,3) as day_abbr
,coalesce(w.workouts,0) as workouts
,case when w.workouts is null then 'N' else 'Y' end as did_workout
,w.workout_list
,case
when w.workouts is null then 'N'
when ((w.cardio_workouts>0 and w.strength_workouts>0) or w.bootcamp_workouts>0) then 'Cardio & Strength'
when w.cardio_workouts >0 then 'Cardio'
when w.strength_workouts>0 then 'Strength'
else 'Mobility'
end as workout_type
-- create row number based on month for facetting
,case
when month_num < 5 then 1
when month_num <9 then 2
when month_num <13 then 3
end as row_num
-- create columnnumber based on month for facetting
,case
when month_num IN (1,5,9) then 1
when month_num IN (2,6,10) then 2
when month_num IN (3,7,11) then 3
when month_num IN (4,8,12) then 4
end as col_num
from days d
left join (
--- active days aggregated
select
created_at as workout_date
,string_agg(distinct(fitness_discipline), ', ') as workout_list
,count(*) as workouts
,count(case when fitness_discipline = 'Strength' then id end) as strength_workouts
,count(case when fitness_discipline IN ('Cardio','Running','Walking','Cycling') then id end) as cardio_workouts
,count(case when fitness_discipline IN ('Yoga','Stretching') then id end) as mobility_workouts
,count(case when fitness_discipline LIKE '%Bootcamp' then id end) as bootcamp_workouts
from workouts
where fitness_discipline NOT IN ('Meditation','NA')
group by 1
) w on w.workout_date = d.date
Insert cell
db
select d. *
,w.workout_list
from days d
inner join (
select
created_at as date
,string_agg(fitness_discipline, ', ') as workout_list
from workouts
group by 1
) w on d.date = w.date
Insert cell
db = DuckDBClient.of({
workouts: workouts,
days: ref_date
})
Insert cell
Insert cell
days = d3.timeDays(new Date(selectYear, 0, 1), new Date(selectYear, 11, 32)).map(function(date) {
return { date: d3.timeFormat("%Y-%m-%d")(date) };
});
Insert cell
Insert cell
days
select d. *
,dense_rank() over (partition by month_num order by week) as month_week
,substring(day_week, 1,3) as day_abbr
from(
select
case
when extract(week from date::date) > 10 and date_part('month',date::date)=1 then 1
else extract(week from date::date)+1
end as week
,date::date as date
,date_part('day',date::date) month_day
,dayname(date::date) as day_week
,monthname(date::date) as month_name
,date_part('month', date::date) as month_num
from days
) d
Insert cell
peloton-data@2.csv
Type Table, then Shift-Enter. Ctrl-space for more options.

Insert cell

One platform to build and deploy the best data apps

Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.
Learn more