Design student–course data models and SQL
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Scenario: Model a university domain with Students, Courses, Departments, Instructors, and Enrollments.
Tasks:
1) OLTP ERD: Specify normalized tables with PRIMARY/FOREIGN keys and constraints (e.g., a student cannot enroll in two sections of the same course in the same term; grade in {A,B,C,D,F,Pass,Fail,Incomplete}). Include indexes you would create and why.
2) Warehouse: Design a star schema for analytics (FactEnrollment with measures like credits_attempted, credits_earned, points; DimStudent, DimCourse, DimDepartment, DimTerm, DimInstructor). Explain your grain and surrogate keys. Model SCD Type 2 on DimStudent for major changes; show columns (effective_start, effective_end, is_current) and how you would join for point-in-time correctness.
3) Partitioning: Propose partitioning/clustering for FactEnrollment at 100M+ rows. Justify by common query predicates and maintenance.
4) Write SQL:
a) List students who took both 'CS101' and 'MATH201' in the same term.
b) For the most recent completed term, return the top 3 departments by average GPA (weighted by credits). Break ties deterministically.
5) Briefly compare OLTP vs OLAP design trade-offs in this scenario and when you would prefer snowflake over star.
Overview: This question evaluates data modeling, database design, and SQL proficiency—covering normalization, schema constraints, indexing, star-schema warehousing, slowly changing dimensions, partitioning strategies, and complex analytic queries—in the domain of relational databases and data warehousing (Data Manipulation/SQL).
Read the full Amazon Data Scientist interview experience this question came from
Students who took CS101 and MATH201 in the same term
Using the tables below, write a SQL query to list all students who, in at least one term, enrolled in both the course with course_code = 'CS101' and the course with course_code = 'MATH201'. A student should only be included if they took both courses in the same term (i.e., the same term_id).
Return one row per student with the columns:
- student_id
- student_name
Use the provided sample data as a guide; your query should work for any valid data.
Tables
departments(department_id INT, department_code VARCHAR(10), department_name VARCHAR(100))
students(student_id INT, student_name VARCHAR(100), major_department_id INT)
courses(course_id INT, course_code VARCHAR(20), course_name VARCHAR(200), department_id INT, credits INT)
terms(term_id INT, term_code VARCHAR(20), start_date DATE, end_date DATE)
enrollments(enrollment_id INT, student_id INT, course_id INT, term_id INT, grade VARCHAR(2))
Hints
- Make sure you are checking that both course codes appear within the same term_id for a given student.
- You can GROUP BY student and term, then use HAVING with COUNT(DISTINCT course_code) = 2, or use a self-join on enrollments.
Top 3 departments by GPA in the most recent completed term
Using the same schema, compute grade-point averages (GPAs) by department for the most recent completed term as of 2025-06-01.
Assume the most recent completed term is the one whose end_date is the latest date before 2025-06-01. In the sample data, this is the term that ended on 2025-05-15 (term_code = 'SPRING2025').
For that term only, calculate each department's GPA as:
GPA = (sum of grade_points * course_credits over all enrollments in that department and term) / (sum of course_credits over those enrollments)
Use a standard 4.0 scale for letter grades:
- A = 4.0
- B = 3.0
- C = 2.0
- D = 1.0
- F = 0.0
Ignore any enrollments with grades outside this set (there are none in the sample, but your query should handle this).
Return the top 3 departments ranked by GPA for the term ending on 2025-05-15, with these columns:
- department_id
- department_name
- avg_gpa (rounded to 2 decimal places)
Order the result by avg_gpa in descending order. Break ties deterministically by department_name in ascending order. Limit the output to 3 rows.
Tables
departments(department_id INT, department_code VARCHAR(10), department_name VARCHAR(100))
students(student_id INT, student_name VARCHAR(100), major_department_id INT)
courses(course_id INT, course_code VARCHAR(20), course_name VARCHAR(200), department_id INT, credits INT)
terms(term_id INT, term_code VARCHAR(20), start_date DATE, end_date DATE)
enrollments(enrollment_id INT, student_id INT, course_id INT, term_id INT, grade VARCHAR(2))
Hints
- Use the terms table to restrict enrollments to the term whose end_date is '2025-05-15'.
- Map letter grades to numeric points with a CASE expression, then compute SUM(points * credits) / SUM(credits) grouped by department, and finally ORDER BY and LIMIT.