Professional level · Lesson 20 of 20

Project: analytics for a learning platform

Revenue per course, completion rates and a leaderboard, using everything you've learned.

15 minutes3 games

You're now the platform's data analyst, and management wants three numbers for the next meeting. Each exercise is a real question that combines several things you've learned.

Same tables: students, courses and enrollments. Always start with a small exploration before writing the solution.

SQL

-- Explore first: one row per enrollment with everything joined
SELECT s.name, c.title, c.price, e.progress
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
ORDER BY c.title, s.name;

1. Revenue per course

Exercise

For each course with enrollments: title and its revenue as revenue (price × number of students), highest first, ties by title.

Expected result:

title | revenue
JavaScript | 1350
Python | 1200
React | 600
SQL | 600
HTML & CSS | 0

SQL

SELECT c.title
FROM enrollments e
JOIN courses c ON c.id = e.course_id

2. Completion rate

Exercise

For each course with enrollments: title, the number of students students, and how many finished (progress = 100) as finished. Order by title.

Expected result:

title | students | finished
HTML & CSS | 3 | 2
JavaScript | 3 | 0
Python | 3 | 1
React | 1 | 0
SQL | 2 | 0

SQL

SELECT c.title
FROM enrollments e
JOIN courses c ON c.id = e.course_id

3. The leaderboard

Exercise

The top 3 students by XP: place (using RANK), name, xp, and their number of courses as courses (even if zero). Exclude students whose XP is NULL.

Expected result:

place | name | xp | courses
1 | Nour | 410 | 2
2 | Laila | 320 | 3
3 | Mariam | 275 | 2

SQL

SELECT s.name, s.xp
FROM students s

Learn more from

Played 0 of 3 games

SQL

Professional level · Lesson 20 of 20

Project: analytics for a learning platform

Revenue per course, completion rates and a leaderboard, using everything you've learned.

15 minutes3 games

You're now the platform's data analyst, and management wants three numbers for the next meeting. Each exercise is a real question that combines several things you've learned.

Same tables: students, courses and enrollments. Always start with a small exploration before writing the solution.

SQL

-- Explore first: one row per enrollment with everything joined
SELECT s.name, c.title, c.price, e.progress
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
ORDER BY c.title, s.name;

1. Revenue per course

Exercise

For each course with enrollments: title and its revenue as revenue (price × number of students), highest first, ties by title.

Expected result:

title | revenue
JavaScript | 1350
Python | 1200
React | 600
SQL | 600
HTML & CSS | 0

SQL

SELECT c.title
FROM enrollments e
JOIN courses c ON c.id = e.course_id

2. Completion rate

Exercise

For each course with enrollments: title, the number of students students, and how many finished (progress = 100) as finished. Order by title.

Expected result:

title | students | finished
HTML & CSS | 3 | 2
JavaScript | 3 | 0
Python | 3 | 1
React | 1 | 0
SQL | 2 | 0

SQL

SELECT c.title
FROM enrollments e
JOIN courses c ON c.id = e.course_id

3. The leaderboard

Exercise

The top 3 students by XP: place (using RANK), name, xp, and their number of courses as courses (even if zero). Exclude students whose XP is NULL.

Expected result:

place | name | xp | courses
1 | Nour | 410 | 2
2 | Laila | 320 | 3
3 | Mariam | 275 | 2

SQL

SELECT s.name, s.xp
FROM students s

Learn more from

Played 0 of 3 games