Solved SQL problem on StrataScratch with substring index and aggregation

Ever feel like you're writing overly complex SQL queries with multiple self-joins just to calculate a simple running total or period-over-period growth? 🤯 Enter SQL Window Functions. They are an absolute game-changer for advanced data analysis, allowing you to perform calculations across a set of table rows related to the current row—all without collapsing your dataset like a standard GROUP BY does. I've put together this visual cheat sheet to break down the 6 key categories you need to know: 1️⃣ Core Concepts: Mastering the OVER() clause, partitioning, and ordering. 2️⃣ Simple Ranking: Unique numbering and distribution (ROW_NUMBER, NTILE). 3️⃣ Advanced Ranking: Handling ties like a pro (RANK, DENSE_RANK). 4️⃣ Relative Position: Looking forward and backward in time (LEAD, LAG). 5️⃣ Boundary Values: Extracting the first or last touchpoints (FIRST_VALUE, LAST_VALUE). 6️⃣ Aggregate-as-Window: Building running totals and moving averages. Bookmark this post for your next data modeling task! 📌 Which window function do you find yourself reaching for the most? Let me know in the comments! 👇 #SQL #DataAnalytics #DataEngineering #DataScience #BusinessIntelligence #TechTips #DataCommunity

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

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

🚀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

🚀 #30DaysOfSQL – Day 20 Continuing my 30 Days of SQL Challenge to strengthen my SQL and data analysis skills. 📌 Topic for Day 20: INDEX – Improving Query Performance Today I learned how indexes help speed up data retrieval in SQL. Indexes work like a table of contents, allowing the database to find data faster instead of scanning the entire table. 💡 Practice Example: Create an index on the salary column to improve search performance. 🧠 SQL Query: CREATE INDEX idx_salary ON employees(salary); 📊 Key Learnings: • INDEX improves query performance • Especially useful for large datasets • Too many indexes can slow down INSERT/UPDATE operations • Best used on columns frequently used in WHERE, JOIN, ORDER BY Understanding indexes is important for writing efficient and optimized SQL queries. Excited to continue this journey with Day 21! #SQL #30DaysOfSQL #DataScience #LearningInPublic #SQLPractice #DataAnalytics

Over time, I’ve realized SQL isn’t about writing complex queries - it’s about writing clear and efficient ones. Here are 3 SQL techniques I use almost daily: • CTEs (WITH clause) for breaking down complex logic • CASE WHEN for applying business rules directly in queries • Window functions for ranking, deduplication, and trend analysis These simple techniques have helped me write cleaner queries and make data easier to work with. Sometimes, it’s the basics done right that make the biggest difference. What SQL feature do you find yourself using the most? #SQL #Data #Analytics #DataAnalyst #BusinessIntelligence

Must-know SQL queries for Data Analysts. Revisiting the fundamentals — because strong basics make better analysts. Here’s a quick cheat sheet covering: • Filtering • Joins • Aggregations • Window functions • CTEs Simple. Practical. Useful #SQL #DataAnalytics #DataAnalyst #LearnSQL #Analytics #TechCareers #DataScience

Explored the fundamentals of SQL Joins and their role in combining data across multiple tables 🔗📊 From INNER, OUTER, SELF to CROSS JOIN — understanding when and how to use each type is key to ensuring accurate insights and efficient queries ⚡📈 The right join can transform raw data into meaningful information, while the wrong one can lead to inaccurate results ❗ Mastering joins is not just about syntax, but about understanding data relationships effectively 🧠💡 #SQL #DataAnalytics #LearningJourney #DatabaseManagement

✅ Solved a SQL problem on StrataScratch — Day 52 of my SQL Journey 💪 Numbers don’t mean much… until you compare them over time 📊 Today’s problem was about calculating the month-over-month percentage change in revenue — a simple metric, but one that tells a powerful story. The approach: • Aggregated total revenue per month • Joined each month with its previous month • Calculated percentage change using the classic MoM formula • Handled NULL cases for the first month cleanly What I practised: • Time-based aggregation using DATE_FORMAT() • Self joins for period comparison • Percentage calculations in SQL • Handling edge cases in analytical queries What stood out — Raw numbers show what happened. Comparisons show what changed. A revenue of 10K means nothing alone… but +25% or -10% tells the real story. That shift — from totals to trends — is what turns data into insight. Consistent learning, one query at a time 🚀 #SQL #StrataScratch #DataAnalytics #LearningInPublic #SQLPractice

Day 7: Mastering the Magic of SQL Window Functions! Today was a game-changer in my SQL journey. I dived deep into Window Functions, and I finally understand why they are a data analyst's best friend. Unlike standard aggregate functions, these allow me to look at individual rows while still calculating totals, averages, or rankings across a specific "window" of data. Key Takeaways from Today: PARTITION BY: This is like a "Group By" that doesn’t hide your rows. It breaks the data into logical chunks (e.g., grouping by Department) so the function can calculate values within those specific groups. OVER(): The "magic wand" that tells SQL, "Treat this as a window function." It defines exactly which rows the function should look at. RANK(): Perfect for finding the "Top N" items. It assigns a rank to each row based on a specific order. Window Frames (The "Rules"): I explored how to define moving windows using: ROWS PRECEDING: Looking back at previous rows. CURRENT ROW: Including the data right where we are. FOLLOWING: Peeking ahead. Why this matters? This is how we calculate Running Totals, Moving Averages, and Year-over-Year growth effortlessly. It’s the difference between seeing a flat table and seeing the story and trends within the data. Feeling more confident with every query! #SQL #DataAnalytics #LearningJourney #WomenInTech #DataScience #ContinuousLearning #SQLWindowFunctions