SQL Tricks for Data Engineers: Window Functions, CTEs, and CASE WHEN

SQL window functions changed how I think about data. Before I learned them, I was writing subqueries for everything. Clunky. Repetitive. Hard to read. Then I discovered window functions, and the same logic became cleaner, faster, and easier for anyone to follow. The one I kept reaching for: ROW_NUMBER() It assigns a unique rank to each row within a group. Simple idea. Powerful in practice. Real example: find the most recent order per customer. Without window functions: → Write a subquery to get max date per customer → Join it back to the original table → Hope nothing breaks With ROW_NUMBER(): → Partition by customer → Order by date descending → Filter where row = 1 Same result. Half the code. Much easier to explain to a colleague. I used this constantly when building SQL pipelines, pulling the latest record per entity from multi-source business data. It saved time and made my queries reviewable. If you're writing SQL regularly and haven't touched window functions yet, ROW_NUMBER() is where I'd start. Small function. Big shift in how you think. Which SQL concept clicked everything into place for you? Drop it below 👇 #SQL #DataAnalytics #DataScience #LearningInPublic

🚨 Common SQL Mistakes That CRASH Production (And How to Fix Them) 🚨 As a Data Analyst with 5+ years optimizing queries at scale, I've seen these SQL blunders cause prod failures, slow dashboards, and endless firefighting. Here's my top 7 that bite hardest – with fixes to bulletproof your code. 1. SELECT * Everywhere Pulls unnecessary columns, bloating memory and breaking when schemas change. ✅ Fix: SELECT order_id, customer_name FROM orders; – explicit columns only. 2. Missing WHERE in UPDATE/DELETE The ultimate prod killer – wipes entire tables accidentally. ✅ Fix: Always test with SELECT first, then add WHERE. Use transactions: BEGIN TRANSACTION; 3. Functions on Indexed Columns WHERE YEAR(order_date) = 2025 kills indexes, forces full scans. ✅ Fix: WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01' 4. NOT IN with NULLs Subquery has NULL? Entire result vanishes silently. ✅ Fix: Use NOT EXISTS or LEFT JOIN WHERE alias.col IS NULL 5. No Indexes on JOIN/WHERE Columns Fine in dev, crawls in prod with real data. ✅ Fix: Index foreign keys, frequent filters: CREATE INDEX idx_order_date ON orders(date); 6. Subqueries vs JOINs Correlated subs run per row – N+1 hell. ✅ Fix: Rewrite as JOINs for massive speedups. 7. DISTINCT Overuse Masks dupes but sorts everything, tanks perf. ✅ Fix: Fix root cause with proper GROUP BY or DISTINCT ON (Postgres). Pro Tip: Always check execution plans before prod. What's your worst SQL war story? 👇 #SQL #DataEngineering #Database #PowerBI #DataAnalytics #TechTips

This SQL query runs perfectly. No errors. Clean logic. Looks correct. But the result is completely wrong. Look at the query in the image. It tries to calculate total revenue per customer. And at first glance, everything seems fine. But there’s a subtle mistake in the aggregation logic that completely distorts the output. This is exactly how production dashboards get incorrect numbers — not because queries fail, but because they silently return misleading results. Your challenge: What is wrong with this query? Write the correct SQL in the comments. Follow Data Rejected for real-world SQL challenges. Repost if this could help someone preparing for interviews or debugging production queries. Subscribe on YouTube for full SQL breakdowns. #SQL #DataEngineering #AnalyticsEngineering #DataAnalytics #Database #LearnSQL #SQLTips #TechCareers #QueryOptimization #BusinessIntelligence #DataRejected

𝗧𝗛𝗜𝗦 𝗪𝗜𝗡𝗗𝗢𝗪 𝗙𝗨𝗡𝗖𝗧𝗜𝗢𝗡 𝗘𝗥𝗥𝗢𝗥 𝗙𝗔𝗜𝗟𝗦 𝗦𝗘𝗡𝗜𝗢𝗥 𝗦𝗤𝗟 𝗜𝗡𝗧𝗘𝗥𝗩𝗜𝗘𝗪𝗦 This SQL query looks perfect. Window function. Partitioning done correctly. No syntax issues. But the result is wrong. The mistake is subtle — and it happens because of how ordering works inside window functions. In real production scenarios, this can lead to: • Incorrect comparisons • Wrong business decisions • Misleading dashboards Most engineers don’t notice it until it’s too late. Your challenge: What is wrong with this query? Write the correct SQL in the comments. Follow Data Rejected for real-world SQL traps. Repost this if it might help someone preparing for interviews or debugging production issues. Subscribe on YouTube for deep SQL breakdowns. #SQL #DataEngineering #Analytics #DataAnalytics #SQLTips #LearnSQL #WindowFunctions #DataEngineeringLife #TechCareers #DataRejected

