Duolingo mid GROUP_CONCATCOALESCECOUNT(DISTINCT)GROUP BYAVGORDER BY

For each user in learning_activity, calculate total_xp as the sum of xp_earned (treating a NULL xp_earned, from a streak-freeze day, as 0). Calculate avg_xp_per_day as the average xp_earned across all logged days for that user, also treating NULL as 0 — a freeze day should pull the average down, not be excluded from it. Calculate language_count as the number of distinct languages the user has activity for, and languages_list as those distinct language names joined into a single comma-separated string, sorted alphabetically. Return user_name, total_xp, avg_xp_per_day (rounded to 2 decimal places), language_count, and languages_list, sorted by total_xp in descending order.

Duolingo · Learner Engagement — practice this real-world SQL scenario live in your browser.

📖 Story Duolingo · Learner Engagement

Monday morning at Duolingo's Learner Engagement team. The learning_activity table logs one row every time a user opens a lesson or uses a 'streak freeze' to protect their streak without actually studying — a freeze day still gets logged with a language and a date, but its xp_earned is NULL, not zero, because no lesson was completed. The team wants a per-user engagement summary: total XP, a fair daily average that doesn't quietly ignore freeze days, and which languages each learner is actively juggling.

🎯 Your Mission

📋 Table Structure

🗂 learning_activity

id INTEGER 1

user_name TEXT Meera

language TEXT Spanish

xp_earned INTEGER 20

activity_date TEXT 2026-07-20

⚡ Step-by-Step Walkthrough

Look at the raw learning activity log

query.sql

SELECT *
FROM learning_activity
ORDER BY user_name, activity_date;

💡 Explanation

learning_activity logs one row per day a user interacts with the app, not one row per user — user_name repeats once for every lesson day or freeze day.
Rohan's 2026-07-21 row has NULL xp_earned — the streak-freeze protected his streak that day without him completing a lesson, which is different from a day where he studied and simply earned 0 XP.
Divya has two separate Spanish rows (2026-07-20 and 2026-07-23) — she studies Spanish on more than one day, but it's still one language, a detail that matters once distinct languages get counted.
Nothing is aggregated yet — this step just confirms what a day-by-day activity log looks like before summarizing it per user.

Total XP and a NULL-safe daily average per user

query.sql

SELECT user_name,
       SUM(COALESCE(xp_earned, 0)) AS total_xp,
       ROUND(AVG(COALESCE(xp_earned, 0)), 2) AS avg_xp_per_day
FROM learning_activity
GROUP BY user_name
ORDER BY user_name;

💡 Explanation

SUM(COALESCE(xp_earned, 0)) turns Rohan's NULL freeze day into a 0 before adding it up — SUM already skips NULLs on its own, so this step wouldn't change his total_xp either way, but COALESCE makes that stop being an accident and start being explicit.
AVG is where COALESCE actually changes the answer: AVG(xp_earned) without COALESCE would divide only by the days that have a non-NULL value, quietly excluding the freeze day from the denominator entirely, not just from the numerator.
Rohan logged 3 days (two lessons plus one freeze) worth 25, NULL, and 30 XP. Without COALESCE, AVG(xp_earned) computes (25+30)/2 = 27.5 — the same average as Divya's, who genuinely studied hard every single day. With COALESCE, it's (25+0+30)/3 = 18.33, correctly reflecting that a third of his logged days had no studying at all.
Meera and Divya have no NULL rows, so COALESCE makes no visible difference for them here — the gap only shows up for a user who actually has a freeze day, which is exactly the kind of bug that hides until the right row shows up in production data.

Add distinct language count and a comma-separated language list

query.sql

SELECT user_name,
       SUM(COALESCE(xp_earned, 0)) AS total_xp,
       ROUND(AVG(COALESCE(xp_earned, 0)), 2) AS avg_xp_per_day,
       COUNT(DISTINCT language) AS language_count,
       GROUP_CONCAT(DISTINCT language ORDER BY language) AS languages_list
FROM learning_activity
GROUP BY user_name
ORDER BY total_xp DESC;

💡 Explanation

COUNT(DISTINCT language) counts unique language names per user, not rows — Divya has 4 activity rows but only 3 distinct languages, since two of those rows are both Spanish.
GROUP_CONCAT(DISTINCT language ORDER BY language) builds languages_list as one string per user, deduplicating the same way COUNT(DISTINCT language) does — without DISTINCT, Divya's list would repeat 'Spanish' twice instead of listing each language once.
SQLite's GROUP_CONCAT only accepts ORDER BY alongside DISTINCT when no custom separator is also supplied — DISTINCT aggregates take exactly one argument, so the alphabetical order here relies on the default comma separator rather than a hand-picked one.
The final ORDER BY total_xp DESC sorts users by their overall total, unrelated to the ORDER BY tucked inside GROUP_CONCAT — that inner ORDER BY only controls the order languages appear in within each user's own comma-separated string.

▶ Practice this live ← Browse all questions