Duolingo Leaderboards
Duolingo is a platform that allows users to learn various languages through bite-sized lessons. Learners can complete multiple lessons in a day and track their progress in their desired language.
You're given three tables: user, lesson, and lessons_completed:
user:
lesson:
lessons_completed:
Write a SQL query that returns a table with the top 3 learners with the highest total lessons completed from 24 Sep 2023 to 30 Sep 2023 for each language. Your output should contain: username, language, total_lessons, rank.
Note: If two or more learners have the same number of lessons completed and are within the top 3, include them all without skipping any rank positions for subsequent learners. For example, if three learners are tied for 1st place in a language, the next learner should still be ranked as 2nd, not 4th.
We first create a CTE RankedLessons to group by username and language and then use the DENSE_RANK() function to rank the learners based on their total lessons for each language. The PARTITION BY clause ensures that we rank the learners separately for each language.
After computing the rank for each learner, we filter out the ones with rank greater than 3 using the WHERE clause.
The final output is ordered by language, rank, and username to present the result clearly.
WITH RankedLessons AS (
SELECT
u.username,
l.language,
COUNT(lc.lesson_id) AS total_lessons,
DENSE_RANK() OVER(PARTITION BY l.language ORDER BY COUNT(lc.lesson_id) DESC) AS rank
FROM lessons_completed lc
JOIN user u ON lc.user_id = u.user_id
JOIN lesson l ON lc.lesson_id = l.lesson_id
WHERE lc.completed_date BETWEEN '2023-09-24' AND '2023-09-30'
GROUP BY u.username, l.language
)
SELECT
username,
language,
total_lessons,
rank
FROM RankedLessons
WHERE rank <= 3
ORDER BY language, rank, username;
The user table no longer exists as expected - I get an error that user does not contain user_id.
Note that querying the table results in only user:swuoevkivrjfta
select * FROM user