🔍 Anatomy of Your First SQL Query Every data journey starts with a simple query — but understanding how it really works makes all the difference. Here’s the breakdown 👇 ✔️ Writing Order vs Execution Order We write SQL as: SELECT → FROM → WHERE But SQL actually executes as: FROM → WHERE → SELECT 👉 Knowing this helps you debug faster and write smarter queries. ✔️ Core SQL Clauses SELECT → Choose only the columns you need (avoid *) FROM → Define your data source WHERE → Filter your data for meaningful insights ✔️ Pro Tips for Professionals 💡 Avoid SELECT * — improves performance & clarity 💡 Keep queries clean & readable (indentation matters) 💡 Always think like an analyst — ask specific questions 📊 SQL is not just about writing queries… It’s about asking the right questions from your data. #SQL #DataAnalytics #LearningSQL #DataAnalyst #CareerGrowth #TechSkills

𝗖𝗼𝗺𝗺𝗼𝗻 𝗦𝗤𝗟 𝗠𝗶𝘀𝘁𝗮𝗸𝗲𝘀 (𝗮𝗻𝗱 𝗛𝗼𝘄 𝘁𝗼 𝗙𝗶𝘅 𝗧𝗵𝗲𝗺) Over time, I’ve seen a few SQL mistakes that can silently break logic or performance. Here are some common ones and how to avoid them: 1. 𝗙𝗼𝗿𝗴𝗲𝘁𝘁𝗶𝗻𝗴 𝘁𝗵𝗲 𝗪𝗛𝗘𝗥𝗘 𝗖𝗹𝗮𝘂𝘀𝗲 Running 𝗗𝗘𝗟𝗘𝗧𝗘 or 𝗨𝗣𝗗𝗔𝗧𝗘 without a 𝗪𝗛𝗘𝗥𝗘 clause can wipe out entire tables. Always double-check your conditions and use transactions when working with critical data. One small miss can lead to massive data loss. 2. 𝗢𝘃𝗲𝗿𝘂𝘀𝗶𝗻𝗴 𝗦𝗘𝗟𝗘𝗖𝗧 * Using 𝗦𝗘𝗟𝗘𝗖𝗧 * fetches unnecessary columns, slows down queries, and makes code less readable. Instead, select only the columns you need—it improves performance and keeps queries future-proof. 3. 𝗖𝗼𝗺𝗽𝗮𝗿𝗶𝗻𝗴 𝘄𝗶𝘁𝗵 𝗡𝗨𝗟𝗟 𝗜𝗻𝗰𝗼𝗿𝗿𝗲𝗰𝘁𝗹𝘆 𝗡𝗨𝗟𝗟 is not a value, so = 𝗡𝗨𝗟𝗟 won’t work. Always use 𝗜𝗦 𝗡𝗨𝗟𝗟 or 𝗜𝗦 𝗡𝗢𝗧 𝗡𝗨𝗟𝗟. This ensures correct filtering and avoids unexpected empty results. 4. 𝗚𝗿𝗼𝘂𝗽𝗶𝗻𝗴 𝗜𝘀𝘀𝘂𝗲𝘀 𝗶𝗻 𝗦𝗘𝗟𝗘𝗖𝗧 Every non-aggregated column in your 𝗦𝗘𝗟𝗘𝗖𝗧 must be in the 𝗚𝗥𝗢𝗨𝗣 𝗕𝗬. Ignoring this leads to errors or incorrect results. Follow SQL standards for clean and accurate aggregation. 5. 𝗜𝗻𝗰𝗼𝗿𝗿𝗲𝗰𝘁 𝗚𝗥𝗢𝗨𝗣 𝗕𝗬 𝗨𝘀𝗮𝗴𝗲 Grouping without proper structure can make your results confusing. Use meaningful groupings and ensure your query clearly reflects the business logic behind the data. 6. 𝗠𝗶𝘀𝘀𝗶𝗻𝗴 𝗣𝗮𝗿𝗲𝗻𝘁𝗵𝗲𝘀𝗲𝘀 𝗶𝗻 𝗖𝗼𝗺𝗽𝗹𝗲𝘅 𝗟𝗼𝗴𝗶𝗰 When combining 𝗔𝗡𝗗 and 𝗢𝗥, operator precedence can change results. Always use parentheses to define logic explicitly; it improves readability and prevents logical bugs. 💡 𝗙𝗶𝗻𝗮𝗹 𝗧𝗵𝗼𝘂𝗴𝗵𝘁: Small SQL mistakes can lead to big data issues. Writing clean, intentional queries is just as important as getting the result. If you’ve faced similar issues, I would love to hear your experiences 👇 Follow Aman Gambhir for more content like this. #SQL #sqltips #sqlquery #query #sqlmistakes #optimization

