CTEs in SQL: Cleaner Queries with Common Table Expressions

🚀 Day 12 of #100DaysOfSQL 👉 Topic: Common Table Expressions (CTE) in SQL Today I learned how to simplify complex queries using CTEs — making SQL more readable and structured. --- 🔹 What is a CTE? A Common Table Expression (CTE) is a temporary result set that you can reference within a query using the "WITH" clause. --- 📌 Syntax: WITH cte_name AS ( SELECT column1, column2 FROM table_name WHERE condition ) SELECT * FROM cte_name; --- 📊 Example 1: Filter High Salary Employees WITH high_salary AS ( SELECT emp_name, salary FROM employees WHERE salary > 50000 ) SELECT * FROM high_salary; --- 📊 Example 2: Use CTE with Aggregation WITH dept_count AS ( SELECT dept_id, COUNT(*) AS total FROM employees GROUP BY dept_id ) SELECT * FROM dept_count; --- 📊 Example 3: CTE with Ranking WITH ranked_employees AS ( SELECT emp_name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank FROM employees ) SELECT * FROM ranked_employees WHERE rank <= 3; --- 💡 Why use CTE? ✔ Improves readability ✔ Breaks complex queries into steps ✔ Reusable within a query ✔ Easier debugging --- 🎯 Key Takeaway: CTEs help you write cleaner, more organized, and maintainable SQL queries. --- #SQL #CTE #DataAnalytics #100DaysOfSQL

🚀 SQL Revision Today I didn’t learn something new… I revised deeply what I already practiced — and that made a huge difference 💯 📌 Complete SQL Practice + Revision (15 Questions) 🔹 LEVEL 1 — GROUP BY Basics Total revenue per category Total orders per category Maximum order value per category 🔹 LEVEL 2 — DISTINCT 4. Unique categories 5. Unique customers 6. Customer + Category unique combinations 🔹 LEVEL 3 — Multi-column GROUP BY 7. Revenue by Customer & Category 8. Order count by Customer & Category 9. Revenue by Category & Month 🔹 LEVEL 4 — GROUP BY + HAVING 10. Categories with revenue > 20000 11. Customers with more than 1 order 12. Customer-Category spending > 10000 🔹 LEVEL 5 — Advanced (Interview Level 🔥) 13. Category summary (Total Orders, Revenue, Avg > 5000, Sorted) 14. High-value repeat customers (Orders > 1 & Avg > 6000) 15. Category-Month summary (Min 2 orders + sorting) 💡 What Changed After Revision? Earlier: ❌ Overusing GROUP BY ❌ Confusion between DISTINCT vs GROUP BY Now: ✅ Using DISTINCT for uniqueness ✅ Using GROUP BY only when aggregation needed ✅ Using HAVING correctly for group filtering 🔥 Key Realization: Revision is where clarity actually comes. Practice shows you problems, Revision fixes your thinking. 💪 Step by step improving towards interview level SQL 🔁 Consistency + Revision = Confidence #SQL #Revision #LearningJourney #DataAnalytics #PlacementPreparation #Coding #BCA #Consistency #100DaysOfCode

📊 Advanced SQL – Day 31 ( 04/04/2026 ) Common Table Expressions (CTE) Today’s session focused on CTEs (Common Table Expressions) — a powerful way to write clean, readable, and structured SQL queries. 💻 🔹 What is a CTE? A temporary result set defined using WITH clause Used within a query to simplify complex logic Improves readability and reusability 🔹 Basic Syntax : WITH cte_name AS ( SELECT column_name FROM table_name ) SELECT * FROM cte_name; 🔹 Key Concepts Learned 🧠 Break complex queries into smaller logical steps Replace nested subqueries with clean structure Can be referenced multiple times in the main query 🔹 Rules of CTE ⚠️ Defined using WITH keyword Exists only for one query execution Must be followed by a main SELECT/INSERT/UPDATE/DELETE Can contain joins, aggregations, filters 🔹 Use Cases 🌍 Simplifying complex subqueries Improving query readability Performing step-by-step data transformations Useful in reporting and analytics queries ✨ Key Takeaway: CTEs make SQL queries more organized, readable, and maintainable, helping solve complex problems in a structured way. 📈 #SQL #CTE #DataAnalytics #LearningJourney #SQLPractice

