SQL Multi-Column Grouping Tutorial

SQL Tutorial: Complex transformations & the WITH clause 👇 Last post covered basic arithmetic transformations. This one tackles a limitation you'll hit almost immediately when you start building real calculations. 🔹 The alias-in-same-SELECT problem SQL cannot reference a column alias in the same SELECT where it's defined. This fails: SELECT *,   revenue - ad_spend - (paid_orders * avg_cost) AS ad_contribution,   100 * ad_contribution / revenue AS contribution_rate -- ERROR FROM campaign_performance; The database hasn't finished creating ad_contribution yet when it tries to use it on the next line. 🔹 The fix: WITH clause (CTEs) A Common Table Expression (CTE) breaks the calculation into named steps. The final SELECT queries from the temporary result, where the intermediate column already exists: WITH extended AS (   SELECT *,     revenue - ad_spend - (paid_orders * avg_cost) AS ad_contribution   FROM campaign_performance ) SELECT *,   100 * ad_contribution / NULLIF(revenue, 0) AS contribution_rate FROM extended; Clean, readable, no repeated formulas. 🔹 Percent of total with scalar subqueries To express each row as a % of the total, you need the SUM of the entire column — but aggregate functions collapse rows. The solution is a scalar subquery: a nested query that returns one value every row can use: SELECT *,   ROUND(     100 * revenue / NULLIF((SELECT SUM(revenue) FROM category_sales), 0),   2) AS revenue_pot FROM category_sales; 🔹 One thing that trips people up Percentages can exceed 100% when negative values exist in the data. If some rows have negative margins, they shrink the total — making profitable rows represent a larger share than expected. Always check for negatives before interpreting % metrics. Next up: filtering data with WHERE and HAVING. #SQL #PostgreSQL #DataAnalysis #LearningInPublic #TechTips

You write SQL from top to bottom. Your database reads it in a completely different order. 🧠🔍 Ever wondered why you can't use an alias you created in a SELECT statement inside your WHERE clause? Or why HAVING feels like a second WHERE? It’s because of the Logical Order of Execution. If you want to debug like a pro and stop guessing why your queries are failing, you need to memorize this "Cheat Code." 📜 The SQL Execution Cheat Sheet: Think of your query like a filter. It doesn't start at the top; it starts with the Source. FROM / JOIN: "Where is the data coming from?" (The database grabs the tables first). WHERE: "Which rows do I need?" (It filters the raw data). GROUP BY: "How should I bucket them?" (It organizes rows into groups). HAVING: "Which groups do I keep?" (It filters the groups, NOT the rows). SELECT: "What columns do I show?" (Finally! This is where aliases are born). DISTINCT: "Any duplicates?" (It cleans the final view). ORDER BY: "How should it look?" (The very last thing—sorting). TOP / LIMIT: "How many should I send back?" #SQL #DataEngineering #CodingTips #MsSQL #Database #CareerAdvice #TechHacks #SanthoshS

Day 18/30 of SQL Challenge Today I learned: FULL JOIN After exploring INNER, LEFT, and RIGHT JOIN, today was about combining everything together. Concept: FULL JOIN returns all records from both tables. If there is a match, data is combined. If there is no match, NULL values appear for the missing side. Basic syntax: SELECT columns FROM table1 FULL JOIN table2 ON table1.column = table2.column; Example: SELECT customers.name, orders.id FROM customers FULL JOIN orders ON customers.id = orders.customer_id; Explanation: * All customers are included * All orders are included * Matching records are combined * Non-matching records show NULL values Key understanding: FULL JOIN gives a complete view of both tables, including matched and unmatched data. Practical use cases: * Finding all matched and unmatched records * Data comparison between two tables * Identifying missing relationships on both sides Important note: Not all databases support FULL JOIN directly (like MySQL). In such cases, it can be simulated using UNION of LEFT JOIN and RIGHT JOIN. Example (conceptual idea): SELECT ... FROM customers LEFT JOIN orders ON ... UNION SELECT ... FROM customers RIGHT JOIN orders ON ... Reflection: Today helped me understand how to analyze complete datasets, including gaps and mismatches not just perfect matches. #SQL #LearningInPublic #Data #BackendDevelopment #SQLPractice #BuildInPublic

