mukaruernest/Health-Analytics-Case-Study

This repository contains analysis from a request from the General Manager of Analytics at Health Co requesting assistance with their analysis.

Health Analytics Case Study

Table of Contents

• Business Questions
• Datasets
• Solution

Business Questions

1. How many unique users exist in the logs dataset?
2. How many total measurements do we have per user on average?
3. What about the median number of measurements per user?
4. How many users have 3 or more measurements?
5. How many users have 1,000 or more measurements?
6. Have logged blood glucose measurements?
7. Have at least 2 types of measurements?
8. Have all 3 measures - blood glucose, weight and blood pressure?
9. What is the median systolic/diastolic blood pressure values?

Before anwering the Business questions I needed to see the dataset I will be dealing with. Below is the health.user_logs table

SELECT * 
FROM health.user_logs

Dataset

health.user_logs

Solutions

1. How many unique users exist in the logs dataset?

SELECT COUNT(DISTINCT id)
FROM health.user_logs;

count
554

For questions 2-8 we created a temporary table

DROP TABLE IF EXISTS user_measure_count;
CREATE TEMP TABLE user_measure_count AS 
SELECT
    id,
    COUNT(*) AS measure_count,
    COUNT(DISTINCT measure) as unique_measures
  FROM health.user_logs
  GROUP BY 1;

2. How many total measurements do we have per user on average?

SELECT
  ROUND(AVG(measure_count), 2)
FROM user_measure_count;

round
79.23

3. What about the median number of measurements per user?

SELECT 
   PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY measure_count) AS median_value 
FROM user_measure_count;

median_value
2

4. How many users have 3 or more measurements?

SELECT
  COUNT(*)
FROM user_measure_count
WHERE measure_count >= 3;

count
209

5. How many users have 1,000 or more measurements?

SELECT
  COUNT(id)
FROM user_measure_count
WHERE measure_count >= 1000;

count
5

6. Have logged blood glucose measurements?

SELECT
  COUNT(DISTINCT id)
FROM health.user_logs
WHERE measure = 'blood_glucose';

count
325

7. Have at least 2 types of measurements?

SELECT 
  COUNT(*)
FROM user_measure_count
WHERE unique_measures >= 2;

count
204

8. Have all 3 measures - blood glucose, weight and blood pressure?

SELECT
  COUNT(*)
FROM user_measure_count
WHERE unique_measures = 3;

count
50

9. What is the median systolic/diastolic blood pressure values?

SELECT
  PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY systolic) AS median_systolic,
  PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY diastolic) AS median_diastolic
FROM health.user_logs
WHERE measure = 'blood_pressure';

median_systolic	median_diastolic
126	79