Studying Abroad and Student Well-Being: An End-to-End SQL Analysis

Project Overview

Studying in another country can provide valuable academic and personal opportunities, but it may also introduce challenges such as language barriers, cultural adjustment, homesickness, and separation from established support networks.

In this project, I used SQL to investigate whether international and domestic university students differed in depression, social connectedness, and acculturative stress. I also explored how factors such as region of origin, language proficiency, academic level, gender, and length of stay were associated with student well-being.

The analysis shows how SQL can be used to clean survey data, calculate group-level metrics, identify patterns, and translate raw records into useful insights.

Question

Do international and domestic students differ in their mental well-being and social connectedness?

Additional questions included:

How many international and domestic students were represented?
Which regions contributed the largest numbers of international students?
Did average depression differ between international and domestic students?
Did social connectedness differ between the two groups?
Did international students report greater acculturative stress?
How were depression and social connectedness related?
Did length of stay or language proficiency correspond with better well-being?
Did outcomes differ by gender or academic level?

Dataset

The dataset contains survey responses from university students and includes information about student background, language proficiency, social support, depression, social connectedness, and acculturative stress.

The original file contained 286 rows. During data cleaning, I identified 18 rows that did not contain a student classification or other usable survey information. These blank rows were excluded, leaving 268 valid student records.

The final analytical sample included:

201 international students
67 domestic students

Important Variables

Variable	Description
`inter_dom`	International or domestic student classification
`region`	Student’s region of origin
`gender`	Student gender
`academic`	Undergraduate or graduate status
`stay`	Length of stay in the host country
`stay_cate`	Short-, medium-, or long-stay category
`japanese_cate`	Japanese proficiency category
`english_cate`	English proficiency category
`todep`	Total depression score
`tosc`	Total social connectedness score
`toas`	Total acculturative stress score

Data Cleaning

Before conducting the analysis, I used SQL to:

Count the number of imported and valid records.
Exclude rows without a domestic or international classification.
Standardize student classifications into readable labels.
Check categorical variables for unexpected values.
Exclude invalid depression-severity labels from severity summaries.
Use nonmissing values when calculating averages.

The analysis was conducted on the 268 valid records rather than all 286 imported rows.

Student Composition

International students represented most of the analytical sample.

International students: 201, or 75.0%
Domestic students: 67, or 25.0%

Because the international group was approximately three times larger than the domestic group, averages and percentages were more informative than raw counts for most comparisons.

International Students by Region

Southeast Asia was the most frequently represented region among international students.

Region	Students
Southeast Asia	122
East Asia	48
South Asia	18
Other regions	11
Japan	2

Together, students from Southeast and East Asia accounted for most of the international student sample. Results for regions with very small sample sizes should be interpreted cautiously.

Depression Scores

International students had an average depression score of 8.04, compared with 8.61 among domestic students.

Student type	Average depression score
International	8.04
Domestic	8.61

The domestic group had a slightly higher average, but the difference was small.

Social Connectedness

The average social connectedness scores were also nearly identical.

Student type	Average social connectedness
International	37.42
Domestic	37.64

The difference between the groups was only 0.22 points. This suggests that international and domestic students in this sample reported similar overall levels of social connectedness.

Acculturative Stress

The clearest difference between the student groups appeared in acculturative stress.

Student type	Average acculturative stress
International	75.56
Domestic	62.84

International students scored approximately 12.72 points higher than domestic students on average. A supplementary group comparison indicated that this difference was statistically significant.

This suggests that international students may experience stress specifically related to cultural adjustment, even when their average depression and social connectedness scores are similar to those of domestic students.

Depression and Social Connectedness

Across all valid student records, depression was negatively related to social connectedness.

Students with higher social connectedness scores generally reported lower depression scores. This was one of the strongest patterns in the dataset.

However, the relationship is observational. The analysis cannot establish that increasing social connectedness directly causes depression scores to decrease. Other personal, social, or environmental factors may contribute to both measures.

Length of Stay

Among international students, average depression scores varied across individual lengths of stay. Students who had stayed for one year had an average depression score of 7.48, while students with two- and three-year stays had averages of 8.28 and 9.09, respectively.