🧠 SQL Execution Plan — The Secret Behind Fast Queries Writing a SQL query is easy. Writing a fast SQL query is what makes the real difference in interviews and production systems 👇 Whenever a query is slow, the first thing every developer should check is the Execution Plan. 🔷 What is an Execution Plan? An Execution Plan shows how SQL Server decides to execute your query. 👉 It tells you: • Which table SQL Server accesses first • What type of joins are being used • Whether it is performing a Scan or a Seek • Which operation is taking the highest cost • Where the query is spending most of its time 💡 In simple words: it is the roadmap SQL Server follows to fetch your data. 🔷 Why is it Important? Two queries may return the same result, but one may take: ✅ 1 second ❌ 30 seconds The Execution Plan helps you understand why. It helps in: • Query optimization • Finding performance bottlenecks • Reducing logical reads • Improving production performance Without checking the execution plan, optimization becomes guesswork. 🔷 Types of Execution Plans ✅ Estimated Execution Plan → Shows what SQL Server plans to do before execution Shortcut: Ctrl + L ✅ Actual Execution Plan → Shows what SQL Server actually did after execution Shortcut: Ctrl + M 💡 Actual Execution Plan is more useful for performance tuning. 🔷 Common Operators You Should Know 🔸 Table Scan → Reads the entire table ❌ Slow for large tables 🔸 Index Scan → Scans many rows from an index ⚠️ Better than Table Scan 🔸 Index Seek → Directly jumps to required rows ✅ Fast and efficient 🔸 Key Lookup → Fetches extra columns from the main table ⚠️ Too many can slow performance 🔸 Nested Loop / Hash Match / Merge Join → Join strategies chosen by SQL Server 🔷 Interview Question Q: How do you identify why a query is slow? 👉 I first check the Actual Execution Plan, look for scans, key lookups, and expensive joins, then optimize the query accordingly. This shows practical knowledge, not just theory. 💡 Final Thought Anyone can write SQL queries. But understanding the Execution Plan is what makes you a better developer🚀 Stay tuned for my next post on how to use indexes according to the Execution Plan in SQL Server😊 #sqlserver #sql #executionplan #database #performanceoptimization #backenddeveloper #interviewprep #sqldeveloper #queryoptimization #dotnetdeveloper

🚀 Day 32/100 — SQL Subqueries: Thinking Inside Queries 🧠💻 Today I learned Subqueries, a powerful concept in SQL used to solve complex problems step by step. 📊 What is a Subquery? 👉 A query inside another query ➡️ Used to break down complex problems into simpler parts 📌 What I explored today: 🔹 Subqueries in SELECT 🔹 Subqueries in WHERE 🔹 Subqueries in FROM 🔹 Nested queries for filtering 💻 Example Scenario: 👉 Find customers who made orders above the average order value 📌 Example Query: SELECT customer_id, order_amount FROM orders WHERE order_amount > ( SELECT AVG(order_amount) FROM orders ); 📊 How it works: 👉 Inner query → calculates average 👉 Outer query → filters higher-than-average orders 🔥 Key Learnings: 💡 Subqueries help solve complex business questions 💡 Makes SQL more flexible and powerful 💡 Commonly asked in interviews 🚀 Real-world use cases: ✔ Filtering based on averages ✔ Comparing values within datasets ✔ Dynamic data selection 🔥 Pro Tip: 👉 Use subqueries when: You need step-by-step filtering OR when JOINs become complex 📊 Tools Used: SQL | MySQL ✅ Day 32 complete. 👉 Quick question: Do you prefer solving problems using JOINs or Subqueries? 🤔 #Day32 #100DaysOfData #SQL #Subqueries #DataAnalytics #LearningInPublic #CareerGrowth #JobReady #InterviewPrep

Day 86 – SQL JOIN (INNER, LEFT, RIGHT) Today I learned how to combine data from multiple tables using JOIN in SQL. JOIN is one of the most powerful concepts in databases because it helps us fetch related data from different tables. 🔹 What is JOIN? JOIN is used to combine rows from two or more tables based on a related column. 🔹 1️⃣ INNER JOIN INNER JOIN returns only the rows that have matching values in both tables. Example: SELECT E10.name, E10.id, E11.age FROM E10 INNER JOIN E11 ON E10.id = E11.id; ✔️ Returns only common matching records ✔️ Non-matching data will be ignored 🔹 2️⃣ LEFT JOIN LEFT JOIN returns: ✔️ All records from the left table ✔️ Matching records from the right table If no match → shows NULL Example: SELECT E12.name, E12.id, E13.age FROM E12 LEFT JOIN E13 ON E12.id = E13.id ORDER BY E12.id; ✔️ All data from left table (E12) ✔️ Non-matching rows show NULL values 🔹 3️⃣ RIGHT JOIN RIGHT JOIN is the opposite of LEFT JOIN. ✔️ All records from the right table ✔️ Matching records from the left table ✔️ Non-matching left values → NULL Example: SELECT E14.name, E14.id, E15.age FROM E14 RIGHT JOIN E15 ON E14.id = E15.id ORDER BY E15.id; 🔹 Quick Difference JOIN TypeResultINNER JOINOnly matching dataLEFT JOINAll left + matching rightRIGHT JOINAll right + matching left 🎯 Key Takeaways Today I learned: ✔️ How to combine tables using JOIN ✔️ Difference between INNER, LEFT, RIGHT JOIN ✔️ How NULL appears when no match is found ✔️ Importance of common column (id) in joins These concepts are very important when working with real-world relational databases. #SQL #MySQL #Database #BackendDevelopment #DataAnalysis #WebDevelopment

