Avoid Grain Problems in SQL Queries

SQL Query Execution Order 3.98 In SQL, queries are executed in a specific order, which can be quite different from the order in which the clauses are written. Here's the logical order of SQL query execution: 1. FROM Specifies the tables from which to retrieve or manipulate data. 2. WHERE Filters rows based on specified conditions. Only rows that meet the conditions proceed to the next stage. 3. GROUP BY Groups rows into sets based on column(s) specified. Any aggregate functions (like SUM, COUNT, etc.) will now apply to each group. 4. HAVING Applies filters to groups created by GROUP BY. Only groups meeting these conditions move forward. 5. SELECT Determines which columns and expressions to return. Executes any functions or expressions listed in the SELECT clause. Deduplication of rows (DISTINCT) happens here if specified. 6. ORDER BY Sorts the result based on specified column(s) and sort direction (ASC or DESC). Does not impact the final rows selected, only the display order. 7. LIMIT Restricts the number of rows returned by the query. Useful for pagination or getting a specific subset of rows. Lets understand with an this simple SQL query SELECT department, COUNT(employee_id) AS total_employees FROM employees WHERE status = 'active' GROUP BY department HAVING total_employees > 5 ORDER BY total_employees DESC LIMIT 10; This query would execute in the following order: 1. FROM employees 2. WHERE status = 'active' 3. GROUP BY department 4. HAVING total_employees > 5 5. SELECT department, COUNT(employee_id) AS total_employees 6. ORDER BY total_employees DESC 7. LIMIT 10 SQL Order of Execution (Logical Explanation) | Namaste SQL | 38 Repost if you find it useful #sql #dataengineering #dataanalyst #dataanalytics #dataengineer #bigdata #analytics #businessanalyst #interviewquestions

Day 13/30 of SQL Challenge Today I learned about pattern matching in SQL using: LIKE While working with text data, I realized that exact matching is often not enough. We sometimes need to search for patterns, partial matches or specific formats. This is where the LIKE operator becomes useful. Concept: LIKE is used in the WHERE clause to search for a specified pattern in a column. Basic syntax: SELECT column_name FROM table_name WHERE column_name LIKE pattern; Common patterns: * '%' -> represents zero, one, or multiple characters * '_' -> represents exactly one character Examples: 1. Find names starting with 'A' SELECT name FROM customers WHERE name LIKE 'A%'; 2. Find names ending with 'n' SELECT name FROM customers WHERE name LIKE '%n'; 3. Find names containing 'ar' SELECT name FROM customers WHERE name LIKE '%ar%'; 4. Find names with exactly 5 characters SELECT name FROM customers WHERE name LIKE '_____'; Explanation: * '%' gives flexibility for partial matching * '_' helps match fixed-length patterns Key understanding: LIKE allows us to work with real-world messy text data where exact matches are not always possible. Practical use cases: * Searching users by partial name * Filtering emails or domains * Finding patterns in product names or codes Important note: LIKE is case-sensitive in some databases and case-insensitive in others, depending on the system being used. Reflection: This concept made me realize that querying text data requires flexibility, not just exact conditions. #SQL #LearningInPublic #Data #BackendDevelopment #SQLPractice #BuildInPublic

*✅ Complete Roadmap to Learn SQL (Structured Query Language) 🧠💻* *Week 1: SQL Basics* - What is SQL and how databases work - Install MySQL Workbench or PostgreSQL - Learn SELECT, FROM, WHERE - Filtering data with conditions - Practice basic queries Example: Fetch all employees, filter salary > 50k *Week 2: Sorting and Aggregation* - ORDER BY (sorting data) - Aggregate functions: COUNT, SUM, AVG, MIN, MAX - GROUP BY concept - HAVING clause Example: Department-wise average salary *Week 3: Joins (Most Important 🔥)* - INNER JOIN - LEFT JOIN, RIGHT JOIN - FULL JOIN - Self Join Example: Combine employees and departments tables *Week 4: Advanced Filtering* - IN, BETWEEN, LIKE - Wildcards (% , _) - NULL handling (IS NULL, IS NOT NULL) - CASE statements Example: Categorize customers based on spending *Week 5: Subqueries* - Nested queries - Correlated subqueries - Using subqueries in SELECT, WHERE Example: Find employees earning above average salary *Week 6: Window Functions (High Value 💰)* - OVER() clause - ROW_NUMBER(), RANK(), DENSE_RANK() - PARTITION BY Example: Rank employees by salary within each department *Week 7: CTE & Views* - Common Table Expressions (WITH) - Temporary vs permanent views - Simplify complex queries Example: Multi-step data transformation *Week 8: Data Modification* - INSERT, UPDATE, DELETE - TRUNCATE vs DELETE - Constraints (PRIMARY KEY, FOREIGN KEY) Example: Update employee salary *Week 9: Indexing & Performance* - What are indexes - Query optimization basics - EXPLAIN keyword Example: Speed up large table queries *Week 10: Working with Real Data* - Import CSV data - Data cleaning in SQL - Handling duplicates - Basic transformations Example: Clean messy sales dataset *Week 11: Mini Projects* - Write complex queries - Solve real-world case studies - Focus on business logic Examples: Sales dashboard queries, Customer segmentation *Week 12: Final Preparation* - Revise all concepts - Practice interview questions - Solve SQL challenges on LeetCode / HackerRank - Mock interviews *Daily Rule for You* - Practice SQL 60 minutes daily - Solve 5 queries daily - Revise previous queries weekly *🔥 Pro Tip* - Focus more on JOINS + WINDOW FUNCTIONS - Practice real datasets, not just theory - Think in terms of “business questions” #learningjourny #dataanalysis