Longer-stay categories contained relatively few students at several individual durations. For example, some lengths of stay were represented by only one student. Therefore, these results did not support a clear conclusion that well-being consistently improved or worsened with time in the host country.

Language Proficiency

Japanese Proficiency

Among international students:

Japanese proficiency	Students	Average depression	Average connectedness
Low	91	7.91	36.99
Average	85	8.38	37.56
High	25	7.40	38.48

International students with high Japanese proficiency had the lowest average depression score and the highest average social connectedness score. However, differences across the three groups were relatively small.

English Proficiency

Among international students:

English proficiency	Students	Average depression	Average connectedness
Low	7	8.00	40.29
Average	41	8.46	36.93
High	153	7.93	37.42

The low-proficiency category contained only seven students. Because of this small sample size, its comparatively high connectedness score should not be generalized to the broader international student population.

Overall, language proficiency alone did not show a strong or consistent relationship with depression in this dataset.

Academic Level

Among international students, graduate students had lower average depression and higher average connectedness than undergraduate students.

International student level	Students	Average depression	Average connectedness
Graduate	20	4.95	40.90
Undergraduate	181	8.39	37.03

This pattern may suggest that graduate international students had different experiences, resources, or support systems. However, only 20 international graduate students were represented.

The domestic graduate category contained just one student, so a meaningful domestic graduate–undergraduate comparison was not possible.

Gender

Average depression scores were similar across gender groups.

Among international students:

Female students: 8.37
Male students: 7.48

Among domestic students:

Female students: 8.50
Male students: 8.80

These differences were modest and should not be interpreted as evidence that gender alone determined student mental health.

Key Findings

The analysis produced five main findings:

International students did not report higher average depression. Their average depression score was slightly lower than that of domestic students.
Social connectedness was nearly identical between the two groups. International and domestic students had average scores of 37.42 and 37.64, respectively.
International students reported substantially greater acculturative stress. This was the most pronounced difference between the groups.
Higher social connectedness was associated with lower depression. The correlation of approximately −0.55 indicated a meaningful negative relationship.
Length of stay and language proficiency did not display simple, consistent relationships with depression. Several categories also contained small sample sizes, limiting strong conclusions.

Recommendations

Based on these findings, universities should avoid assuming that international students necessarily experience greater general depression or weaker social connections than domestic students.

Instead, student-support programs could focus more specifically on cultural adjustment by:

Providing orientation programs that continue beyond students’ first few weeks.
Creating peer-mentoring opportunities between new and continuing students.
Expanding culturally responsive counseling and support services.
Organizing activities that help students form meaningful social connections.
Offering practical assistance with language, housing, transportation, and navigating university systems.
Monitoring acculturative stress separately from general mental health measures.

Because connectedness was strongly associated with lower depression, programs that help students build reliable social networks may be especially valuable for both domestic and international students.

Conclusion

The original expectation was that students studying outside their home countries might report higher depression and lower social connectedness. The data did not support that expectation.

International and domestic students had similar average depression and social connectedness scores. However, international students reported considerably greater acculturative stress, highlighting a challenge that may not be fully captured by general measures of depression.

The project also identified a strong negative relationship between depression and social connectedness. Together, these results suggest that universities should address the specific pressures of cultural adjustment while continuing to create opportunities for all students to develop supportive social relationships.

From a technical perspective, this project shows how SQL can be used to clean survey data, produce group summaries, investigate multidimensional questions, and communicate findings that can support evidence-based decisions.

Complete SQL Script

-- ============================================================
-- STUDYING ABROAD AND STUDENT WELL-BEING
-- SQL PORTFOLIO PROJECT
-- Database: MySQL 
-- ============================================================

-- Project question:
-- Do international and domestic students differ in depression,
-- social connectedness, and acculturative stress?

-- Important data-cleaning note:
-- The original dataset contains 286 rows, but 18 rows are blank.
-- The analysis therefore used 268 valid student records.


-- ============================================================
-- 1. PREVIEW THE DATASET
-- ============================================================

SELECT *
FROM students
LIMIT 10;


-- ============================================================
-- 2. CHECK THE NUMBER OF IMPORTED AND VALID RECORDS
-- ============================================================

SELECT
    COUNT(*) AS imported_rows,
    COUNT(inter_dom) AS valid_student_records,
    SUM(
        CASE
            WHEN inter_dom IS NULL THEN 1
            ELSE 0
        END
    ) AS blank_rows
