README on WSD final Project

Initialize the tables

• User table:

  CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(320) NOT NULL,
  password CHAR(60) NOT NULL
  );

• Morning table:

  CREATE TABLE morning_report (
    id SERIAL PRIMARY KEY,
    report_date DATE not null, 
    sleep_duration  INT CHECK (sleep_duration >= 0),
    sleep_quality INT CHECK (sleep_quality >= 1 and sleep_quality <=5),
    sleep_mood INT CHECK (sleep_mood >= 1 and sleep_mood <=5),
    user_id INTEGER REFERENCES users(id)
  );

• Evening table:

  CREATE TABLE evening_report (
    id SERIAL PRIMARY KEY,
    report_date DATE not null, 
    exercise_duration  INT CHECK (exercise_duration >= 0),
    study_duration  INT CHECK (study_duration >= 0),
    eat_quality INT CHECK (eat_quality >= 1 and eat_quality <=5),
    general_mood INT CHECK (general_mood >= 1 and general_mood <=5),
    user_id INTEGER REFERENCES users(id)
  );

Get Summary report

• get average sleep duration, time spent on sports and exercise, time spent studying, sleep quality, generic mood on Monthly and Weekly base.

SELECT 
AVG(sleep_duration) AS sleep_duration_average,
AVG(sleep_quality) AS sleep_quality_average
FROM 
morning_report
WHERE
report_date >= '2020-12-01'
AND
report_date <= '2020-12-05'
GROUP BY
user_id

SELECT 
AVG(exercise_duration) AS exercise_duration_average,
AVG(study_duration) AS study_duration_average,
AVG(general_mood) AS general_mood_average
FROM 
evening_report
WHERE
report_date >= '2020-12-01'
AND
report_date <= '2020-12-05'
GROUP BY
user_id

• get average mood

  SELECT user_id, AVG(sleep_mood)
  FROM (
    SELECT user_id, sleep_mood FROM morning_report WHERE report_date = '2020-12-05' GROUP BY user_id, sleep_mood 
    UNION ALL
    SELECT user_id, general_mood FROM evening_report WHERE report_date = '2020-12-05' GROUP BY user_id, general_mood
  ) AS subquery
  GROUP BY user_id