📌 Advanced SQL Cheat Sheet (For Real Projects & Interviews) If basic SQL is done, this is what actually matters 👇 🔹 Execution Order (Most Important) FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT 👉 That’s why you can’t use window functions in WHERE. 🔹 COUNT Differences COUNT(*) → counts all rows COUNT(column) → ignores NULLs 🔹 Handling Duplicates GROUP BY + HAVING → identify ROW_NUMBER() → remove duplicates safely 🔹 Window Functions (Game Changer) Used when GROUP BY is not enough Examples: • Top N per group • Running totals • Ranking 🔹 ROW_NUMBER vs RANK vs DENSE_RANK ROW_NUMBER → unique rank RANK → skips numbers DENSE_RANK → no gaps 🔹 JOIN Mistakes (Very Common) 👉 Wrong joins = wrong data Always check: • Row count before & after join • Duplicate keys • Join condition 🔹 Subquery vs CTE CTE → readable & reusable Subquery → quick but messy in complex logic 🔹 EXISTS vs IN EXISTS → faster for large data IN → okay for small datasets 🔹 CASE vs WHERE CASE → for transformation WHERE → for filtering 🔹 NULL Logic (Tricky) = NULL ❌ (won’t work) IS NULL ✔ 🔹 Performance Tips • Avoid SELECT * • Use indexes wisely • Filter early (WHERE) • Use proper joins 🔹 Real Analyst Thinking Before writing SQL, ask: • What is the business question? • What defines this metric? • Is my data clean? 💡 SQL is not about writing long queries. It’s about writing correct and efficient logic. 🎯 Save this if you're preparing for real-world SQL, not just basics. #SQL #AdvancedSQL #DataAnalytics #DataAnalyst #SQLTips #DataEngineering #BusinessIntelligence #Analytics #LearnSQL #TechCareers

🔹 SQL SELECT Statement – The Most Important Command in SQL If SQL had a starting point, this would be it. The SELECT statement is used to retrieve data from a database. Every analysis, report, or dashboard begins with SELECT. 📌 Basic Syntax: SELECT column1, column2 FROM table_name; 📌 Example: SELECT * FROM employees; ✔️ * means “fetch all columns” ✔️ Returns complete data from the table 📌 Selecting Specific Columns (Best Practice): SELECT name, salary FROM employees; 👉 Instead of fetching everything, select only what you need 👉 Improves performance and readability 📌 Using SELECT with WHERE: SELECT name, salary FROM employees WHERE salary > 50000; 👉 Filters data based on conditions 📌 Using SELECT with ORDER BY: SELECT name, salary FROM employees ORDER BY salary DESC; 👉 Sorts results (highest to lowest salary) 📌 Using SELECT with DISTINCT: SELECT DISTINCT department FROM employees; 👉 Removes duplicate values 📌 Why SELECT is Important: ✔️ Core of data retrieval ✔️ Used in almost every SQL query ✔️ Essential for Data Analysts, Data Engineers, Backend Developers 💡 Pro Tip: Avoid using SELECT * in real-world projects. Instead, fetch only required columns for better performance. SQL starts with SELECT… Master it, and everything else becomes easier. #SQL #DataAnalysis #Database #TechSkills #DataEngineer #MySQL

