Student Report Pivot
ASKED IN INTERVIEWEd Tech
Problem Statement
Create a report showing Math, Science, and English scores as columns for every student.
Rules:
- Output columns: student_id, student_name, math_score, science_score, english_score.
- Missing scores must be 0.
- Sort by student_id ASC.
Tests your understanding of
Pivot, CASE WHEN, Aggregation and Joins
Input Tables
students
| student_id(INTEGER) | student_name(VARCHAR) |
|---|---|
| 1 | Alice |
| 2 | Bob |
grades
| student_id(INTEGER) | subject_name(VARCHAR) | score(INTEGER) |
|---|---|---|
| 1 | Math | 95 |
| 1 | Science | 88 |
| 2 | Math | 70 |
| 2 | English | 85 |
subjects
| subject_id(INTEGER) | subject_name(VARCHAR) |
|---|---|
| 101 | Math |
Expected Output
| student_id(INTEGER) | student_name(VARCHAR) | math_score(INTEGER) | science_score(INTEGER) | english_score(INTEGER) |
|---|---|---|---|---|
| 1 | Alice | 95 | 88 | 0 |
| 2 | Bob | 70 | 0 | 85 |
Tags
MediumASKED IN INTERVIEWPivotCASE WHENAggregationJoins
20-30 min
58%