My SQL query was wrong for 6 months 😐 I was writing SQL in the right order. But SQL does not execute in that order. Here is what nobody tells you ↓ → How We Write SQL • SELECT • FROM • WHERE • GROUP BY • HAVING • ORDER BY • LIMIT → How SQL Actually Executes • 1 - FROM - get the table first • 2 - WHERE - filter rows • 3 - GROUP BY - group filtered rows • 4 - HAVING - filter groups • 5 - SELECT - pick columns • 6 - ORDER BY - sort result • 7 - LIMIT - restrict rows Write order is not execute order. This trips everyone. Learning Hack : " Find Which Girl Have Selected Old Ladies " → Why This Matters • WHERE runs BEFORE SELECT ↳ You cannot use SELECT alias in WHERE clause ↳ WHERE new_salary > 50000 - ERROR • HAVING runs AFTER GROUP BY ↳ Use HAVING for aggregate conditions ↳ Not WHERE COUNT(*) > 5 - use HAVING COUNT(*) > 5 • ORDER BY runs AFTER SELECT ↳ You CAN use alias in ORDER BY ↳ ORDER BY new_salary DESC - WORKS → Common Mistakes • Alias in WHERE ✓ Wrong - WHERE alias > 5 ✓ Correct - WHERE col > 5 • Aggregate in WHERE ✓ Wrong - WHERE COUNT(*) > 5 ✓ Correct - HAVING COUNT(*) > 5 • GROUP BY alias ✓ Wrong - GROUP BY alias ✓ Correct - GROUP BY col_name → Key Rules • FROM first - WHERE second - never reversed • Alias usable - ORDER BY yes / WHERE no / HAVING no • Aggregate functions - only in SELECT and HAVING • LIMIT always last in execution → One Line to Remember FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT Write order is not execute order. Now you will never make these mistakes again. 💪 Get my NOTES here: https://lnkd.in/ghCp94Z2 ↳ Save this before your next DE interview.

𝗚𝗼𝗼𝗱 𝗦𝗤𝗟 𝘄𝗼𝗿𝗸𝘀. 𝗖𝗹𝗲𝗮𝗻 𝗦𝗤𝗟 𝗶𝘀 𝗲𝗮𝘀𝘆 𝘁𝗼 𝗿𝗲𝗮𝗱, 𝗱𝗲𝗯𝘂𝗴, 𝗮𝗻𝗱 𝘀𝗰𝗮𝗹𝗲. When you start learning SQL, the main focus is usually getting the correct result. But in real-world projects, writing clean and readable SQL is just as important. Because your queries will be read by: • teammates • analysts • engineers • your future self Here are 4 simple practices that instantly improve your SQL quality 👇 1️⃣ Use aliases for readability Aliases make queries shorter and easier to understand. Instead of repeating long table names, use meaningful aliases. Example: SELECT u.id, u.name, SUM(o.amount) AS total_spent FROM users AS u JOIN orders AS o ON u.id = o.user_id GROUP BY u.id, u.name; 2️⃣ Format queries properly Well-formatted SQL is much easier to debug and maintain. Best practices: • Use uppercase for SQL keywords • Place each clause on a new line • Align JOIN conditions 3️⃣ Follow naming conventions Consistent naming makes databases easier to navigate. Common convention: • snake_case for tables and columns • descriptive column names Example: customer_id order_date total_amount 4️⃣ Avoid SELECT * It might feel convenient, but it can: • slow down queries • retrieve unnecessary data • break code when schema changes Better approach: SELECT order_id, order_date, total_amount FROM orders; 💡 Key takeaway Clean SQL isn't just about style — It makes your queries faster to understand, easier to maintain, and more production-ready. Small habits like these make a big difference in real data projects. Curious to know 👇 What’s one SQL habit that improved your queries the most? #SQL #DataAnalytics #LearningInPublic #SQLTips #DataAnalyticsJourney

