GROUP BY vs Window Functions in SQL

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

🚨𝐈𝐟 𝐲𝐨𝐮 𝐡𝐚𝐝 𝐭𝐨 𝐟𝐨𝐜𝐮𝐬 𝐨𝐧 𝐣𝐮𝐬𝐭 𝟑 𝐭𝐨𝐩𝐢𝐜𝐬 𝐢𝐧 𝐒𝐐𝐋 — 𝐦𝐚𝐤𝐞 𝐢𝐭 𝐭𝐡𝐞𝐬𝐞 👇 1. 𝐂𝐓𝐄𝐬 (𝐂𝐨𝐦𝐦𝐨𝐧 𝐓𝐚𝐛𝐥𝐞 𝐄𝐱𝐩𝐫𝐞𝐬𝐬𝐢𝐨𝐧𝐬) — Your thinking framework Messy nested queries? Hard to debug logic? CTEs let you break complex transformations into clear, step-by-step logic.You stop writing “confusing queries” and start building readable data flows. 2. 𝐖𝐢𝐧𝐝𝐨𝐰 𝐅𝐮𝐧𝐜𝐭𝐢𝐨𝐧𝐬 — Your unfair advantage. This is where most people plateau. Running totals, rankings, lag/lead — suddenly you’re solving problems that used to need complex pipelines. From “just querying data” → to deriving insights inside SQL itself 3. 𝐐𝐮𝐞𝐫𝐲 𝐎𝐩𝐭𝐢𝐦𝐢𝐳𝐚𝐭𝐢𝐨𝐧 — The real differentiator Two engineers. Same result.
One query runs in seconds… the other burns compute for minutes. Indexes, execution plans, reducing scans — this is where you move from writing SQL to engineering performant systems. 💭Which of these made the biggest difference in your SQL journey? 👇 #SQL #DataEngineering #DataSkills #BigData #Analytics #TechCareers #CareerGrowth

Understanding the logical order of execution in SQL queries is a game-changer for every Data Analyst and Data Engineer 🚀 Many of us write queries starting with SELECT, but SQL actually processes them in a completely different order behind the scenes. Knowing this helps in writing optimized queries, debugging faster, and truly mastering data manipulation. 📌 SQL Logical Execution Order: FROM JOIN ON WHERE GROUP BY HAVING SELECT ORDER BY LIMIT 💡 Key Insight: Even though SELECT appears first in your query, it is executed almost at the end. This is why aliases created in SELECT often can’t be used in WHERE—because they don’t exist yet at that stage. Mastering this concept can significantly improve your query performance and problem-solving skills in real-world data scenarios. #SQL #DataAnalytics #DataEngineering #Learning #TechSkills #CareerGrowth

People spend months learning things they’ll never use. But these SQL commands? They cover 95% of real-world data work. 📊 Basic Data Retrieval Select records Count records Select distinct values Select specific columns 🔍 Filtering & Sorting Sort ascending / descending Filter with conditions (AND / OR) ✏️ Modifying Data Insert records Update records Delete records 📈 Analysis & Aggregation MIN / MAX AVG / SUM GROUP BY HAVING 🔗 Joins INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN In real-world projects, I’ve seen these commands solve almost everything. ✨️Everything you need to crack to become a Data Engineer - 𝙎𝙌𝙇, 𝙋𝙮𝙎𝙥𝙖𝙧𝙠, 𝘿𝙖𝙩𝙖𝙗𝙧𝙞𝙘𝙠𝙨 𝙞𝙣𝙩𝙚𝙧𝙫𝙞𝙚𝙬 𝙧𝙚𝙖𝙙𝙮 𝙥𝙖𝙘𝙠𝙖𝙜𝙚𝙨 + 1:1 𝙜𝙪𝙞𝙙𝙖𝙣𝙘𝙚, 𝙢𝙤𝙘𝙠 𝙞𝙣𝙩𝙚𝙧𝙫𝙞𝙚𝙬𝙨 & 𝙖𝙡𝙨𝙤 𝙟𝙤𝙞𝙣 𝙩𝙝𝙚 𝘿𝙖𝙩𝙖 𝙘𝙤𝙢𝙢𝙪𝙣𝙞𝙩𝙮 𝙩𝙤 𝙡𝙚𝙖𝙧𝙣 𝙛𝙧𝙤𝙢 𝙩𝙝𝙚 𝙗𝙚𝙨𝙩. 🔗 𝐄𝐱𝐩𝐥𝐨𝐫𝐞 𝐡𝐞𝐫𝐞: https://lnkd.in/gEkUiYrU #SQL #DataEngineering #DataAnalytics #LearnSQL #TechSkills #BigData