FROM students;


-- ============================================================
-- 3. COUNT INTERNATIONAL AND DOMESTIC STUDENTS
-- ============================================================

SELECT
    CASE
        WHEN inter_dom = 'Inter' THEN 'International'
        WHEN inter_dom = 'Dom' THEN 'Domestic'
        ELSE 'Unknown'
    END AS student_type,
    COUNT(*) AS total_students
FROM students
WHERE inter_dom IS NOT NULL
GROUP BY inter_dom
ORDER BY total_students DESC;


-- ============================================================
-- 4. CALCULATE THE PERCENTAGE OF STUDENTS IN EACH GROUP
-- ============================================================

SELECT
    CASE
        WHEN inter_dom = 'Inter' THEN 'International'
        WHEN inter_dom = 'Dom' THEN 'Domestic'
        ELSE 'Unknown'
    END AS student_type,
    COUNT(*) AS total_students,
    ROUND(
        100.0 * COUNT(*) / SUM(COUNT(*)) OVER (),
        1
    ) AS percentage_of_students
FROM students
WHERE inter_dom IS NOT NULL
GROUP BY inter_dom
ORDER BY total_students DESC;


-- ============================================================
-- 5. COUNT INTERNATIONAL STUDENTS BY REGION
-- ============================================================

SELECT
    region,
    COUNT(*) AS total_students
FROM students
WHERE inter_dom = 'Inter'
  AND region IS NOT NULL
GROUP BY region
ORDER BY total_students DESC;


-- ============================================================
-- 6. SUMMARIZE THE MAIN WELL-BEING MEASURES
-- ============================================================

SELECT
    CASE
        WHEN inter_dom = 'Inter' THEN 'International'
        WHEN inter_dom = 'Dom' THEN 'Domestic'
        ELSE 'Unknown'
    END AS student_type,
    COUNT(*) AS total_students,
    ROUND(AVG(todep), 2) AS avg_depression_score,
    ROUND(AVG(tosc), 2) AS avg_social_connectedness,
    ROUND(AVG(toas), 2) AS avg_acculturative_stress
FROM students
WHERE inter_dom IS NOT NULL
GROUP BY inter_dom
ORDER BY student_type;


-- ============================================================
-- 7. COMPARE DEPRESSION SCORES
-- ============================================================

SELECT
    CASE
        WHEN inter_dom = 'Inter' THEN 'International'
        WHEN inter_dom = 'Dom' THEN 'Domestic'
        ELSE 'Unknown'
    END AS student_type,
    COUNT(*) AS total_students,
    ROUND(AVG(todep), 2) AS avg_depression_score,
    MIN(todep) AS minimum_depression_score,
    MAX(todep) AS maximum_depression_score
FROM students
WHERE inter_dom IS NOT NULL
  AND todep IS NOT NULL
GROUP BY inter_dom
ORDER BY avg_depression_score DESC;


-- ============================================================
-- 8. COMPARE SOCIAL CONNECTEDNESS
-- ============================================================

SELECT
    CASE
        WHEN inter_dom = 'Inter' THEN 'International'
        WHEN inter_dom = 'Dom' THEN 'Domestic'
        ELSE 'Unknown'
    END AS student_type,
    COUNT(*) AS total_students,
    ROUND(AVG(tosc), 2) AS avg_social_connectedness,
    MIN(tosc) AS minimum_connectedness_score,
    MAX(tosc) AS maximum_connectedness_score
FROM students
WHERE inter_dom IS NOT NULL
  AND tosc IS NOT NULL
GROUP BY inter_dom
ORDER BY avg_social_connectedness DESC;


-- ============================================================
-- 9. COMPARE ACCULTURATIVE STRESS
-- ============================================================

SELECT
    CASE
        WHEN inter_dom = 'Inter' THEN 'International'
        WHEN inter_dom = 'Dom' THEN 'Domestic'
        ELSE 'Unknown'
    END AS student_type,
    COUNT(*) AS total_students,
    ROUND(AVG(toas), 2) AS avg_acculturative_stress,
    MIN(toas) AS minimum_stress_score,
    MAX(toas) AS maximum_stress_score