*✅ Complete Roadmap to Learn SQL (Structured Query Language) 🧠💻* *Week 1: SQL Basics* - What is SQL and how databases work - Install MySQL Workbench or PostgreSQL - Learn SELECT, FROM, WHERE - Filtering data with conditions - Practice basic queries Example: Fetch all employees, filter salary > 50k *Week 2: Sorting and Aggregation* - ORDER BY (sorting data) - Aggregate functions: COUNT, SUM, AVG, MIN, MAX - GROUP BY concept - HAVING clause Example: Department-wise average salary *Week 3: Joins (Most Important 🔥)* - INNER JOIN - LEFT JOIN, RIGHT JOIN - FULL JOIN - Self Join Example: Combine employees and departments tables *Week 4: Advanced Filtering* - IN, BETWEEN, LIKE - Wildcards (% , _) - NULL handling (IS NULL, IS NOT NULL) - CASE statements Example: Categorize customers based on spending *Week 5: Subqueries* - Nested queries - Correlated subqueries - Using subqueries in SELECT, WHERE Example: Find employees earning above average salary *Week 6: Window Functions (High Value 💰)* - OVER() clause - ROW_NUMBER(), RANK(), DENSE_RANK() - PARTITION BY Example: Rank employees by salary within each department *Week 7: CTE & Views* - Common Table Expressions (WITH) - Temporary vs permanent views - Simplify complex queries Example: Multi-step data transformation *Week 8: Data Modification* - INSERT, UPDATE, DELETE - TRUNCATE vs DELETE - Constraints (PRIMARY KEY, FOREIGN KEY) Example: Update employee salary *Week 9: Indexing & Performance* - What are indexes - Query optimization basics - EXPLAIN keyword Example: Speed up large table queries *Week 10: Working with Real Data* - Import CSV data - Data cleaning in SQL - Handling duplicates - Basic transformations Example: Clean messy sales dataset *Week 11: Mini Projects* - Write complex queries - Solve real-world case studies - Focus on business logic Examples: Sales dashboard queries, Customer segmentation *Week 12: Final Preparation* - Revise all concepts - Practice interview questions - Solve SQL challenges on LeetCode / HackerRank - Mock interviews *Daily Rule for You* - Practice SQL 60 minutes daily - Solve 5 queries daily - Revise previous queries weekly *🔥 Pro Tip* - Focus more on JOINS + WINDOW FUNCTIONS - Practice real datasets, not just theory - Think in terms of “business questions” ✅💻💯

In today's SQL lesson, we answer: "Which customers spent above average this month?" Simple question on the surface. But answering it requires three separate things: - a monthly total per customer - the average across all customers - and a comparison between the two. Usually this is a subquery inside a subquery inside a subquery that works but that nobody, including the author, can read three days later. CTEs are the fix. Breaking it down: • WITH … AS gives a subquery a name. That name becomes a temporary table you can reference anywhere below in the same query. Nothing is stored permanently. It's computed on the fly, exists only for the duration of the query and disappears when it's done. • Chaining CTEs is what makes this powerful. You can chain as many as you need, each one adding a layer of logic on top of the last. • WHERE total > avg_total is the final filter. By the time SQL reaches this line, the heavy lifting is already done in the CTEs. The comparison is clean and readable. Where CTEs earn their place: • Multi-step logic → name each stage, build on it sequentially • Reuse within a query → reference the same CTE multiple times, compute once • Debugging → run each block in isolation to inspect results • Replacing views → avoid a permanent view for one-time complex logic • Code reviews → colleagues can read, extend and maintain it When should you still use a subquery? CTEs aren't always the answer. For a simple, single-use inline lookup, filtering by a subquery result makes it shorter and perfectly readable 👇. | WHERE amount > (SELECT AVG(amount) FROM sales) | Why does this matter beyond SQL? CTEs are a design decision. A query written in CTEs is a query that can be handed to another analyst and understood in five minutes. A deeply nested query is a puzzle that needs the original author in the room. As data teams grow, readability becomes a requirement.  A CTE is how SQL that you’ve written survives beyond you. 🔖 Save this if you work with data. ✅ Follow me for more practical SQL, data engineering tips and automation breakdowns for teams that run on data.