SQL looked so simple when I started… SELECT * FROM clean_data; That’s what I imagined Data Engineering would be. But reality? 👇 ❌ 200-line queries ❌ Multiple joins breaking everything ❌ Dirty & missing data ❌ Performance issues on large datasets ❌ And debugging… forever And then you realize — SQL is just the beginning. ⚠️ Real-world Data Engineering is not just writing queries: It’s about handling messy data, optimizing performance, and building reliable pipelines. 💡 What I learned: ✔ Clean data is a myth ✔ Optimization matters more than syntax ✔ Understanding data flow > writing queries ✔ Pipelines > SQL Because in reality… “SQL gets you started, but systems make you a Data Engineer.” If you’ve faced this, you know the struggle 😅 Drop a 🔥 if this is relatable #DataEngineering #SQL #BigData #ETL #DataPipeline #TechReality #Analytics #Debugging

With claude code, most analysts should be able to build data pipelines. In my jobs, i relied on data engineering teams and developers for data. Implementation time was in weeks, and debugging and correction took another 2-3 weeks. There was no alternative. This was a major bottleneck for making analytics useful. In my consulting projects, I felt stuck because of this. So i took up event implementation and data pipelines. The learning curve was steep but now i didn't have to rely on anyone. And In the last few months, i have offloaded much of this work to Claude. The possibilities have multiplied even more. Creating events for tricky things like horizontal scroll on mobile, or zoom on an image - is easy. On top of this, I can now build data pipelines with APIs to fetch any data that's publicly available + any exposed data from my own accounts. So getting data from 3rd party services, marketing platforms, or webhooks is also sorted. The days of waiting for data are done for good.

𝟴𝟬% 𝗼𝗳 𝗱𝗮𝘁𝗮 𝗲𝗻𝗴𝗶𝗻𝗲𝗲𝗿𝘀 𝗳𝗮𝗶𝗹 𝘁𝗵𝗶𝘀 𝗿𝗼𝘂𝗻𝗱. I've reviewed 50+ data engineering interviews at product companies. The same gap shows up every time. Candidates know the names: Delta, Iceberg, Hudi. 𝗔𝗹𝗺𝗼𝘀𝘁 𝗻𝗼𝗻𝗲 𝗰𝗮𝗻 𝗲𝘅𝗽𝗹𝗮𝗶𝗻 𝘁𝗵𝗲 𝗶𝗻𝘁𝗲𝗿𝗻𝗮𝗹𝘀 𝘁𝗵𝗮𝘁 𝗺𝗮𝗸𝗲 𝘁𝗵𝗲𝗺 𝗱𝗶𝗳𝗳𝗲𝗿𝗲𝗻𝘁. Here's what they're actually testing: They don't ask "what is Delta Lake?" 𝗧𝗵𝗲𝘆 𝗮𝘀𝗸 "𝘄𝗵𝗮𝘁 𝗶𝘀 𝘁𝗵𝗲 _𝗱𝗲𝗹𝘁𝗮_𝗹𝗼𝗴 𝗱𝗶𝗿𝗲𝗰𝘁𝗼𝗿𝘆 𝗮𝗻𝗱 𝘄𝗵𝘆 𝗱𝗼𝗲𝘀 𝗶𝘁 𝗲𝘅𝗶𝘀𝘁?" They don't ask "what is Iceberg?" 𝗧𝗵𝗲𝘆 𝗮𝘀𝗸 "𝘄𝗵𝗮𝘁 𝗵𝗮𝗽𝗽𝗲𝗻𝘀 𝘂𝗻𝗱𝗲𝗿 𝘁𝗵𝗲 𝗵𝗼𝗼𝗱 𝘄𝗵𝗲𝗻 𝘆𝗼𝘂 𝗰𝗵𝗮𝗻𝗴𝗲 𝗮 𝗽𝗮𝗿𝘁𝗶𝘁𝗶𝗼𝗻 𝘀𝗰𝗵𝗲𝗺𝗲?" They don't ask "what is Hudi?" 𝗧𝗵𝗲𝘆 𝗮𝘀𝗸 "𝗲𝘅𝗽𝗹𝗮𝗶𝗻 𝗖𝗼𝗪 𝘃𝘀 𝗠𝗼𝗥 — 𝗮𝗻𝗱 𝘄𝗵𝗶𝗰𝗵 𝗼𝗻𝗲 𝗯𝗿𝗲𝗮𝗸𝘀 𝗮𝘁 𝟭 𝗯𝗶𝗹𝗹𝗶𝗼𝗻 𝗱𝗮𝗶𝗹𝘆 𝘂𝗽𝘀𝗲𝗿𝘁𝘀." The answers to those questions are in the carousel above. 10 slides. White background. Dense content. ↳ Slide 2 — the wrong answer vs right answer on "what is a table format" ↳ Slides 3–5 — Delta, Iceberg, Hudi internals with real code ↳ Slide 6 — head-to-head comparison across 11 dimensions ↳ Slides 7–8 — schema evolution and time travel internals ↳ Slide 9 — decision framework for which format to choose ↳ Slide 10 — complete cheat sheet to screenshot Save this before your next data engineering loop. Full table formats interview prep course on Topmate — link in comments. Which concept surprised you most — and which one cost you an interview? Drop your story below. Follow Divyanshi Garg for more such updates. #DataEngineering #DeltaLake #ApacheIceberg #ApacheHudi #DataLakehouse #SQL

