Duolingo Leaderboards
HardDuolingo 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.
Related courses
