Tinder easy JOINGROUP BYCOUNTORDER BY

For each user, count the total number of likes they have sent to others. Show user name along with the count, sorted by count in descending order.

Tinder · Engagement Analytics — practice this real-world SQL scenario live in your browser.

📖 Story Tinder · Engagement Analytics

Tuesday afternoon, Tinder HQ. Intern Kabir is shadowing the analytics team and gets his first real task: 'Just tell me which users are the most active swipers — who's sending out the most likes?' Simple question, but Kabir needs the users and likes tables to answer it properly.

🎯 Your Mission

📋 Table Structure

🗂 users

user_id INTEGER 1

name TEXT Aditi

gender TEXT Female

city TEXT Mumbai

🗂 likes

like_id INTEGER 1

from_user_id INTEGER 1

to_user_id INTEGER 2

like_date TEXT 2024-05-01

⚡ Step-by-Step Walkthrough

Join users with likes on the sender side

query.sql

SELECT u.user_id,
       u.name,
       l.like_id
FROM users u
JOIN likes l ON u.user_id = l.from_user_id;

💡 Explanation

We need to connect each like to the user who sent it, not the user who received it. The from_user_id column in likes is what tells us who initiated the like.
JOIN users u JOIN likes l ON u.user_id = l.from_user_id — this matches every like row to the user in the users table who has that same id as from_user_id.
A plain JOIN (not LEFT JOIN) is fine here because we specifically want to look at like activity, and any user who exists will still show up once we group later — plain JOIN is used since this is just a warm-up step to see the raw connection.
Notice we did NOT use to_user_id here. That column tells us who received the like, which is a completely different question from who sent it.
Result of this step: one row per like, tagged with the name of the user who sent that like — the raw material we'll count in the next step.

Group by user and count total likes sent

query.sql

SELECT u.user_id,
       u.name,
       COUNT(l.like_id) AS likes_sent
FROM users u
JOIN likes l ON u.user_id = l.from_user_id
GROUP BY u.user_id, u.name
ORDER BY likes_sent DESC;

💡 Explanation

GROUP BY u.user_id, u.name takes all the individual like rows from the previous step and collapses the ones belonging to the same sender into a single row per user.
COUNT(l.like_id) counts how many like rows landed inside each group — that number is exactly how many likes that user sent out in total.
Why COUNT(l.like_id) and not COUNT(*)? Both give the same answer here since we used a plain JOIN, but COUNT(l.like_id) is a good habit because it always counts only actual like records, staying accurate even if the query structure changes later.
ORDER BY likes_sent DESC puts the most active swipers at the top of the list, which is exactly what Kabir's task asked for — 'who's sending out the most likes.'
If we skipped GROUP BY entirely, we'd be back to one row per like with no summary at all — the count column wouldn't even make sense without it.
Notice this query only lists users who have sent at least one like. Anyone who never sent a like simply won't appear, because we used JOIN, not LEFT JOIN, in the previous step.

Final query with clean output

query.sql

SELECT u.name AS user_name,
       COUNT(l.like_id) AS likes_sent
FROM users u
JOIN likes l ON u.user_id = l.from_user_id
GROUP BY u.user_id, u.name
ORDER BY likes_sent DESC;

💡 Explanation

This final version is functionally identical to the previous step — the only change is we drop u.user_id from the SELECT list since Kabir only needs the name, not the internal id, in his report.
We still GROUP BY u.user_id, u.name even though user_id isn't shown, because grouping by name alone is risky — two different users could theoretically share the same name, and grouping only by name would wrongly merge their counts together.
AS user_name and AS likes_sent give the output columns clean, readable labels instead of raw SQL expressions — good practice for any report that a non-technical person like Kabir's manager will read.
Expected output (top rows): Rahul → 5 likes sent, then a mix of users around 2-3 likes sent each, since Rahul liked six different people while most others liked one or two.
This is a good example of 'clean up the final SELECT' — the underlying logic (JOIN, GROUP BY, COUNT, ORDER BY) doesn't change from step 2 to step 3, only the columns we choose to expose to the reader change.

▶ Practice this live ← Browse all questions