1 AM. Eyes burning. Pipelines running. 🌃 If you are a Data Engineer working late tonight, take a breath. The relentless days are exactly what forge Senior DEs. But hard work needs to be paired with smart logic. Tonight, let's look at a trick that separates the amateurs from the pros: The "Gaps and Islands" Pattern. 🧩 The Problem: You need to find users who logged in for exactly N consecutive days (or finding N consecutive transactions). ❌ The Junior Approach: Writing N-1 Self-Joins. (If N=10, your query plan will cry, and your cluster will crash). ❌ The Intermediate Approach: Using LEAD() and LAG(). (Great for N=3, but completely unscalable if N changes dynamically). ✅ The Senior Approach: Gaps and Islands using simple Math + Window Functions. The Logic: If you take an incrementing ID (or Date) and subtract a ROW_NUMBER() (partitioned by the user/event), the difference will remain CONSTANT for consecutive rows! Once you have that constant (the "Island"), you just GROUP BY it and COUNT.

As a data engineer working in ops, SQL is at the core of my daily work. After my post about window functions, I figured I’d share a list of the SQL functions that I use the most: ▶ SELECT ▶ WHERE ▶ ORDER BY ▶ JOINS (INNER, LEFT, FULL) ▶ Aggregation functions (SUM, AVG, COUNT, MAX) ▶ GROUP BY ▶ UNION ▶ CASE STATEMENT ▶ CTEs ▶ Date and Time operations (EXTRACT, DATE_ADD, DATE_SUB) ▶ COALESCE & IFNULL ▶ CAST & CONVERT ▶ Ranking functions (ROW_NUMBER, RANK, DENSE_RANK) ▶ QUALIFY ▶ String functions (SUBSTRING, CONCAT) ▶ LEAD & LAG Even with so many functions available, I usually use these few mentioned above. This is a great example of the 80/20 rule—you really only need about 20% of the available SQL functions to handle 80% of your daily tasks. What are some of the SQL functions that you use on a regular basis? Drop them in the comments—I’m curious to see what others are using! #DataEngineering #SQL

80% of data engineers I've interviewed mix up at least two of these three. And it's the #1 reason system design rounds go wrong. Not because engineers don't know what partitioning is. Because they use the words interchangeably — and interviewers notice immediately. Here's the honest breakdown: 𝗣𝗮𝗿𝘁𝗶𝘁𝗶𝗼𝗻𝗶𝗻𝗴, 𝗦𝗵𝗮𝗿𝗱𝗶𝗻𝗴, 𝗮𝗻𝗱 𝗕𝘂𝗰𝗸𝗲𝘁𝗶𝗻𝗴 𝗮𝗿𝗲 𝗻𝗼𝘁 𝘀𝗶𝗺𝗶𝗹𝗮𝗿 𝗰𝗼𝗻𝗰𝗲𝗽𝘁𝘀. They solve completely different problems at completely different layers. 𝗣𝗮𝗿𝘁𝗶𝘁𝗶𝗼𝗻𝗶𝗻𝗴 splits data into folders by column value — on the same machine. When you query WHERE event_date = '2026-01-15', Spark skips every other folder entirely. Zero I/O on unneeded data. 𝗦𝗵𝗮𝗿𝗱𝗶𝗻𝗴 splits data across different servers. Nothing to do with query speed — everything to do with write scale. When your PostgreSQL table can't fit on one machine, you shard. Different problem. Different layer entirely. 𝗕𝘂𝗰𝗸𝗲𝘁𝗶𝗻𝗴 splits data into a fixed number of files using a hash function. No folder skipping. No scaling. One benefit only — join optimisation. Two tables bucketed on the same column join without shuffling across the network. In large Spark jobs this is the difference between a 2-minute join and a 20-minute one. 𝗧𝗵𝗲 𝗼𝗻𝗲-𝗹𝗶𝗻𝗲 𝗰𝗵𝗲𝗮𝘁 𝘀𝗵𝗲𝗲𝘁: Partitioning = skip folders → read speed Sharding = scale machines → write scale Bucketing = skip shuffle → join speed 𝗧𝗵𝗲 𝟯 𝗺𝗶𝘀𝘁𝗮𝗸𝗲𝘀 𝘁𝗵𝗮𝘁 𝗳𝗮𝗶𝗹 𝗶𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝘀: Mistake 1 → Over-partitioning on user_id. One folder per user = millions of tiny files. Metadata explosion. Spark dies. Mistake 2 → Expecting bucketing to skip data at read time. It doesn't. Engineers confuse it with partitioning every single time. Mistake 3 → Sharding on a timestamp. All writes go to the last shard. Hotspot. Database melts. The image above has visual diagrams showing exactly how each one distributes data physically, the full comparison table, and all three common mistakes explained. Save it before your next system design interview. Which of these three trips you up most — and which one do you think most engineers get wrong? Drop it in the comments 👇 #DataEngineering #ApacheSpark #DataEngineer #SystemDesign #BigData