FROM students
WHERE inter_dom IS NOT NULL
  AND toas IS NOT NULL
GROUP BY inter_dom
ORDER BY avg_acculturative_stress DESC;


-- ============================================================
-- 10. COMPARE WELL-BEING BY GENDER
-- ============================================================

SELECT
    CASE
        WHEN inter_dom = 'Inter' THEN 'International'
        WHEN inter_dom = 'Dom' THEN 'Domestic'
        ELSE 'Unknown'
    END AS student_type,
    gender,
    COUNT(*) AS total_students,
    ROUND(AVG(todep), 2) AS avg_depression_score,
    ROUND(AVG(tosc), 2) AS avg_social_connectedness,
    ROUND(AVG(toas), 2) AS avg_acculturative_stress
FROM students
WHERE inter_dom IS NOT NULL
  AND gender IS NOT NULL
GROUP BY inter_dom, gender
ORDER BY student_type, gender;


-- ============================================================
-- 11. COMPARE WELL-BEING BY ACADEMIC LEVEL
-- ============================================================

SELECT
    CASE
        WHEN inter_dom = 'Inter' THEN 'International'
        WHEN inter_dom = 'Dom' THEN 'Domestic'
        ELSE 'Unknown'
    END AS student_type,
    academic,
    COUNT(*) AS total_students,
    ROUND(AVG(todep), 2) AS avg_depression_score,
    ROUND(AVG(tosc), 2) AS avg_social_connectedness,
    ROUND(AVG(toas), 2) AS avg_acculturative_stress
FROM students
WHERE inter_dom IS NOT NULL
  AND academic IS NOT NULL
GROUP BY inter_dom, academic
ORDER BY student_type, academic;


-- ============================================================
-- 12. ANALYZE INTERNATIONAL STUDENTS BY LENGTH OF STAY
-- ============================================================

SELECT
    stay_cate,
    COUNT(*) AS total_students,
    ROUND(AVG(stay), 2) AS avg_length_of_stay,
    ROUND(AVG(todep), 2) AS avg_depression_score,
    ROUND(AVG(tosc), 2) AS avg_social_connectedness,
    ROUND(AVG(toas), 2) AS avg_acculturative_stress
FROM students
WHERE inter_dom = 'Inter'
  AND stay_cate IS NOT NULL
GROUP BY stay_cate
ORDER BY FIELD(stay_cate, 'Short', 'Medium', 'Long');


-- ============================================================
-- 13. ANALYZE EACH INDIVIDUAL LENGTH OF STAY
-- ============================================================

SELECT
    stay AS years_of_stay,
    COUNT(*) AS total_students,
    ROUND(AVG(todep), 2) AS avg_depression_score,
    ROUND(AVG(tosc), 2) AS avg_social_connectedness,
    ROUND(AVG(toas), 2) AS avg_acculturative_stress
FROM students
WHERE inter_dom = 'Inter'
  AND stay IS NOT NULL
GROUP BY stay
ORDER BY stay;


-- ============================================================
-- 14. ANALYZE JAPANESE PROFICIENCY
-- ============================================================

SELECT
    japanese_cate AS japanese_proficiency,
    COUNT(*) AS total_students,
    ROUND(AVG(todep), 2) AS avg_depression_score,
    ROUND(AVG(tosc), 2) AS avg_social_connectedness,
    ROUND(AVG(toas), 2) AS avg_acculturative_stress
FROM students
WHERE inter_dom = 'Inter'
  AND japanese_cate IS NOT NULL
GROUP BY japanese_cate
ORDER BY FIELD(japanese_cate, 'Low', 'Average', 'High');


-- ============================================================
-- 15. ANALYZE ENGLISH PROFICIENCY
-- ============================================================

SELECT
    english_cate AS english_proficiency,
    COUNT(*) AS total_students,
    ROUND(AVG(todep), 2) AS avg_depression_score,
    ROUND(AVG(tosc), 2) AS avg_social_connectedness,
    ROUND(AVG(toas), 2) AS avg_acculturative_stress
FROM students
WHERE inter_dom = 'Inter'
  AND english_cate IS NOT NULL
GROUP BY english_cate
ORDER BY FIELD(english_cate, 'Low', 'Average', 'High');


-- ============================================================
-- 16. ANALYZE INTERNATIONAL STUDENTS BY REGION
-- ============================================================

