SQL Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK Explained

💡 Quick SQL Trick: Count Vowels in a String Want to count vowels (a, e, i, o, u) in a column? Use LENGTH and REPLACE for a clean solution: ✅ How it works: 🔹 REPLACE(name, 'a', '') removes 'a' from the string. 🔹 Subtracting the new length from the original length gives the count of 'a'. 🔹 Repeat for all vowels and sum them up. 🔹 This approach is simple, fast, and works in almost any SQL flavor! #SQLTips #DataScience #Database #VowelCount #CodingMadeEasy #LearnSQL

“Here is some basic SQL notes… saved for future me (and anyone else who needs it).” Because let’s be honest — we all learn SQL… and then forget the syntax the moment we actually need it 😅 So instead of pretending I’ll remember everything, I decided to document the basics: - Core queries - Filtering - Aggregations - Joins & more Nothing fancy. Just the stuff that actually gets used. Sometimes the smartest thing in tech isn’t knowing everything… it’s knowing where you saved it 😉 #SQL #DataAnalytics #DataScience #LearningInPublic #TechNotes

✅ Solved a SQL problem on StrataScratch — Day 51 of my SQL Journey 💪 After 50 days on LeetCode, I’ve started focusing more on real-world, business-driven SQL problems 📊 Today’s problem was about analysing how rankings change over time — not just activity, but improvement in position. I worked on: • Aggregating total comments per country by month • Ranking countries within each month using DENSE_RANK() • Comparing rankings between December and January • Identifying countries whose rank improved What I practised: • Window functions for ranking • Time-based aggregation using DATE functions • Comparing metrics across time periods • Turning raw activity into performance insights What stood out — Growth isn’t always about higher numbers. It’s about moving ahead. A country can increase activity… Yet still fall behind others. That’s why relative performance matters more than absolute values. SQL helps uncover not just change… but meaningful change. Consistent learning, one query at a time 🚀 #SQL #StrataScratch #DataAnalytics #LearningInPublic #SQLPractice

💡 RANK() vs DENSE_RANK() vs ROW_NUMBER() in SQL 👉 If you're working with SQL window functions, understanding the difference between these three is very important. 1️⃣ RANK() ▪️ The same values get the same rank. ▪️ Skips numbers after ties 2️⃣ DENSE_RANK() ▪️ Same values get the same rank ▪️ No gaps in ranking 3️⃣ Row_Number() ▪️ Assigns a unique number to each row ▪️ No duplicates, even if values are the same 🔍 Key Difference ✔️ RANK() - Skips ranks on ties ✔️ DENSE_RANK() - No gaps in ranks ✔️ ROW_NUMBER() - Always unique #SQL #DataScience #DataAnalytics #Database #TechTips #LearningSQL #InterviewPrep

The query that taught me the most about SQL performance: A query running 4 minutes on a 50M row table. The fix took 30 seconds. Here's what I learned: The query had a WHERE clause filtering on YEAR(transaction_date) = 2023. This function-wrapped column prevented partition pruning. The database scanned all 50M rows. Fix: WHERE transaction_date >= '2023-01-01' AND transaction_date < '2024-01-01' Result: 9 seconds. The partition pruning now skipped 11 of 12 monthly partitions. The lesson: any function applied to a filtered column in a WHERE clause breaks partition pruning and index usage. Common culprits: → DATE(), YEAR(), MONTH() on date columns → CAST() or CONVERT() on join columns → LOWER() or UPPER() on string filter columns Write predicates that let the engine use its optimization structures. #SQL #QueryOptimization #DataEngineering #Performance #Snowflake

Day 15 of my SQL series Today I covered window functions using ROW_NUMBER. This allows us to rank data without grouping it — a very powerful concept in SQL. Next: RANK & DENSE_RANK #SQL #DataAnalytics #Learning #CareerGrowth

🚀 Day 18: Mastering SQL Window Functions! ⚡ Today I moved beyond basic aggregations and unlocked one of the most powerful features in SQL: Window Functions (OVER, PARTITION BY). Unlike standard GROUP BY clauses that collapse your rows, Window Functions allow you to calculate values across a group while keeping the detail of every single record. What I achieved today: ✅ Competitive Ranking: Used DENSE_RANK() to rank products by price within their specific categories (e.g., finding the #1 most expensive electronic). ✅ Financial Tracking: Created a Running Total using SUM() OVER. This is exactly how bank statements or inventory logs are calculated! Understanding how to "partition" data is a total game-changer for building dashboards and reports. 📊 #SQL #DataAnalytics #100DaysOfCode #WindowFunctions #DataScience #PostgreSQL #TechSkills

🚀 Day 13 – Data Analysis Journey | Daily SQL Challenge Continuing my daily SQL practice on HackerRank 💻 🧩 Problem Query the following two values from the STATION table: • The sum of all values in LAT_N rounded to 2 decimal places • The sum of all values in LONG_W rounded to 2 decimal places 💡 Approach • Used SUM() to calculate total values for both columns • Applied ROUND() function to limit the result to 2 decimal places • Combined both calculations into a single SELECT statement 🧠 Solution SELECT ROUND(SUM(LAT_N), 2), ROUND(SUM(LONG_W), 2) FROM STATION; ⚠️ Challenge Faced Initially, I wrote two separate queries for LAT_N and LONG_W But realized that HackerRank expects the output in a single row with two columns. This helped me understand: 👉 Multiple aggregations can be done in one query 👉 Output format matters as much as logic 📚 Key Learning Efficient SQL queries combine multiple calculations in a single statement, improving performance and readability. 🔥 Staying consistent and improving SQL skills step by step! #Day13 #30DaysOfSQL #SQL #DataAnalytics #LearningJourney #HackerRank #Consistency #Aggregation 🚀

Day 35/90 — Week 5 complete. Phase 2 has officially started and this week was big. Here is the full cheat sheet — save it. CASE WHEN: → SQL's if/else — classify rows into labelled segments → Use with GROUP BY to count each segment in one query Subquery in WHERE: → WHERE amount > (SELECT AVG(amount) FROM orders) → Filters dynamically — recalculates every time Subquery in FROM: → Wrap a query in FROM to create a virtual table → Always alias it — FROM (...) AS my_table Correlated subquery: → References the outer query's columns → Runs once per row — powerful but can be slow on large data Master query using all of this week: Filter above-average orders → classify each city by tier → count per tier All in a single SQL statement. Week 6 starts Monday — CTEs. The cleaner, more readable alternative to nested subqueries. Follow so you don't miss it. Tag someone who is learning SQL and needs this cheat sheet. #SQL #CaseWhen #Subquery #DataAnalytics #LearnSQL #Week5 #SQLCheatSheet #DataAnalyst