day-31 🚀 Day 31 of My SQL Journey – 𝗜𝗻𝘁𝗿𝗼𝗱𝘂𝗰𝘁𝗶𝗼𝗻 𝘁𝗼 𝗖𝗧𝗘𝘀 (𝗖𝗼𝗺𝗺𝗼𝗻 𝗧𝗮𝗯𝗹𝗲 𝗘𝘅𝗽𝗿𝗲𝘀𝘀𝗶𝗼𝗻𝘀) Today I explored 𝗖𝗧𝗘𝘀, a powerful SQL feature that helps write cleaner, more structured, and readable queries. 🔹 Learned how to use the `WITH` clause to create temporary result sets 🔹 Understood how CTEs simplify complex queries and replace nested subqueries 🔹 Practiced grouping and filtering data using CTEs 🔹 Explored real use cases like data transformation and hierarchical queries 💡 𝗢𝗻𝗲 𝗸𝗲𝘆 𝘁𝗮𝗸𝗲𝗮𝘄𝗮𝘆: CTEs not only improve readability but also make SQL queries more maintainable and professional. Here’s a simple example I worked on: calculating total amount per customer and filtering those above a threshold using a CTE. 📈 Step by step, getting better at writing optimized and structured SQL queries! hashtag#SQL hashtag#CTE hashtag#DataAnalytics hashtag#LearningJourney hashtag#Database hashtag#100DaysOfCode hashtag#TechSkills

🚀 SQL Journey – Day 31: Introduction to CTE (Common Table Expression) Today’s focus was on CTEs (Common Table Expressions) — one of the most powerful concepts for writing clean and structured SQL queries. 🔹 What is a CTE? A CTE is a temporary result set defined using the "WITH" clause, which can be referenced within a SELECT, INSERT, UPDATE, or DELETE query. 👉 It helps break complex queries into smaller, readable parts. 🔹 Basic Syntax WITH cte_name AS (  SELECT column_name  FROM table_name  WHERE condition ) SELECT * FROM cte_name; 🔹 Why CTE is Useful? • Improves query readability • Avoids writing subqueries multiple times • Helps structure complex logic step-by-step • Makes debugging easier 🔹 Concept Understanding (From Today’s Notes) Instead of writing nested subqueries like: SELECT customer, SUM(amount) FROM sales GROUP BY customer HAVING SUM(amount) > 8000; 👉 We can first create a temporary result: WITH total_sales AS (  SELECT customer, SUM(amount) AS total  FROM sales  GROUP BY customer ) SELECT * FROM total_sales WHERE total > 8000; ✔ Clean ✔ Readable ✔ Easy to maintain 🔹 Key Insight CTE acts like a temporary table inside a query that can be reused multiple times within the same query. 💡 Day 31 Realization CTEs are not just about syntax — they change the way you think about writing queries. Instead of solving everything at once, you break problems into logical steps. That’s how real-world SQL is written. From messy queries → to structured thinking. HAPPY LEARNING!✨ #SQL #CTE #DataAnalytics #LearningJourney #SQLPractice #RDBMS #TechJourney #CSE

Day 31 of SQL Learning – Understanding CTE (Common Table Expressions) Today I focused on Common Table Expressions (CTEs). This is where SQL starts becoming structured instead of messy. A CTE is a temporary result set defined once and used within a query. Instead of stacking multiple subqueries, you break the logic into clear steps. Structure: WITH cte_name AS ( SELECT column1, column2 FROM table_name WHERE condition ) SELECT * FROM cte_name; Why CTEs matter: Most SQL problems are not complex. They are poorly structured. CTEs fix that by: • Making queries readable • Reducing repetition • Breaking logic into steps • Making debugging easier Where it is used: • Complex SELECT queries • Joins with intermediate logic • Aggregations • Recursive problems like hierarchies Real-world example: Finding employees earning above their department’s average salary: WITH dept_avg AS ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) SELECT e.employee_name, e.salary FROM employees e JOIN dept_avg d ON e.department_id = d.department_id WHERE e.salary > d.avg_salary; Instead of repeating the same calculation, define it once and reuse it. CTEs don’t make SQL more powerful. They make your thinking clearer. #SQL #DataAnalytics #LearningJourney #CTE #SQLLearning #DataScience

