spencerroan’s gists

spencerroan / weekly_activity_company_percent.sql

Created June 23, 2016 20:32


	with hydrated_activities as (
	select t.user_id, t.intensity, t.occurred_on, u.company_id, u.gender
	from simple_activities t inner join users u on u.id = t.user_id
	),
	filtered_activities as (
	select * from hydrated_activities where company_id = 12 or company_id = 4
	),
	weekly_activities as (
	select user_id user_id, date_trunc('week', occurred_on) occurred_on, avg(intensity) intensity

spencerroan / weekly_hydration_company_percent.sql

Created June 23, 2016 20:26


	with fixed_waters as (
	select
	user_id,
	occurred_on,
	sum((case when level=3 then 4 else level end ) * 8) as ounces
	from simple_waters
	group by occurred_on, user_id
	order by occurred_on, user_id
	),

spencerroan / daily_sleep.sql

Last active June 23, 2016 20:21


	with hydrated_sleeps as (
	select ss.user_id, ss.well_rested, ss.occurred_on, u.company_id, u.gender
	from simple_sleeps ss inner join users u on u.id = ss.user_id
	),
	filtered_sleeps as (
	select * from hydrated_sleeps where company_id = 12 or company_id = 4
	),
	sleep_counts as (
	select

spencerroan / weekly_participation.sql

Created June 23, 2016 20:03


	with filtered_temp as (
	select *
	from hydrated_temp
	where company_id = 4 or company_id = 12
	) ,
	full_weekly_participation as (
	select
	date_trunc('week',
	'epoch'::timestamp

spencerroan / weekly hydration percent.sql

Created June 21, 2016 21:06


	with fixed_waters as (
	select
	user_id,
	occurred_on,
	sum((case when level=3 then 4 else level end ) * 8) as ounces
	from simple_waters
	group by occurred_on, user_id
	order by occurred_on, user_id
	),

spencerroan / weekly stress percent.sql

Created June 21, 2016 21:00


	with hydrated_stresses as (
	select t.user_id, t.level, t.occurred_on, u.company_id, u.gender
	from simple_stresses t inner join users u on u.id = t.user_id
	),
	filtered_stresses as (
	select * from hydrated_stresses where company_id = 12 or company_id = 4
	),
	weekly_stresses as (
	select user_id user_id, date_trunc('week', occurred_on) occurred_on, avg(level) stress_level

spencerroan / weekly activity percent.sql

Created June 21, 2016 20:40


	with hydrated_activities as (
	select t.user_id, t.intensity, t.occurred_on, u.company_id, u.gender
	from simple_activities t inner join users u on u.id = t.user_id
	),
	filtered_activities as (
	select * from hydrated_activities where company_id = 12 or company_id = 4
	),
	weekly_activities as (
	select user_id user_id, date_trunc('week', occurred_on) occurred_on, avg(intensity) intensity

spencerroan / hydration_percent.sql

Created June 21, 2016 19:01



	with fixed_waters as (
	select
	user_id,
	occurred_on,
	sum((case when level=3 then 4 else level end ) * 8) as ounces
	from simple_waters
	group by occurred_on, user_id
	order by occurred_on, user_id

spencerroan / stress_percent.sql

Created June 21, 2016 17:23


	with hydrated_stresses as (
	select t.user_id, t.level, t.occurred_on, u.company_id, u.gender
	from simple_stresses t inner join users u on u.id = t.user_id
	),
	filtered_stresses as (
	select * from hydrated_stresses where company_id = 12 or company_id = 4
	),
	stress_counts as (
	select

spencerroan / activity_percents.sql

Created June 21, 2016 17:16


	with hydrated_activities as (
	select t.user_id, t.intensity, t.occurred_on, u.company_id, u.gender
	from simple_activities t inner join users u on u.id = t.user_id
	),
	filtered_activities as (
	select * from hydrated_activities where company_id = 12 or company_id = 4
	),
	activity_counts as (
	select

spencer roan spencerroan