Does going to university in a different country affect your mental health? A Japanese international university surveyed its students in 2018 and published a study the following year that was approved by several ethical and regulatory boards.
The study found that international students have a higher risk of mental health difficulties than the general population, and that social connectedness (belonging to a social group) and acculturative stress (stress associated with joining a new culture) are predictive of depression.
Explore the students data using PostgreSQL to find out if you would come to a similar conclusion for international students and see if the length of stay is a contributing factor.
Here is a data description of the columns you may find helpful.
| Field Name | Description |
|---|---|
inter_dom | Types of students (international or domestic) |
japanese_cate | Japanese language proficiency |
english_cate | English language proficiency |
academic | Current academic level (undergraduate or graduate) |
age | Current age of student |
stay | Current length of stay in years |
todep | Total score of depression (PHQ-9 test) |
tosc | Total score of social connectedness (SCS test) |
toas | Total score of acculturative stress (ASISS test) |
-- Run this code to view the data in students
SELECT *
FROM students
LIMIT 10;1. Performing the calculations
Count the international students and find the summary statistics for each diagnostic test using aggregate functions. Round the averages to two decimal places and use the aliases min_phq, max_phq, and avg_phq.
SELECT
stay, -- Column 1: Length of stay in years
COUNT(*) AS count_int, -- Column 2: Number of international students
ROUND(AVG(todep), 2) AS average_phq, -- Column 3: Average PHQ-9 score
ROUND(AVG(tosc), 2) AS average_scs, -- Column 4: Average Social Connectedness score
ROUND(AVG(toas), 2) AS average_as -- Column 5: Average Acculturative Stress score
FROM students
WHERE inter_dom = 'Inter' -- Filter: Only international students
GROUP BY stay -- Group by length of stay
ORDER BY stay DESC -- Sort by stay in descending order
LIMIT 9; -- Ensure the table has 9 rows2. Filter and group the data
You previously performed counts and average calculations on the data; now you need to apply the appropriate filter and group so that the calculations are done on the international student group only.
SELECT
stay, -- Column 1: Length of stay in years
COUNT(*) AS count_int, -- Column 2: Number of international students for each stay
ROUND(AVG(todep), 2) AS average_phq, -- Column 3: Average PHQ-9 score (depression), rounded to 2 decimals
ROUND(AVG(tosc), 2) AS average_scs, -- Column 4: Average Social Connectedness score, rounded to 2 decimals
ROUND(AVG(toas), 2) AS average_as -- Column 5: Average Acculturative Stress score, rounded to 2 decimals
FROM students
WHERE inter_dom = 'Inter' -- Filter: Only international students
GROUP BY stay -- Group by length of stay
ORDER BY stay DESC; -- Sort by length of stay in descending order3. Ordering records
Order the resulting table in descending order of stay.
SELECT
stay, -- Column 1: Length of stay in years
COUNT(*) AS count_int, -- Column 2: Number of international students for each stay
ROUND(AVG(todep), 2) AS average_phq, -- Column 3: Average PHQ-9 score (depression), rounded to 2 decimal places
ROUND(AVG(tosc), 2) AS average_scs, -- Column 4: Average Social Connectedness score, rounded to 2 decimals
ROUND(AVG(toas), 2) AS average_as -- Column 5: Average Acculturative Stress score, rounded to 2 decimals
FROM students
WHERE inter_dom = 'Inter' -- Filter: Only international students
GROUP BY stay -- Group by length of stay
ORDER BY stay DESC -- Sort by `stay` in descending order
LIMIT 9; -- Limit the result to 9 rows