🚀 SQL Concepts You MUST Know: Subquery vs Temp Table vs CTE If you’re preparing for SQL interviews or working as a data engineer, these 3 concepts come up everywhere 👇 🔹 Subquery A query inside another query 👉 Best for simple filtering or quick lookups 🔹 Temporary Table Stores intermediate results physically (for the session) 👉 Useful when working with large datasets or multiple reuses 🔹 CTE (Common Table Expression) A readable, reusable query block using WITH 👉 Perfect for complex logic and recursive queries 💡 Quick Insight: Subquery → Quick & simple Temp Table → Heavy data, reuse multiple times CTE → Clean & readable logic 🔥 Real-world tip: Good engineers don’t just know how to write queries… They know when to use the right approach. 💬 Which one do you use the most in your projects? #SQL #DataEngineering #InterviewPrep #Database #Learning #Analytics #Developer

SQL Challenge 9/100 : Best Time to Trade Stock 📈 (Part-4) 💪 Difficulty - Hard 🔗 Part 3: Refer old post You are given a table with stock prices 📊 🎯 Problem Let’s make it tougher 👇 👉 You can make at most 2 transactions 🔁 👉 You cannot hold multiple stocks at the same time 🚫📦 👉 Must sell before buying again 🔄 ❓ Your Task Write a SQL query to: ✅ Return maximum profit 💰 ✅ Also return the buy & sell days for both transactions\ ⚠️ Rules: Max 2 transactions only 🔢 No overlapping 🚫 Buy < Sell always ⏳ No hardcoding ❌ -- Create table CREATE TABLE stock_prices (   day INT,   price INT ); -- Insert data INSERT INTO stock_prices (day, price) VALUES (1, 100), (2, 180), (3, 260), (4, 310), (5, 40), (6, 535), (7, 695); Tagging Ankit Bansal Sumit Mittal for better reach Sanjay Gatti #SQL #DataEngineering #SQLChallenge #Analytics #LearnSQL 🚀

Most data analysts have never written a recursive CTE. Then they're asked one in an interview and freeze. Recursive CTEs solve 3 specific problems that nothing else solves cleanly: 1. HIERARCHIES — "Find all employees under manager X, at any depth" WITH RECURSIVE tree AS ( SELECT id, manager_id, name, 1 AS depth FROM employees WHERE id = 5 UNION ALL SELECT e.id, e.manager_id, e.name, t.depth + 1 FROM employees e JOIN tree t ON e.manager_id = t.id ) SELECT * FROM tree; 2. DATE SPINES — "Generate a row for every day in 2026" WITH RECURSIVE dates AS ( SELECT DATE '2026-01-01' AS d UNION ALL SELECT d + INTERVAL '1 day' FROM dates WHERE d < DATE '2026-12-31' ) SELECT d FROM dates; Useful for filling missing dates in time series before joining to fact tables. 3. GRAPH TRAVERSAL — "Find all friends-of-friends within N degrees" Same UNION ALL pattern: anchor + recursive step + termination condition. When you SHOULDN'T use recursive CTEs: - Flat lookups (just JOIN) - Anything that fits in a single window function - Loops > 100 deep (databases hate this) When you SHOULD: - Org charts, category trees, comment threads - Date/number generation - Bill of materials, dependency graphs Practice 8 recursive CTE problems on real databases: https://lnkd.in/gs7Eueed Day 3 of 7 — Advanced SQL. #SQL #RecursiveCTE #AdvancedSQL #DataAnalyst #SQLInterview #DataAnalytics #InterviewPrep #FreeResources