SELECT
    region,
    COUNT(*) AS total_students,
    ROUND(AVG(todep), 2) AS avg_depression_score,
    ROUND(AVG(tosc), 2) AS avg_social_connectedness,
    ROUND(AVG(toas), 2) AS avg_acculturative_stress
FROM students
WHERE inter_dom = 'Inter'
  AND region IS NOT NULL
GROUP BY region
ORDER BY avg_depression_score DESC;


-- ============================================================
-- 17. EXCLUDE VERY SMALL REGIONAL GROUPS
-- ============================================================

SELECT
    region,
    COUNT(*) AS total_students,
    ROUND(AVG(todep), 2) AS avg_depression_score,
    ROUND(AVG(tosc), 2) AS avg_social_connectedness,
    ROUND(AVG(toas), 2) AS avg_acculturative_stress
FROM students
WHERE inter_dom = 'Inter'
  AND region IS NOT NULL
GROUP BY region
HAVING COUNT(*) >= 10
ORDER BY avg_depression_score DESC;


-- ============================================================
-- 18. DEPRESSION-SEVERITY DISTRIBUTION
-- ============================================================

SELECT
    CASE
        WHEN inter_dom = 'Inter' THEN 'International'
        WHEN inter_dom = 'Dom' THEN 'Domestic'
        ELSE 'Unknown'
    END AS student_type,
    depsev AS depression_severity,
    COUNT(*) AS total_students
FROM students
WHERE inter_dom IS NOT NULL
  AND depsev IN ('Min', 'Mild', 'Mod', 'ModSev', 'Sev')
GROUP BY inter_dom, depsev
ORDER BY
    student_type,
    FIELD(depsev, 'Min', 'Mild', 'Mod', 'ModSev', 'Sev');


-- ============================================================
-- 19. PERCENTAGE DISTRIBUTION OF DEPRESSION SEVERITY
-- ============================================================

WITH severity_counts AS (
    SELECT
        inter_dom,
        depsev,
        COUNT(*) AS severity_total
    FROM students
    WHERE inter_dom IS NOT NULL
      AND depsev IN ('Min', 'Mild', 'Mod', 'ModSev', 'Sev')
    GROUP BY inter_dom, depsev
)

SELECT
    CASE
        WHEN inter_dom = 'Inter' THEN 'International'
        WHEN inter_dom = 'Dom' THEN 'Domestic'
        ELSE 'Unknown'
    END AS student_type,
    depsev AS depression_severity,
    severity_total,
    ROUND(
        100.0 * severity_total /
        SUM(severity_total) OVER (PARTITION BY inter_dom),
        1
    ) AS percentage_within_student_type
FROM severity_counts
ORDER BY
    student_type,
    FIELD(depsev, 'Min', 'Mild', 'Mod', 'ModSev', 'Sev');


-- ============================================================
-- 20. STUDENTS ABOVE THE OVERALL AVERAGE DEPRESSION SCORE
-- ============================================================

WITH overall_average AS (
    SELECT
        AVG(todep) AS avg_depression
    FROM students
    WHERE inter_dom IS NOT NULL
      AND todep IS NOT NULL
)

SELECT
    CASE
        WHEN s.inter_dom = 'Inter' THEN 'International'
        WHEN s.inter_dom = 'Dom' THEN 'Domestic'
        ELSE 'Unknown'
    END AS student_type,
    s.region,
    s.gender,
    s.academic,
    s.todep AS depression_score,
    ROUND(o.avg_depression, 2) AS overall_avg_depression
FROM students AS s
CROSS JOIN overall_average AS o
WHERE s.inter_dom IS NOT NULL
  AND s.todep > o.avg_depression
ORDER BY s.todep DESC;


-- ============================================================
-- 21. COUNT STUDENTS ABOVE THE OVERALL AVERAGE
-- ============================================================

WITH overall_average AS (
    SELECT
        AVG(todep) AS avg_depression
    FROM students
    WHERE inter_dom IS NOT NULL
      AND todep IS NOT NULL
)

SELECT
    CASE
        WHEN s.inter_dom = 'Inter' THEN 'International'
        WHEN s.inter_dom = 'Dom' THEN 'Domestic'
        ELSE 'Unknown'
    END AS student_type,
    COUNT(*) AS students_above_overall_average