The Only SQL Cheat Sheet You'll Ever Need 🗄️ SQL is the backbone of data analytics — and mastering it means knowing more than just SELECT * FROM table. Here's a complete breakdown of every SQL concept category, from basics to advanced. Bookmark this. 🧵 ⚙️ The Basics Core clauses: SELECT FROM WHERE GROUP BY HAVING ORDER BY LIMIT Operators: = != < >= BETWEEN IN NOT ∑ Aggregate Functions min() max() avg() count() median() mode() stddev() Use with: GROUP BY HAVING DISTINCT 🔤 String Manipulation concat() replace() reverse() trim() upper() lower() len() str() Pattern matching: LIKE ILIKE wildcards % 📅 Date Manipulation day() month() year() getdate() date_add() datediff() date_trunc() date_format() — format output precisely 🔗 Joins INNER LEFT OUTER SELF joins ANTI JOIN — find non-matching rows Join on multiple keys or a condition 🧹 Cleaning & Transformation cast() coalesce() ifnull() iif() CASE WHEN — conditional logic in queries UNION UNION ALL INTERSECT MINUS 🪟 Window Functions Aggregates: sum() count() avg() max() min() Ranking: row_number() rank() denserank() Offset: lead() lag() with OVER(PARTITION BY... ORDER BY...) 🧠 Advanced SQL CTEs — Common Table Expressions for readable, modular queries Subqueries — correlated vs. uncorrelated; nested logic inside queries UDFs — User Defined Functions to reuse custom logic Data Modeling — structuring tables for performance and scalability 💡 The real SQL progression: Basics → Aggregates → Joins → Window Functions → CTEs & Advanced. Most analysts stop at Joins. Go further — Window Functions alone will set you apart in 90% of interviews. Which SQL category do you use most in your day-to-day work? Drop it in the comments 👇 — and save this post so you always have the reference handy! #SQL #DataAnalytics #DataScience #DataEngineering #WindowFunctions #DatabaseManagement #TechCareer #LearnSQL #BigData #Analytics

SQL Case Study #1 — Danny's Diner: What I Actually Learned I kept seeing Danny's Diner pop up on LinkedIn — analysts posting solutions, beginners calling it their first real SQL win. Eventually I sat down and worked through it myself. It shifted something in how I think about data problems, not just how I write queries. Here's what I explored 👇 🔹 Q1 — Total spend per customer → JOIN + SUM() 🔹 Q2 — Real visits, not duplicate rows → COUNT(DISTINCT order_date) 🔹 Q3 — First purchase per customer → ROW_NUMBER() 🔹 Q4 & 5 — Most popular items overall vs. per customer → DENSE_RANK() 🔹 Q6–8 — Behavior before vs. after membership → WHERE order_date < join_date 🔹 Q9–10 — Loyalty points logic → CASE WHEN The real takeaway? The queries aren't hard. Figuring out which query answers the actual business question — that's the skill. And that's exactly what this case study trains. If you're stuck between knowing SQL and thinking like an analyst, do this one. Thank you Danny Ma — three tables taught me more than months of tutorials. I've linked the full challenge below — the dataset, all 10 questions, and my complete solutions. Work through it yourself first, then compare notes.

In my work, I have written and reviewed tons of SQL queries across different dialects. Over time, I started to notice recurring patterns in analytical SELECT queries (OLTP queries have a different structure and are not always SELECTs). So I’d like to talk about the structure of analytical SQL queries and share some personal opinions on each part. Let’s start with DISTINCT )) Why DISTINCT? Because it’s the second keyword after SELECT which just starts the query. Every time I see DISTINCT in an analytical query, it signals that something might be wrong. Most of the time, DISTINCT is used to deduplicate results after messy joins. It often appears when duplication happens, but the analyst hasn’t identified the root cause and instead applies DISTINCT as a quick fix. The real issue is usually: incorrect join logic or poor table design So while it’s not always the analyst’s fault, DISTINCT is a strong indicator that a problem exists. When is DISTINCT actually OK? There are valid use cases. For example, generating a full grid (filling missing combinations): SELECT ... FROM (SELECT DISTINCT date FROM facts) x CROSS JOIN (SELECT DISTINCT user_id FROM facts) y LEFT JOIN facts f ON f.date = x.date AND f.user_id = y.user_id That said, I personally struggle to think of many other justified cases )) Fun fact (at least for me) In most cases, DISTINCT and GROUP BY behave identically. So you can often rewrite: SELECT DISTINCT {xs} FROM t as: SELECT {xs} FROM t GROUP BY {xs} But - there is an important difference The difference appears when window functions are involved: DISTINCT is applied after window functions GROUP BY is applied before So this query: SELECT DISTINCT art, FIRST_VALUE(price) OVER (PARTITION BY art ORDER BY dt DESC) AS price FROM t cannot be directly rewritten with GROUP BY without subqueries in standard SQL. Some dialects (like ClickHouse or Databricks) provide workarounds, but generally: using DISTINCT with window functions is less efficient than using subqueries, so it’s not a mainstream approach I have a lot more thoughts on SQL structure and patterns. But maybe there’s something specific you’d like me to cover next?