🎯𝐒𝐐𝐋 𝐒𝐞𝐫𝐢𝐞𝐬 | 𝐄𝐩. 𝟕 — 𝐆𝐑𝐎𝐔𝐏 𝐁𝐘 & 𝐃𝐈𝐒𝐓𝐈𝐍𝐂𝐓: 𝐔𝐧𝐝𝐞𝐫𝐬𝐭𝐚𝐧𝐝𝐢𝐧𝐠 𝐔𝐧𝐢𝐪𝐮𝐞𝐧𝐞𝐬𝐬 𝐢𝐧 𝐃𝐚𝐭𝐚

𝐖𝐡𝐲 𝐜𝐨𝐮𝐧𝐭𝐢𝐧𝐠 𝐨𝐧𝐜𝐞 𝐯𝐬 𝐦𝐚𝐧𝐲 𝐭𝐢𝐦𝐞𝐬 𝐦𝐚𝐭𝐭𝐞𝐫𝐬 𝐦𝐨𝐫𝐞 𝐭𝐡𝐚𝐧 𝐲𝐨𝐮 𝐭𝐡𝐢𝐧𝐤

🧠 𝐇𝐨𝐨𝐤:
Imagine you’re analyzing the 𝐎𝐥𝐲𝐦𝐩𝐢𝐜 𝐆𝐚𝐦𝐞𝐬 dataset.
You want to find 𝐰𝐡𝐢𝐜𝐡 𝐎𝐥𝐲𝐦𝐩𝐢𝐜 𝐞𝐝𝐢𝐭𝐢𝐨𝐧 𝐡𝐚𝐝 𝐭𝐡𝐞 𝐡𝐢𝐠𝐡𝐞𝐬𝐭 𝐧𝐮𝐦𝐛𝐞𝐫 𝐨𝐟 𝐮𝐧𝐢𝐪𝐮𝐞 𝐚𝐭𝐡𝐥𝐞𝐭𝐞𝐬.
If you used 𝐂𝐎𝐔𝐍𝐓(*), you’d count every row (including duplicates).
But what if the same athlete appears more than once in different events?
That’s where understanding uniqueness in SQL becomes critical.

💡 𝐂𝐨𝐧𝐜𝐞𝐩𝐭 𝐁𝐫𝐞𝐚𝐤𝐝𝐨𝐰𝐧:
1️⃣ 𝐃𝐈𝐒𝐓𝐈𝐍𝐂𝐓 helps you capture one-of-each: unique values or combinations.
2️⃣ 𝐆𝐑𝐎𝐔𝐏 𝐁𝐘 helps you aggregate by categories: one row per group, summarized values.
3️⃣ When you combine them: you get metrics like “number of unique athletes per Olympic edition”.

𝐔𝐬𝐞 𝐜𝐚𝐬𝐞 𝐫𝐮𝐥𝐞 𝐨𝐟 𝐭𝐡𝐮𝐦𝐛:
➡️ Use 𝐃𝐈𝐒𝐓𝐈𝐍𝐂𝐓 when you care about how many unique entities.
➡️ Use 𝐆𝐑𝐎𝐔𝐏 𝐁𝐘 when you care about how many / how much per category.

🧩 𝐄𝐱𝐚𝐦𝐩𝐥𝐞 𝐟𝐫𝐨𝐦 𝐒𝐭𝐫𝐚𝐭𝐚𝐒𝐜𝐫𝐚𝐭𝐜𝐡:
🔗 𝐋𝐚𝐫𝐠𝐞𝐬𝐭 𝐎𝐥𝐲𝐦𝐩𝐢𝐜𝐬: https://lnkd.in/e_tZYS6W

𝐁𝐮𝐬𝐢𝐧𝐞𝐬𝐬 𝐪𝐮𝐞𝐬𝐭𝐢𝐨𝐧:
“Find the Olympics edition (year + season) with the highest number of unique athletes.”

Get Somyagupta’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

𝐒𝐨𝐥𝐮𝐭𝐢𝐨𝐧 𝐬𝐤𝐞𝐭𝐜𝐡:

SELECT
games,
COUNT(DISTINCT id) AS athlete_count
FROM olympics_athletes_events
GROUP BY games
ORDER BY athlete_count DESC
LIMIT 1;

➡️ 𝐂𝐎𝐔𝐍𝐓(𝐃𝐈𝐒𝐓𝐈𝐍𝐂𝐓 𝐢𝐝) ensures each athlete is counted once per “games”.
➡️ 𝐆𝐑𝐎𝐔𝐏 𝐁𝐘 games allows us to summarise by each edition.
➡️ 𝐎𝐑𝐃𝐄𝐑 𝐁𝐘 … DESC picks the edition with most unique athletes.

🔍 𝐖𝐡𝐲 𝐭𝐡𝐢𝐬 𝐦𝐚𝐭𝐭𝐞𝐫𝐬:
By properly combining 𝐃𝐈𝐒𝐓𝐈𝐍𝐂𝐓 and 𝐆𝐑𝐎𝐔𝐏 𝐁𝐘, you avoid double-counting and get correct metrics.

𝐅𝐨𝐫 𝐞𝐱𝐚𝐦𝐩𝐥𝐞:
1. How many distinct customers bought each category?
2. Which quarter had the most unique new users?
3. Which product was launched by most distinct companies?
These are common real-world questions — and if you answer them incorrectly, you’ll mislead business decisions.

🧩 𝐓𝐫𝐲 𝐭𝐡𝐞𝐬𝐞 𝐩𝐫𝐨𝐛𝐥𝐞𝐦𝐬:
1️⃣ Marketing Campaign Success — Advanced → segment performance, unique users per campaign.
2️⃣ Actor Rating Difference Analysis → unique actors, movies, ratings across time.

Have you ever faced a metric that seemed “simple” but produced wrong results because of duplicates or missing grouping?
Share your story — let’s learn from each other. 👇

🚀 𝐂𝐨𝐦𝐢𝐧𝐠 𝐍𝐞𝐱𝐭:
𝐒𝐐𝐋 𝐒𝐞𝐫𝐢𝐞𝐬 | 𝐄𝐩. 𝟖 — 𝐇𝐀𝐕𝐈𝐍𝐆: 𝐅𝐢𝐥𝐭𝐞𝐫𝐢𝐧𝐠 𝐆𝐫𝐨𝐮𝐩𝐬, 𝐍𝐨𝐭 𝐑𝐨𝐰𝐬