FROM students AS s
CROSS JOIN overall_average AS o
WHERE s.inter_dom IS NOT NULL
  AND s.todep > o.avg_depression
GROUP BY s.inter_dom
ORDER BY students_above_overall_average DESC;


-- ============================================================
-- 22. WINDOW FUNCTION:
-- COMPARE EACH STUDENT WITH THEIR GROUP AVERAGE
-- ============================================================

SELECT
    CASE
        WHEN inter_dom = 'Inter' THEN 'International'
        WHEN inter_dom = 'Dom' THEN 'Domestic'
        ELSE 'Unknown'
    END AS student_type,
    region,
    gender,
    academic,
    todep AS depression_score,
    ROUND(
        AVG(todep) OVER (PARTITION BY inter_dom),
        2
    ) AS student_type_avg_depression,
    ROUND(
        todep - AVG(todep) OVER (PARTITION BY inter_dom),
        2
    ) AS difference_from_group_average
FROM students
WHERE inter_dom IS NOT NULL
  AND todep IS NOT NULL
ORDER BY
    student_type,
    difference_from_group_average DESC;


-- ============================================================
-- 23. RANK REGIONS BY AVERAGE DEPRESSION
-- ============================================================

WITH regional_summary AS (
    SELECT
        region,
        COUNT(*) AS total_students,
        AVG(todep) AS avg_depression
    FROM students
    WHERE inter_dom = 'Inter'
      AND region IS NOT NULL
    GROUP BY region
)

SELECT
    region,
    total_students,
    ROUND(avg_depression, 2) AS avg_depression_score,
    DENSE_RANK() OVER (
        ORDER BY avg_depression DESC
    ) AS depression_rank
FROM regional_summary
ORDER BY depression_rank;


-- ============================================================
-- 24. RANK LANGUAGE-PROFICIENCY GROUPS
-- ============================================================

WITH language_summary AS (
    SELECT
        japanese_cate,
        COUNT(*) AS total_students,
        AVG(todep) AS avg_depression,
        AVG(tosc) AS avg_social_connectedness
    FROM students
    WHERE inter_dom = 'Inter'
      AND japanese_cate IS NOT NULL
    GROUP BY japanese_cate
)

SELECT
    japanese_cate AS japanese_proficiency,
    total_students,
    ROUND(avg_depression, 2) AS avg_depression_score,
    ROUND(avg_social_connectedness, 2)
        AS avg_social_connectedness,
    DENSE_RANK() OVER (
        ORDER BY avg_depression
    ) AS lowest_depression_rank
FROM language_summary
ORDER BY lowest_depression_rank;


-- ============================================================
-- 25. COMPARE SOCIAL-SUPPORT SOURCES
-- ============================================================

SELECT
    CASE
        WHEN inter_dom = 'Inter' THEN 'International'
        WHEN inter_dom = 'Dom' THEN 'Domestic'
        ELSE 'Unknown'
    END AS student_type,

    ROUND(
        100 * AVG(
            CASE
                WHEN partner_bi = 'Yes' THEN 1
                ELSE 0
            END
        ),
        1
    ) AS pct_with_partner_support,

    ROUND(
        100 * AVG(
            CASE
                WHEN friends_bi = 'Yes' THEN 1
                ELSE 0
            END
        ),
        1
    ) AS pct_with_friend_support,

    ROUND(
        100 * AVG(
            CASE
                WHEN parents_bi = 'Yes' THEN 1
                ELSE 0
            END
        ),
        1
    ) AS pct_with_parent_support,

    ROUND(
        100 * AVG(
            CASE
                WHEN professional_bi = 'Yes' THEN 1
                ELSE 0
            END
        ),
        1
    ) AS pct_with_professional_support

FROM students
WHERE inter_dom IS NOT NULL
GROUP BY inter_dom;


-- ============================================================
-- 26. COMPARE DEPRESSION BY FRIEND SUPPORT
-- ============================================================

SELECT
    CASE
        WHEN inter_dom = 'Inter' THEN 'International'
        WHEN inter_dom = 'Dom' THEN 'Domestic'
        ELSE 'Unknown'
    END AS student_type,
    friends_bi AS friend_support,
    COUNT(*) AS total_students,
    ROUND(AVG(todep), 2) AS avg_depression_score,
    ROUND(AVG(tosc), 2) AS avg_social_connectedness
