Master SQL Window Functions for Advanced Data Analysis

✅ Solved a SQL problem on StrataScratch — Day 53 of my SQL Journey 💪 Data isn’t always clean… Sometimes it comes packed inside a single column 📦 Today’s problem was about analysing business categories — But the twist? Multiple categories were stored in one field. The approach: • Split comma-separated categories into individual rows • Used SUBSTRING_INDEX() to extract each category • Generated sequence numbers to iterate through values • Aggregated total reviews per category • Sorted to identify the most reviewed categories What I practised: • String manipulation in SQL • Handling multi-value fields • Using LENGTH + REPLACE for dynamic splitting • Transforming unstructured data into an analysable format What stood out — Real-world data is rarely perfect. Sometimes the problem isn’t analysis… It’s preparing the data so analysis becomes possible. Once you break structure out of chaos, insights start to appear naturally. Consistent learning, one query at a time 🚀 #SQL #StrataScratch #DataAnalytics #LearningInPublic #SQLPractice

🚀Day 87 of My 100 Days Data Analysis Journey This is what SQL looks like when everything finally connects. Not scattered commands. Not random syntax. But a clear system that controls how data is filtered, grouped, combined, and understood. At a glance, this breaks SQL into its core building blocks: WHERE, defines what matters GROUP BY & HAVING, turns raw data into meaningful segments ORDER BY, brings structure and clarity to results JOINS, connects multiple tables into one complete view FUNCTIONS, summarize data into insights ALIAS (AS), improves readability and interpretation Then comes precision: LIKE, IN, BETWEEN, EXISTS AND, OR, NOT Each one is small on its own. Together, they form a system that answers complex questions. The real shift happens here: SQL stops being something to memorize and becomes something to think with. That is where real analysis begins. #DataAnalytics #SQL #LearningInPublic #100DaysOfCode #DataSkills #TechJourney

DAY 18 Understanding Data Questions: The Real Skill Behind SQL Anyone can learn SQL syntax, but the real magic starts before you even touch the keyboard. Understanding what the data question is really asking is half the battle. Is it about trends, comparisons, or anomalies? Are we summarizing individual records or aggregated patterns? Do we need a single metric or a story from multiple joined tables? Once you truly understand the question, you can pick the right SQL tool for the job: GROUP BY + aggregates for summaries and KPIs JOINs to connect relationships across datasets CASE WHEN for conditional logic WHERE for filtering rows based on condition The stronger your grasp of data logic, the more powerful your SQL becomes. It’s not just about writing queries it’s about turning questions into insights. #DataAnalytics #SQL #DataAnalysis #BusinessIntelligence #DataThinking

𝗧𝘄𝗼 𝗦𝗤𝗟 𝗳𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀. 𝗢𝗻𝗲 𝘀𝗺𝗮𝗹𝗹 𝗱𝗶𝗳𝗳𝗲𝗿𝗲𝗻𝗰𝗲. 𝗕𝘂𝘁 𝗶𝘁 𝗰𝗮𝗻 𝗰𝗵𝗮𝗻𝗴𝗲 𝘆𝗼𝘂𝗿 𝗿𝗲𝘀𝘂𝗹𝘁𝘀 𝗰𝗼𝗺𝗽𝗹𝗲𝘁𝗲𝗹𝘆. After finishing my 21 Days of SQL challenge, I decided to continue sharing small SQL insights that are easy to miss but important to understand. Today’s tip 👇 COUNT(*) vs COUNT(column) At first glance, these two look almost the same. But they behave very differently when NULL values are present. COUNT(*) Counts every row in the table, regardless of NULL values. SELECT COUNT(*) FROM orders; COUNT(column) Counts only rows where the specified column is NOT NULL. SELECT COUNT(discount) FROM orders; So if the discount column contains NULL values, those rows will not be counted. 💡 Why this matters In real datasets, NULL values are very common. Using the wrong count method can lead to incorrect analysis and misleading results. Key takeaway COUNT(*) → counts rows COUNT(column) → counts non-NULL values Small SQL details like this make a big difference in data analysis. Curious to know 👇 Did you know this difference before, or did it surprise you? #SQL #DataAnalytics #LearningInPublic #SQLTips #DataAnalyticsJourney

Most analysts reach for self-joins when they need rankings and running totals. There is a better way. SQL Window Functions let you perform calculations across a set of rows related to the current row, without collapsing your result set or writing expensive self-joins. Once you understand how the OVER clause works, your queries become cleaner, faster, and far easier to maintain. Here are five window functions worth mastering: 1. ROW_NUMBER() — Assign a unique sequential rank to each row within a partition, perfect for deduplication logic. 2. RANK() and DENSE_RANK() — Rank rows with ties handled differently; choose based on whether gaps in ranking matter to your use case. 3. SUM() OVER() — Calculate running totals without a subquery, ideal for financial and time-series analysis. 4. LAG() and LEAD() — Access previous or next row values in a single pass, eliminating the need for self-joins entirely. 5. NTILE(n) — Distribute rows into n buckets for percentile-based segmentation and reporting. The real performance gain comes from how SQL Server processes these functions. A single table scan with a window frame is almost always cheaper than joining a table to itself, especially at scale. If you are still writing self-joins to compare rows or accumulate totals, it is time to revisit your approach. Window functions are not advanced syntax reserved for data scientists. They are a core skill every data engineer and analyst should have in daily rotation. #SQLServer #DataEngineering #SQLPerformance #WindowFunctions #DataAnalytics