🚀 SQL Journey – Day 22: Subqueries in SQL Today I explored one of the most powerful concepts in SQL — Subqueries 🔍 💡 What I learned: • A subquery is a query inside another query • Helps to filter, compare, and calculate data efficiently 📊 Types of Subqueries: • Single Row, Multiple Row, Multiple Column • Based on Dependency → Independent & Correlated • Based on Location → SELECT, WHERE, FROM • Based on Keywords → IN, ANY, ALL, EXISTS 🔥 Highlight of the day — Correlated Subquery: • Depends on the outer query • Executes row by row • Useful for comparing values within groups 💻 Example: Find employees earning more than their department average SELECT e1.name, e1.salary FROM employees e1 WHERE e1.salary > ( SELECT AVG(e2.salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id ); 🎯 Key Takeaway: Subqueries make SQL smarter with nested logic, and correlated subqueries take it further with row-level comparisons. #SQL #DataAnalytics #LearningJourney #SQLPractice #ITProjects #DataEngineering

𝗙𝗜𝗟𝗧𝗘𝗥 𝗰𝗹𝗮𝘂𝘀𝗲 — 𝗰𝗹𝗲𝗮𝗻𝗲𝗿 𝗰𝗼𝗻𝗱𝗶𝘁𝗶𝗼𝗻𝗮𝗹 𝗮𝗴𝗴𝗿𝗲𝗴𝗮𝘁𝗶𝗼𝗻 CASE WHEN inside aggregations is everywhere. But there's a cleaner way in modern SQL. The 𝗙𝗜𝗟𝗧𝗘𝗥 clause. Old way: SELECT SUM(CASE WHEN status = 'paid' THEN amount END) AS paid, SUM(CASE WHEN status = 'pending' THEN amount END) AS pending FROM orders; New way with FILTER: SELECT SUM(amount) FILTER (WHERE status = 'paid') AS paid, SUM(amount) FILTER (WHERE status = 'pending') AS pending FROM orders; Same result. Much cleaner. Works with COUNT, AVG, MIN, MAX — any aggregate function. The best SQL isn't always the most complex — sometimes it's just more readable. Have you used FILTER before? #SQL #DataAnalysis #PostgreSQL #DataEngineering #Analytics

I recently spent some time strengthening my SQL fundamentals beyond just basic queries and joins. To prepare better for data analyst roles, I worked through a structured set of practice questions covering topics like subqueries, CTEs, window functions, CASE statements, conditional aggregation, date functions, and database objects such as procedures, triggers, and views. Instead of passively reading, I focused on solving interview-style questions and understanding when and why to use each concept. Some of the areas I practiced: • Correlated subqueries and EXISTS • Window functions like ROW_NUMBER, DENSE_RANK • CTEs for structured query building • Conditional aggregation using CASE I’ve documented this practice along with the dataset, questions, and solutions here: 🔗 https://lnkd.in/gN29KuwR This exercise really helped me improve my query logic and confidence while approaching SQL problems. I’m continuing to build more projects and deepen my understanding of data analytics. #SQL #MySQL #DataAnalytics #DataAnalyst

📊 Strengthening My SQL Fundamentals – Date & Time Formatting in MySQL. Today, I explored how to work with date and time functions in MySQL, focusing on the powerful DATE_FORMAT() function to extract structured insights from datetime data. 🔍 Key Takeaways: • Extracted day, weekday, month, and year from a single datetime column • Worked with useful format specifiers like %d, %a, %m, %b, %M, %Y • Improved understanding of how formatted data enhances reporting and analysis. 💡 Why this matters: Formatting date-time data plays a crucial role in: • Building intuitive dashboards • Performing time-based analysis • Writing cleaner, more readable SQL queries Even small improvements like these contribute to writing more efficient and production-ready queries. 🚀 Consistency is key — growing one concept at a time. Baraa Khatib Salkini #SQL #MySQL #DataAnalytics #LearningInPublic #TechSkills #Database #100DaysOfCode