SQL Subquery Differences: Correlated vs Non-Correlated

If you want to become a stronger data engineer, improving your SQL is one of the highest-leverage things you can do. Not just writing joins and aggregations, but understanding grain, debugging mismatched metrics, using window functions properly, and thinking about performance and data quality. I’ve just published a new Substack post called 𝗦𝗤𝗟 𝗳𝗼𝗿 𝗗𝗮𝘁𝗮 𝗘𝗻𝗴𝗶𝗻𝗲𝗲𝗿𝘀: 𝗧𝗵𝗲 𝗖𝗼𝗺𝗽𝗹𝗲𝘁𝗲 𝗥𝗲𝗳𝗲𝗿𝗲𝗻𝗰𝗲 𝗖𝗵𝗲𝗰𝗸𝗹𝗶𝘀𝘁. The aim was to create a reference-style guide that helps people build stronger fundamentals and identify what to learn next. I’ve put the link in the comments. Let me know your thoughts... #dataengineering #SQL #substack ----------------------------------- 💻 Follow Faisal for more data engineering-related content ♻️ Book some time with me here: https://lnkd.in/emA_BFqP 📫 Subscribe to my Substack to read about my thoughts on data: https://lnkd.in/evpBJc9n

I’ve been diving deep into SQL lately, building a full set of queries from basics to advanced topics. Here’s a sneak peek of what I’ve worked on 👇 1. Database & Table creation– `CREATE DATABASE` & `CREATE TABLE` 2. Basic SELECT & Filterin– `SELECT`, `WHERE`, `ORDER BY`, `LIMIT 3. Conditional Logic & Patterns– `AND/OR`, `IN`, `LIKE`, `CASE` 4. Aggregations & Grouping– `COUNT`, `SUM`, `AVG`, `MIN/MAX`, `HAVING` 5. Joins & Relationships – `INNER JOIN`, `LEFT JOIN`, `RIGHT JOIN`, `FULL JOIN`, `SELF JOIN` 6. Subqueries & Pagination– Nested queries, `LIMIT OFFSET` 7. Window Functions– `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `PARTITION BY`, `SUM/AVG OVER()`, `LAG`, `LEAD` This project helped me understand data filtering, aggregation, relational joins, and advanced analytics using window functions.. Always open to feedback and suggestions from SQL pros! #SQL #DataAnalytics #Database #Learning #Tech #Coding #SQLPractice

🔍 SQL Joins - explained once and for all! If you've ever stared at a JOIN query and thought "wait, what's actually being returned here?" you're not alone. Here's a quick visual breakdown every data professional should bookmark 👇 ✅ INNER JOIN → Only matching rows from both tables ✅ FULL JOIN → Everything from both tables (matched + unmatched) ✅ FULL JOIN + WHERE NULL → Only the rows that DON'T match (outer exclusive) ✅ LEFT JOIN → All of A + matching rows from B ✅ LEFT JOIN + WHERE B.key IS NULL → Only rows in A with NO match in B ✅ RIGHT JOIN → All of B + matching rows from A ✅ RIGHT JOIN + WHERE B.key IS NULL → Only rows in B with NO match in A Mastering JOINs is the difference between writing queries that work... and writing queries that work efficiently. 💡 Save this post next time you're lost in a JOIN rabbit hole. 🐇 Which JOIN do you use the most in your day-to-day work? Drop it in the comments! 👇 #SQL #DataEngineering #DataScience #Analytics #Programming #TechTips #LearnSQL #DatabaseDesign #DataAnalytics #SoftwareEngineering #Coding #BigData #CareerGrowth #TechCommunity

Earlier this week, I was debugging a legacy SQL script. It was a mess of subqueries wrapped inside subqueries—like trying to unwrap an endless stack of boxes just to find one small item. It was hard to read, impossible to debug, and slowed the whole team down. The fix? I refactored the entire thing into clean CTEs (Common Table Expressions). Here is why I’ve made the switch: Readability: CTEs let you name your data blocks. You read the code from top to bottom, like a story, not from the inside out. Easy Debugging: You can test each "block" individually. No more untangling a web of parentheses. Team Speed: If a teammate can understand your query in 30 seconds instead of 30 minutes, you’ve just saved the company money. In Data Science Engineering, "clean" is often better than "clever." #SQL #DataEngineering #DataScience #CleanCode #TechTips

Something I've been always adamant when working with data I wished I saw more of it here is this: Documenting queries. Pipelines change all the time; New columns show up; Business logic evolves; New people join the team. And suddenly… a query that made sense months ago is hard to understand. I had a joke with a previous colleague that when th code was made, just me and God knew what it was. Now that a few months has passed it's just God 😂 😂 Documenting doesn't have to be a big issue. A few small things go a long way: • Write a small summary of what the query is actually doing • Write where the data is coming from • Any tricky joins or specific business rules? Comment it • How key metrics are calculated? A small comment goes a long way Nothing fancy. Even a few lines at the top help a lot. Good documentation isn’t just for others. It’s for you… 3 months later, trying to figure out what you were thinking 😅 😄 #DataEngineering #SQL #Coding #Documentation

