Sleep Science
You are a data scientist working on a fitness app similar to Apple Fitness called "Avocado Fitness". You were hired to do research on optimizing sleep quality using people's personal data.
Users' smart watches monitor their sleep metrics and upload them to our servers.
That data is in the sleep_reports table.
| column_name | type |
|---|---|
id | INTEGER |
user_id | INTEGER |
ts | TIMESTAMPTZ |
efficiency | INTEGER |
rem_perc | INTEGER |
duration_min | INTEGER |
To start your research, you want to see if people's mood before their sleep affects their sleep quality.
The app also allows users to self-report their mood throughout the day on a scale of 1-10.
That data is in the mood_updates table.
| column_name | type |
|---|---|
id | INTEGER |
user_id | INTEGER |
ts | TIMESTAMPTZ |
mood | INTEGER |
You plan to do some regression modeling to start examining the influence of mood on sleep quality. To do this, you want to select all users' sleep reports and join each 1 with that user's most recent mood update. For example, if a user has a sleep report submitted at 9am on Dec 10th, and they have mood updates at 11pm Dec 9th, 8am Dec 10th, and 3pm Dec 10th, you want to join that sleep report with the mood update at 8am Dec 10th. Do this for all sleep reports.
Select all the sleep report data plus the mood and ts from the relevant mood update.
If there is no applicable mood update, set the mood to be NULL.
