Coding questions · Amazon
Design student–course data models and SQL
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).
Scenario: Model a university domain with Students, Courses, Departments, Instructors, and Enrollments. Tasks:
- 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.
- 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.
- Partitioning: Propose partitioning/clustering for FactEnrollment at 100M+ rows. Justify by common query predicates and maintenance.
- 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.
- Briefly compare OLTP vs OLAP design trade-offs in this scenario and when you would prefer snowflake over star.
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.
Table departments
| column | type | constraints |
|---|---|---|
| department_id | INT | PRIMARY KEY |
| department_code | VARCHAR(10) | NOT NULL, UNIQUE |
| department_name | VARCHAR(100) | NOT NULL |
Sample data:
| department_code | department_id | department_name |
|---|---|---|
| CS | 1 | Computer Science |
| MATH | 2 | Mathematics |
| PHYS | 3 | Physics |
Table students
| column | type | constraints |
|---|---|---|
| student_id | INT | PRIMARY KEY |
| student_name | VARCHAR(100) | NOT NULL |
| major_department_id | INT | FOREIGN KEY REFERENCES departments(department_id) |
Sample data:
| major_department_id | student_id | student_name |
|---|---|---|
| 1 | 1 | Alice Johnson |
| 1 | 2 | Bob Smith |
| 2 | 3 | Carol Lee |
| 2 | 4 | David Kim |
Table courses
| column | type | constraints |
|---|---|---|
| course_id | INT | PRIMARY KEY |
| course_code | VARCHAR(20) | NOT NULL |
| course_name | VARCHAR(200) | NOT NULL |
| department_id | INT | NOT NULL, FOREIGN KEY REFERENCES departments(department_id) |
| credits | INT | NOT NULL |
Sample data:
| course_code | course_id | course_name | credits | department_id |
|---|---|---|---|---|
| CS101 | 1 | Intro to Computer Science | 3 | 1 |
| MATH201 | 2 | Calculus II | 4 | 2 |
| CS201 | 3 | Data Structures | 3 | 1 |
| PHYS101 | 4 | General Physics I | 4 | 3 |
| MATH101 | 5 | Calculus I | 3 | 2 |
Table terms
| column | type | constraints |
|---|---|---|
| term_id | INT | PRIMARY KEY |
| term_code | VARCHAR(20) | NOT NULL |
| start_date | DATE | NOT NULL |
| end_date | DATE | NOT NULL |
Sample data:
| end_date | start_date | term_code | term_id |
|---|---|---|---|
| 2024-12-15 | 2024-09-01 | FALL2024 | 1 |
| 2025-05-15 | 2025-01-15 | SPRING2025 | 2 |
| 2025-08-15 | 2025-06-10 | SUMMER2025 | 3 |
Table enrollments
| column | type | constraints |
|---|---|---|
| enrollment_id | INT | PRIMARY KEY |
| student_id | INT | NOT NULL, FOREIGN KEY REFERENCES students(student_id) |
| course_id | INT | NOT NULL, FOREIGN KEY REFERENCES courses(course_id) |
| term_id | INT | NOT NULL, FOREIGN KEY REFERENCES terms(term_id) |
| grade | VARCHAR(2) |
Sample data:
| course_id | enrollment_id | grade | student_id | term_id |
|---|---|---|---|---|
| 1 | 1 | A | 1 | 1 |
| 2 | 2 | B | 1 | 1 |
| 3 | 3 | A | 1 | 2 |
| 1 | 4 | C | 2 | 1 |
| 2 | 5 | B | 2 | 2 |
| 2 | 6 | A | 3 | 1 |
| 4 | 7 | B | 3 | 1 |
| 5 | 8 | A | 4 | 2 |
| 1 | 9 | A | 4 | 2 |
| 2 | 10 | A | 4 | 2 |
| 4 | 11 | C | 3 | 2 |
Expected output
One row per student who, in at least one term, enrolled in both CS101 and MATH201 in that same term. Columns: student_id, student_name.
| student_id | student_name |
|---|---|
| 1 | Alice Johnson |
| 4 | David Kim |
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.
Table departments
| column | type | constraints |
|---|---|---|
| department_id | INT | PRIMARY KEY |
| department_code | VARCHAR(10) | NOT NULL, UNIQUE |
| department_name | VARCHAR(100) | NOT NULL |
Sample data:
| department_code | department_id | department_name |
|---|---|---|
| CS | 1 | Computer Science |
| MATH | 2 | Mathematics |
| PHYS | 3 | Physics |
Table students
| column | type | constraints |
|---|---|---|
| student_id | INT | PRIMARY KEY |
| student_name | VARCHAR(100) | NOT NULL |
| major_department_id | INT | FOREIGN KEY REFERENCES departments(department_id) |
Sample data:
| major_department_id | student_id | student_name |
|---|---|---|
| 1 | 1 | Alice Johnson |
| 1 | 2 | Bob Smith |
| 2 | 3 | Carol Lee |
| 2 | 4 | David Kim |
Table courses
| column | type | constraints |
|---|---|---|
| course_id | INT | PRIMARY KEY |
| course_code | VARCHAR(20) | NOT NULL |
| course_name | VARCHAR(200) | NOT NULL |
| department_id | INT | NOT NULL, FOREIGN KEY REFERENCES departments(department_id) |
| credits | INT | NOT NULL |
Sample data:
| course_code | course_id | course_name | credits | department_id |
|---|---|---|---|---|
| CS101 | 1 | Intro to Computer Science | 3 | 1 |
| MATH201 | 2 | Calculus II | 4 | 2 |
| CS201 | 3 | Data Structures | 3 | 1 |
| PHYS101 | 4 | General Physics I | 4 | 3 |
| MATH101 | 5 | Calculus I | 3 | 2 |
Table terms
| column | type | constraints |
|---|---|---|
| term_id | INT | PRIMARY KEY |
| term_code | VARCHAR(20) | NOT NULL |
| start_date | DATE | NOT NULL |
| end_date | DATE | NOT NULL |
Sample data:
| end_date | start_date | term_code | term_id |
|---|---|---|---|
| 2024-12-15 | 2024-09-01 | FALL2024 | 1 |
| 2025-05-15 | 2025-01-15 | SPRING2025 | 2 |
| 2025-08-15 | 2025-06-10 | SUMMER2025 | 3 |
Table enrollments
| column | type | constraints |
|---|---|---|
| enrollment_id | INT | PRIMARY KEY |
| student_id | INT | NOT NULL, FOREIGN KEY REFERENCES students(student_id) |
| course_id | INT | NOT NULL, FOREIGN KEY REFERENCES courses(course_id) |
| term_id | INT | NOT NULL, FOREIGN KEY REFERENCES terms(term_id) |
| grade | VARCHAR(2) |
Sample data:
| course_id | enrollment_id | grade | student_id | term_id |
|---|---|---|---|---|
| 1 | 1 | A | 1 | 1 |
| 2 | 2 | B | 1 | 1 |
| 3 | 3 | A | 1 | 2 |
| 1 | 4 | C | 2 | 1 |
| 2 | 5 | B | 2 | 2 |
| 2 | 6 | A | 3 | 1 |
| 4 | 7 | B | 3 | 1 |
| 5 | 8 | A | 4 | 2 |
| 1 | 9 | A | 4 | 2 |
| 2 | 10 | A | 4 | 2 |
| 4 | 11 | C | 3 | 2 |
Expected output
Top 3 departments by GPA for the term ending on 2025-05-15 (SPRING2025), with GPA weighted by course credits and rounded to 2 decimal places. Columns: department_id, department_name, avg_gpa.
| avg_gpa | department_id | department_name |
|---|---|---|
| 4 | 1 | Computer Science |
| 3.64 | 2 | Mathematics |
| 2 | 3 | Physics |
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.