COUNT(*) vs COUNT(column) SQL difference explained

Day 4/30 of SQL Challenge Today I learned: -> LIMIT Key idea: LIMIT is used to control how many rows are returned in a query result. Example: SELECT * FROM products LIMIT 5; What I understood: When working with large datasets, we don’t always need all the data. LIMIT helps us quickly preview or focus on a smaller portion. Small insight: LIMIT is often used with ORDER BY to get top or bottom results. Example: SELECT name, price FROM products ORDER BY price DESC LIMIT 3; This returns the top 3 most expensive products. Practice thought: What if I want to skip some rows and then get results? Example: SELECT * FROM products LIMIT 5 OFFSET 5; This skips the first 5 rows and returns the next 5. Note: OFFSET is used to skip some data. In another day we just learn the OFFSET. #SQL #LearningInPublic #Data #BackendDevelopment #DataEngineer #DataAnalyst

🚨 Most SQL problems are not SQL problems. They are grain problems. A lot of analysts open SQL, join 3 tables, aggregate, and then wonder why the numbers are wrong. The failure usually starts before the first line of SQL: What does 1 row represent? That single question decides whether your output is correct or garbage. In real systems, grain is rarely clean. One table may be: 1 row per transaction Another may be: multiple status updates for the same transaction Another may be: multiple fee records for the same transaction Now someone joins all 3 and writes: count(*) sum(amount) Looks normal. It is not normal. It is a multiplier. That is how dashboards end up showing: inflated transaction volumes duplicated revenue fake operational trends numbers that die in reconciliation ✅ The right approach is simple: Define the business question Define the target grain Reduce each source to that grain Then join Then aggregate SQL does not save you from bad logic. It scales it. The best analysts do not start with functions. They start with grain. #SQL #DataAnalytics #DataEngineering #BusinessIntelligence #Analytics #SQLTips #DataModeling

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

✅ 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

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 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 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

Indexed Columns vs Non‑Indexed Columns When working with databases or large datasets, how you store data can be just as important as the data itself. Here’s a simple breakdown: Indexed Columns Think of an index like a table of contents in a book. - Much faster searches and filtering - Improves overall query performance --Trade‑off: Uses more storage and can slow down INSERT / UPDATE / DELETE operations Best for: Columns frequently used in WHERE, JOIN, GROUP BY, or ORDER BY Non‑Indexed Columns This is like reading a book page by page. - Smaller storage footprint - No extra overhead on write operations - Slower query performance on large tables Best for: Columns rarely used in filters or joins How do you decide which columns to index in your environment? Let’s discuss in Comment section. #DataEngineering #SQL #Databases #PerformanceOptimization #Analytics #PowerBI #DataAnalytics #MashapaAnalytics

🚀 SQL Practice — Finding First Order Per Customer Today I worked on a very practical SQL problem: 👉 How to find the first order for each customer Here are two different approaches I explored: 🔹 Approach 1: Using ROW_NUMBER() (Window Function) WITH RankedOrders AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY dateTime ASC) as rn FROM samples.bakehouse.sales_transactions ) SELECT * FROM RankedOrders WHERE rn = 1; 🔹 Approach 2: Using Aggregation SELECT customerID, MIN(dateTime) AS first_order_date FROM samples.bakehouse.sales_transactions GROUP BY customerID; 💡 Key Learnings: ROW_NUMBER() gives full row details (more flexible) MIN() is simpler but only returns the date Window functions are powerful when you need complete records, not just aggregates 📌 Real-world use cases: Customer onboarding analysis First purchase tracking Retention & cohort analysis Small steps every day toward mastering SQL for Data Engineering 💪 #SQL #DataEngineering #WindowFunctions #LearningInPublic #Analytics #DataSkills

🚀 #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