Day 28/30 Today’s sql class was a reminder that data analysis is not just about writing queries,it’s about making decisions through structure. On the surface, this looks like SQL. Tables, queries, outputs. But what we worked on was deeper than that. We took raw data and applied logic to categorize it, defining what is cheap, moderate, or expensive. And that right there is the work. Because data on its own doesn’t carry meaning. The analyst gives it meaning. How you group it. How you define it. How you choose to interpret it. That’s what shapes the insight. At the end of the day, business decisions are not made from raw tables, they’re made from structured, interpreted insight. Still building. Still refining. Still showing up. #Day28 #SQL #DataAnalytics #LearningInPublic #DataThinking #CareerGrowth

I used to handle running totals and rankings by self-joining tables back to themselves. It was messy, the performance was usually terrible, and it made the queries unreadable for anyone else on the team. Then I finally stopped ignoring Window Functions. The transition from "Aggregating/Grouping" to "Windowing" is probably the biggest jump in productivity you can make in SQL. The difference is simple: GROUP BY collapses your data. You lose the individual row details to get the summary. Window Functions keep your data alive. They let you peek at the total, the previous row, or the next row without destroying the granularity of your original table. My daily driver list for pipelines: LAG() / LEAD(): Essential for calculating time-deltas between user events (like session duration). DENSE_RANK(): The only clean way to handle ties when identifying top performers or latest records. SUM() OVER(): The cleanest way to get a running total without a self-join in sight. ROW_NUMBER(): Still the best way to deduplicate data in an ETL pipeline. If you are still struggling with them, don't focus on the syntax. Focus on the Frame. PARTITION BY is just saying: "Reset the calculation here." ORDER BY is just saying: "The order matters for this specific calculation." Once you visualize the "frame" moving across your rows, the mystery disappears. What was the specific problem that finally forced you to learn Window Functions? (For me, it was trying to calculate sessionization on web logs). #DataEngineering #SQL #Analytics #DataPipeline #LearningInPublic

🚀 Day 86 of My 100 Days Data Analysis Journey. If you only use SQL to query data, you’re barely scratching the surface. There’s a deeper layer most beginners don’t see early enough. SQL isn’t just about pulling data… It’s about designing how data lives. Today’s focus shifted into: Creating structured tables Defining PRIMARY KEYS for uniqueness Linking tables using FOREIGN KEYS Applying constraints to maintain clean, reliable data Because here’s what changes everything: Well-structured data makes analysis easy. Poorly structured data makes even simple queries painful. At this stage, it stops being about syntax… and starts becoming about thinking in systems. That’s the shift. 💡 #DataAnalytics #SQL #DatabaseDesign #LearningInPublic #100DaysOfCode #TechJourney

🚀 𝗠𝗮𝘀𝘁𝗲𝗿 𝗦𝗤𝗟 𝗪𝗶𝗻𝗱𝗼𝘄 𝗙𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀 If you're working with SQL and still relying heavily on GROUP BY, it's time to level up your skills. Window Functions allow you to perform calculations across rows without collapsing your dataset. This means you can: ✔ Rank data ✔ Calculate running totals ✔ Compare rows within the same result set All while keeping your original data intact. 🔍 𝗗𝗶𝗳𝗳𝗲𝗿𝗲𝗻𝗰𝗲 𝗕𝗲𝘁𝘄𝗲𝗲𝗻 𝗥𝗔𝗡𝗞(), 𝗗𝗘𝗡𝗦𝗘_𝗥𝗔𝗡𝗞() & 𝗥𝗢𝗪_𝗡𝗨𝗠𝗕𝗘𝗥() These are the most commonly used window functions for ranking 👇 👉 𝗥𝗢𝗪_𝗡𝗨𝗠𝗕𝗘𝗥() Assigns a unique number to each row. Even if values are the same, the numbering will be different. 👉 𝗥𝗔𝗡𝗞() Gives the same rank to duplicate values, but skips the next rank. Example: 1, 2, 2, 4 👉 𝗗𝗘𝗡𝗦𝗘_𝗥𝗔𝗡𝗞() Also gives the same rank to duplicates, but does NOT skip ranks. Example: 1, 2, 2, 3 💡 𝗪𝗵𝗲𝗻 𝘁𝗼 𝗨𝘀𝗲 𝗪𝗵𝗮𝘁? Use 𝗥𝗢𝗪_𝗡𝗨𝗠𝗕𝗘𝗥() → when you need unique ordering (no duplicates) Use 𝗥𝗔𝗡𝗞() → when gaps in ranking are acceptable Use 𝗗𝗘𝗡𝗦𝗘_𝗥𝗔𝗡𝗞() → when you want continuous ranking #SQL #WindowFunctions #DataAnalytics #LearnSQL #SQLInterview #DataAnalyst #DataEngineering

SQL Execution Order (not how we write it, but how it actually runs) Most of us write queries like this: SELECT → FROM → WHERE → GROUP BY → ORDER BY But internally, SQL processes it very differently. SQL executes in this order: FROM JOIN WHERE GROUP BY HAVING SELECT DISTINCT ORDER BY LIMIT Here’s a simpler way to think about it FILTER → SHOW → SORT → LIMIT What this actually means • FILTER → FROM, JOIN, WHERE, GROUP BY, HAVING (Define data + reduce it step by step) • SHOW → SELECT, DISTINCT (Choose what you want to display) • SORT → ORDER BY (Organize the result) • LIMIT → LIMIT / TOP (Control how much data you return) Once we start thinking in execution order, we stop “trial and error” and start writing SQL with confidence. If you’re working with SQL daily, this mental model makes a huge difference. #SQL #DataAnalytics #LearnSQL #SQLTips #DataEngineering #Analytics