🚀 SQL Journey – Day 32: Recursive CTE (Hierarchical Queries) Today’s focus was on Recursive CTEs, one of the most powerful SQL concepts used to work with hierarchical or repeating data. 🔹 What is a Recursive CTE? A Recursive CTE is a CTE that calls itself repeatedly to process hierarchical or sequential data. 👉 Used when data has parent-child relationships 🔹 Basic Structure WITH cte_name AS (  -- Anchor Query (starting point)  SELECT ...  UNION ALL  -- Recursive Query (calls itself)  SELECT ...  FROM table t  JOIN cte_name c ON condition ) SELECT * FROM cte_name; 🔹 Key Components ✔ Anchor Query → Starting rows ✔ Recursive Query → Repeats logic ✔ UNION ALL → Combines results ✔ Stops when no new rows are returned 🔹 Where is it Used? • Employee → Manager hierarchy • Category → Subcategory structure • Organizational charts • Tree-like data traversal 🔹 Concept Understanding (From Today’s Notes) Recursive CTE works step-by-step: 1️⃣ Start with base data (Anchor) 2️⃣ Use result to fetch next level 3️⃣ Repeat until condition fails 👉 Like traversing a tree or graph 🔹 Important Rules • Must use UNION ALL (not UNION) • Recursive part must reference CTE name • Be careful with infinite loops • Can control depth using conditions 🔹 Interview Insight 💡 If a problem involves: • Hierarchy • Levels • Parent-child relationships 👉 Think Recursive CTE immediately 💡 Day 32 Realization Recursive CTE is not just SQL — it’s logic + iteration inside queries. Once you understand this, you can solve complex hierarchical problems easily. SQL is getting deeper. Thinking is getting sharper. HAPPY LEARNING!✨ #SQL #CTE #RecursiveCTE #DataAnalytics #LearningJourney #SQLPractice #RDBMS #TechJourney #CSE

📘 SQL Journey – Day 28: Mastering Subqueries (Types & Practical Clarity) Today’s focus was on strengthening my understanding of different types of subqueries and when to use each in real-world scenarios. Instead of just writing queries, I focused on choosing the right type of subquery for the problem. ⸻ 🔹 Types of Subqueries (Deep Understanding) ✅ Single Row Subquery • Returns only one value • Used with operators (=, >, <, >=, <=) • Example use: Compare salary with average salary ✅ Multi Row Subquery • Returns multiple values • Example use: Filter records based on multiple matching values ✅ Correlated Subquery • Depends on outer query → Executes once for each row • Used for row-wise comparisons • More powerful but can impact performance if not optimized ⸻ 🔹 Key Learning Shift ✔ Earlier: “Write a subquery” ✔ Now: “Which subquery type fits this problem?” • That’s the real difference ⸻ 🔹 Important Rules ✔ Subquery executes inside → outside ✔ Data types must match between inner & outer query ✔ Correlated subqueries run multiple times ✔ Can be used in SELECT, WHERE, FROM ⸻ 🔹 Real Use Cases Practiced • Employees earning above department average • Filtering based on dynamic conditions • Replacing row values with aggregated results • Replacing complex joins with subqueries (and vice versa) ⸻ 💡 Day 28 Realization Choosing the right approach (JOIN vs Subquery) That’s what makes you strong. That’s where real problem-solving begins. ⸻ #SQL #Subqueries #AdvancedSQL #DataAnalytics #LearningJourney #RDBMS #HappyToCode