[𝗦𝗤𝗟 𝗖𝗛𝗔𝗟𝗟𝗘𝗡𝗚𝗘 #6]: 𝗧𝗵𝗲 "𝗗𝗮𝘁𝗮 𝗕𝘂𝗰𝗸𝗲𝘁𝗲𝗲𝗿" Raw numbers are great, but for a Finance or Product team, a list of 10,000 transactions is just noise. To find patterns, we need to see the 𝗱𝗶𝘀𝘁𝗿𝗶𝗯𝘂𝘁𝗶𝗼𝗻. Are most of our users spending small amounts, or are we driven by "whales"? Today’s challenge is about building a frequency histogram, a must-have skill for any analyst performing exploratory data analysis (EDA). 𝗧𝗵𝗲 𝗦𝗲𝘁𝘂𝗽 You have a transactions table. The CFO wants a high-level summary of transaction volume across specific price ranges (buckets). Your task is to categorize every transaction and count how many fall into each range. 𝗧𝗵𝗲 𝗦𝗰𝗵𝗲𝗺𝗮: CREATE TABLE transactions (   txn_id INT,    amount NUMERIC(10,2) ); 𝗧𝗵𝗲 𝗠𝗶𝘀𝘀𝗶𝗼𝗻 Write a query that groups transaction amounts into the following four buckets: 1️⃣ 0-100 2️⃣ 101-500 3️⃣ 501-1000 4️⃣ 1000+ 𝗘𝘅𝗽𝗲𝗰𝘁𝗲𝗱 𝗢𝘂𝘁𝗽𝘂𝘁: | 𝗯𝘂𝗰𝗸𝗲𝘁 | 𝗰𝗼𝘂𝗻𝘁 | | 0-100 | 45 | | 101-500 | 123 | | 501-1000 | 67 | | 1000+ | 12 | 𝗧𝗵𝗲 𝗦𝘁𝗿𝗮𝘁𝗲𝗴𝘆 𝗦𝗲𝘀𝘀𝗶𝗼𝗻: There are a few ways to slice this. The most common is using a CASE WHEN statement, but some dialects have specialized functions like WIDTH_BUCKET or floor math tricks. How would you ensure the buckets appear in the correct order (numerical rather than alphabetical)? And how do you handle the upper boundaries to make sure no transaction is counted twice? Drop your code in the comments! Tell us which SQL engine you’re using and your favorite trick for bucketing data. Let’s see those solutions! #SQL #DataAnalysis #DataScience #DataEngineering #PostgreSQL #MySQL #BigQuery #CodingChallenge #Statistics #LearnSQL

❄️ LeetCode Daily Challenge 📅 Day 19 of 50 Days SQL Challenge Continuing my SQL journey with another Hard-level SQL problem focused on session analytics and behavioral anomaly detection. 📌 Problem: Find Zombie Sessions 🔗 Problem Link: https://lnkd.in/gDEr9VEq 💡 Problem Summary: We need to identify zombie sessions — sessions where users appear active but show abnormal behavior patterns. A session qualifies as a zombie session if it satisfies ALL conditions: ✔ Session duration > 30 minutes ✔ At least 5 scroll events ✔ Click-to-scroll ratio < 0.20 ✔ No purchases during the session Finally, return the result ordered by: scroll_count (DESC), session_id (ASC) The idea was to aggregate event-level data into session-level metrics, then filter sessions based on behavioral thresholds. Another Hard SQL challenge completed — consistency continues 💪 Let’s grow one query at a time 🚀 #LeetCode #SQL #DataEngineering #Analytics #Database #WindowFunctions #DailyPractice #LearningInPublic #50DaysChallenge

𝐖𝐫𝐢𝐭𝐢𝐧𝐠 𝐒𝐐𝐋 𝐢𝐬 𝐞𝐚𝐬𝐲. 𝐖𝐫𝐢𝐭𝐢𝐧𝐠 𝐞𝐟𝐟𝐢𝐜𝐢𝐞𝐧𝐭 𝐒𝐐𝐋 𝐢𝐬 𝐰𝐡𝐞𝐫𝐞 𝐭𝐡𝐞 𝐩𝐫𝐨𝐬 𝐬𝐭𝐚𝐧𝐝 𝐨𝐮𝐭. 🔰 RANK vs DENSE_RANK: RANK: Provides a ranking with gaps if there are ties. DENSE_RANK: Provides a ranking without gaps, even in the case of ties. 🔰 HAVING vs WHERE Clause: WHERE: Filters rows before grouping. HAVING: Filters groups after the GROUP BY clause. 🔰 UNION vs UNION ALL: UNION: Removes duplicates and combines results. UNION ALL: Combines results without removing duplicates. 🔰 JOIN vs UNION: JOIN: Combines columns from multiple tables. UNION: Combines rows from multiple tables with similar structure. 🔰 DELETE vs DROP vs TRUNCATE: DELETE: Removes rows, with the option to filter. DROP: Removes the entire table or database. TRUNCATE: Deletes all rows but keeps the table structure. 🔰 CTE vs TEMP TABLE: CTE: Temporary result set used within a single query. TEMP TABLE: Physical temporary table that persists for the session. 🔰 SUBQUERIES vs CTE: SUBQUERIES: Nested queries inside the main query. CTE: Can be more readable and used multiple times in a query. 🔰 ISNULL vs COALESCE: ISNULL: Replaces NULL with a specified value, accepts two parameters. COALESCE: Returns the first non-NULL value from a list of expressions, accepting multiple parameters. 🔰 INTERSECT vs INNER JOIN: INTERSECT: Returns common rows from two queries. INNER JOIN: Combines matching rows from two tables based on a condition. 🔰 EXCEPT vs NOT IN: EXCEPT: Returns rows in the first query but not in the second. NOT IN: Filters rows where a column's value is not in a given list. #Data #SQL #Fabric #DataEngineering #DataAnalyst #Datascience

STOP writing subqueries just to use ROW_NUMBER(). There’s a much cleaner way. QUALIFY ▶️ If you’ve ever written SQL like this: rank rows wrap it in a subquery then filter WHERE rn = 1 …you were doing extra work. QUALIFY lets you filter the result of a window function directly. Example: Want the latest order per customer? Instead of: 🔹 CTE 🔹subquery 🔹extra nesting You can do it in one clean query. With QUALIFY, SQL reads the way your brain thinks. You calculate the window function… and filter it right there. Perfect for: ✅ deduplication ✅ latest record per ID ✅ top N per group ✅ ranking logic ✅ interview questions ✅ Cleaner SQL. ✅ Less nesting. ✅ Less pain. It honestly feels illegal the first time you use it. 💾 Save this for your next SQL debugging spiral. #SQL #DataEngineering #AnalyticsEngineering #Snowflake #BigQuery #Databricks #DataAnalytics #DataEngineer #TechCareers #LearnSQL

🚀 𝗗𝗮𝘆 𝟮𝟳 – 𝗦𝗤𝗟 𝗝𝗼𝘂𝗿𝗻𝗲𝘆: 𝗦𝘂𝗯𝗾𝘂𝗲𝗿𝗶𝗲𝘀 𝗗𝗲𝗲𝗽 𝗗𝗶𝘃𝗲 Today I focused on understanding how subqueries actually work internally and how to use them effectively in real-world scenarios. This wasn’t just theory — I practiced multiple cases to improve execution flow thinking and logical problem-solving. 🔍 𝗪𝗵𝗮𝘁 𝗜 𝘄𝗼𝗿𝗸𝗲𝗱 𝗼𝗻: Subqueries in SELECT and WHERE Fetching intermediate results using nested queries Writing dynamic conditions based on subquery outputs 📌 𝗧𝘆𝗽𝗲𝘀 𝗼𝗳 𝗦𝘂𝗯𝗾𝘂𝗲𝗿𝗶𝗲𝘀 𝗜 𝗽𝗿𝗮𝗰𝘁𝗶𝗰𝗲𝗱: ✔️ Single Row Subquery → Returns one value → Used with operators like =, >, < ✔️ Multi Row Subquery → Returns multiple values → Used with IN, ANY, ALL ✔️ Correlated Subquery → Depends on outer query → Executes row-by-row (powerful but costly) 🧠 𝗞𝗲𝘆 𝗧𝗮𝗸𝗲𝗮𝘄𝗮𝘆𝘀: Subqueries execute inside → outside Outer query depends on inner query results Data type compatibility is critical Can be nested at multiple levels ⚡ 𝗥𝗲𝗮𝗹𝗶𝘇𝗮𝘁𝗶𝗼𝗻𝘀: Correlated subqueries are powerful but can hurt performance Poor usage = slow queries Sometimes JOINS are a smarter alternative 💡 𝗙𝗶𝗻𝗮𝗹 𝗧𝗵𝗼𝘂𝗴𝗵𝘁: Subqueries are not just syntax — they’re a thinking pattern. They help break complex problems into smaller logical steps. Mastering them means writing smarter SQL, not longer SQL. #𝗦𝗤𝗟 #𝗦𝘂𝗯𝗾𝘂𝗲𝗿𝗶𝗲𝘀 #𝗔𝗱𝘃𝗮𝗻𝗰𝗲𝗱 𝘀𝗾𝗹 #𝗗𝗮𝘁𝗮𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 #𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴𝗝𝗼𝘂𝗿𝗻𝗲𝘆