FROM students
WHERE inter_dom IS NOT NULL
  AND friends_bi IS NOT NULL
GROUP BY inter_dom, friends_bi
ORDER BY student_type, friend_support;


-- ============================================================
-- 27. COMPARE DEPRESSION BY PARTNER SUPPORT
-- ============================================================

SELECT
    CASE
        WHEN inter_dom = 'Inter' THEN 'International'
        WHEN inter_dom = 'Dom' THEN 'Domestic'
        ELSE 'Unknown'
    END AS student_type,
    partner_bi AS partner_support,
    COUNT(*) AS total_students,
    ROUND(AVG(todep), 2) AS avg_depression_score,
    ROUND(AVG(tosc), 2) AS avg_social_connectedness
FROM students
WHERE inter_dom IS NOT NULL
  AND partner_bi IS NOT NULL
GROUP BY inter_dom, partner_bi
ORDER BY student_type, partner_support;


-- ============================================================
-- 28. CREATE A SIMPLIFIED DEPRESSION CATEGORY
-- ============================================================

SELECT
    CASE
        WHEN inter_dom = 'Inter' THEN 'International'
        WHEN inter_dom = 'Dom' THEN 'Domestic'
        ELSE 'Unknown'
    END AS student_type,

    CASE
        WHEN todep <= 4 THEN 'Minimal'
        WHEN todep BETWEEN 5 AND 9 THEN 'Mild'
        WHEN todep BETWEEN 10 AND 14 THEN 'Moderate'
        WHEN todep BETWEEN 15 AND 19 THEN 'Moderately Severe'
        WHEN todep >= 20 THEN 'Severe'
        ELSE 'Missing'
    END AS depression_category,

    COUNT(*) AS total_students

FROM students
WHERE inter_dom IS NOT NULL
  AND todep IS NOT NULL
GROUP BY
    inter_dom,
    CASE
        WHEN todep <= 4 THEN 'Minimal'
        WHEN todep BETWEEN 5 AND 9 THEN 'Mild'
        WHEN todep BETWEEN 10 AND 14 THEN 'Moderate'
        WHEN todep BETWEEN 15 AND 19 THEN 'Moderately Severe'
        WHEN todep >= 20 THEN 'Severe'
        ELSE 'Missing'
    END
ORDER BY
    student_type,
    FIELD(
        depression_category,
        'Minimal',
        'Mild',
        'Moderate',
        'Moderately Severe',
        'Severe'
    );


-- ============================================================
-- 29. IDENTIFY THE HIGHEST DEPRESSION SCORES
-- ============================================================

SELECT
    CASE
        WHEN inter_dom = 'Inter' THEN 'International'
        WHEN inter_dom = 'Dom' THEN 'Domestic'
        ELSE 'Unknown'
    END AS student_type,
    region,
    gender,
    academic,
    todep AS depression_score,
    tosc AS social_connectedness,
    toas AS acculturative_stress,
    DENSE_RANK() OVER (
        ORDER BY todep DESC
    ) AS depression_rank
FROM students
WHERE inter_dom IS NOT NULL
  AND todep IS NOT NULL
ORDER BY depression_rank
LIMIT 10;


-- ============================================================
-- 30. FINAL SUMMARY TABLE
-- ============================================================

SELECT
    CASE
        WHEN inter_dom = 'Inter' THEN 'International'
        WHEN inter_dom = 'Dom' THEN 'Domestic'
        ELSE 'Unknown'
    END AS student_type,
    COUNT(*) AS total_students,
    ROUND(AVG(age), 2) AS avg_age,
    ROUND(AVG(stay), 2) AS avg_length_of_stay,
    ROUND(AVG(todep), 2) AS avg_depression_score,
    ROUND(AVG(tosc), 2) AS avg_social_connectedness,
    ROUND(AVG(toas), 2) AS avg_acculturative_stress
FROM students
WHERE inter_dom IS NOT NULL
GROUP BY inter_dom
ORDER BY student_type;

Studying Abroad and Student Well-Being: An End-to-End SQL Analysis

Leave a Reply Cancel reply