-
Notifications
You must be signed in to change notification settings - Fork 0
/
others
84 lines (64 loc) · 2.94 KB
/
others
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
AVG in Minutes: I narrow down my results to just the averages of the different types of minutes by Id.
SELECT Id,
avg(VeryActiveMinutes) AS Avg_Very_Active_Minutes,
avg(FairlyActiveMinutes) AS Avg_Fairly_Active_Minutes,
avg(LightlyActiveMinutes) AS Avg_Lightly_Active_Minutes,
avg(SedentaryMinutes) AS Avg_Sedentary_Minutes,
FROM capstonebnb.dbo.daily_activity_cleaned
GROUP BY Id
User Types by Activity Levels: The CDC recommends 150 minutes of physical activity each week.
I added up the average minutes of Very Active and Fairly Active to see if each unique ID was meeting the CDC's guidelines for activity.
SELECT Id,
avg(VeryActiveMinutes) + avg(FairlyActiveMinutes)
AS Total_Avg_Active_Minutes
FROM capstonebnb.dbo.daily_activity_cleaned
GROUP BY Id
But if we add in the "lightly active" level, it shows that most of the users now have an average of 150 minutes or more of an "active" period, which is what the CDC recommends.
SELECT Id,
avg(VeryActiveMinutes) + avg(FairlyActiveMinutes) + avg(LightlyActiveMinutes) AS Total_Avg_Active_Minutes,
CASE WHEN avg(VeryActiveMinutes) + avg(FairlyActiveMinutes) + avg(LightlyActiveMinutes) >= 150 THEN 'Meets CDC Recommendation'
WHEN avg(VeryActiveMinutes) + avg(FairlyActiveMinutes) + avg(LightlyActiveMinutes) <150 THEN 'Does Not Meet CDC Recommendation'
END CDC_Recommendations
FROM capstonebnb.dbo.daily_activity_cleaned
GROUP BY Id
Steps
SELECT Id,
avg(TotalSteps) AS Avg_Total_Steps,
CASE
WHEN avg(TotalSteps) < 5000 THEN 'Inactive'
WHEN avg(TotalSteps) BETWEEN 5000 AND 7499 THEN 'Low Active User'
WHEN avg(TotalSteps) BETWEEN 7500 AND 9999 THEN 'Average Active User'
WHEN avg(TotalSteps) BETWEEN 10000 AND 12499 THEN 'Active User'
WHEN avg(TotalSteps) >= 12500 THEN 'Very Active User'
END User_Type
FROM capstonebnb.dbo.daily_activity_cleaned
GROUP BY Id
Calories, Steps & Active Minutes by ID: What their logged calories tell us about how many steps they take and how long they are active.
SELECT Id,
Sum(TotalSteps) AS Sum_total_steps,
SUM(Calories) AS Sum_Calories,
SUM(VeryActiveMinutes + FairlyActiveMinutes) AS Sum_Active_Minutes
FROM capstonebnb.dbo.daily_activity_cleaned
GROUP BY Id
Total Steps by Date: I analyzed the Total Steps by Date to find out when our users were the most active.
SELECT ActivityDate,
--ROUND (avg(TotalSteps), 2) AS Average_Total_Steps
--FROM capstonebnb.dbo.daily_activity_cleaned
--GROUP BY ActivityDate
Sleep
First, I looked at which date all the users had the most minutes of sleep.
SELECT
SleepDay,
SUM(TotalMinutesAsleep) AS Total_Minutes_Asleep
FROM capstonebnb.dbo.Sleep_day
WHERE SleepDay IS NOT NULL
GROUP BY SleepDay
Average minutes slept, total steps, and calories by user Id.
SELECT a.Id,
avg(a.TotalSteps) AS AvgTotalSteps,
avg(a.Calories) AS AvgCalories,
avg(s.TotalMinutesAsleep) AS AvgTotalMinutesAsleep,
FROM capstonebnb.dbo.daily_activity_cleaned AS a
INNER JOIN `scenic-kiln-368505.Bellabeat.Sleepday` AS s ON a.Id=s.Id
GROUP BY a.Id
--ORDER BY Average_Total_Steps DESC