Monojit Saha

🔥 𝐓𝐡𝐞 𝐒𝐐𝐋 𝐏𝐨𝐰𝐞𝐫 𝐌𝐨𝐯𝐞 𝐌𝐨𝐬𝐭 𝐎𝐯𝐞𝐫𝐥𝐨𝐨𝐤 Everyone writes SQL… But the moment interviews ask for window functions, things fall apart. They’re the real shortcut for ranking, running totals, and previous/next row comparison. (Credit: Raushan Kumar) Here’s the essence from the PDF: • 𝐑𝐎𝐖_𝐍𝐔𝐌𝐁𝐄𝐑 / 𝐑𝐀𝐍𝐊 / 𝐃𝐄𝐍𝐒𝐄_𝐑𝐀𝐍𝐊 — ranking inside groups • 𝐋𝐄𝐀𝐃 / 𝐋𝐀𝐆 — compare rows without joins • 𝐅𝐈𝐑𝐒𝐓_𝐕𝐀𝐋𝐔𝐄 / 𝐋𝐀𝐒𝐓_𝐕𝐀𝐋𝐔𝐄 — pick highest/lowest • 𝐒𝐔𝐌 / 𝐀𝐕𝐆 — running totals & averages • 𝐍𝐓𝐈𝐋𝐄 / 𝐂𝐔𝐌𝐄_𝐃𝐈𝐒𝐓 — distribution insights 𝐐𝐮𝐢𝐜𝐤 𝐓𝐢𝐩: Rewrite one ranking or cumulative query today using a window function — your SQL will instantly look cleaner. 𝐒𝐚𝐯𝐞 𝐭𝐡𝐢𝐬 𝐩𝐨𝐬𝐭 — it’s a cheat sheet for interviews. 𝐉𝐨𝐢𝐧 𝐌𝐲 𝐂𝐡𝐚𝐧𝐧𝐞𝐥𝐬 𝐟𝐨𝐫 𝐔𝐩𝐝𝐚𝐭𝐞𝐬 Book a 1:1 session on Topmate: https://lnkd.in/gzcV5_ua 𝐖𝐡𝐚𝐭𝐬𝐀𝐩𝐩 𝐔𝐩𝐝𝐚𝐭𝐞𝐬: https://lnkd.in/e6MfYSnD 𝐃𝐚𝐭𝐚 𝐄𝐧𝐠𝐢𝐧𝐞𝐞𝐫𝐢𝐧𝐠: t.me/+4dDVlzXpYz1hM2Rl 𝐉𝐨𝐛 𝐀𝐥𝐞𝐫𝐭𝐬: t.me/+2FsyYrGsLzlhMzJl

🚀 𝐅𝐢𝐯𝐞 𝐏𝐨𝐰𝐞𝐫𝐟𝐮𝐥 𝐖𝐚𝐲𝐬 𝐭𝐨 𝐑𝐞𝐦𝐨𝐯𝐞 𝐃𝐮𝐩𝐥𝐢𝐜𝐚𝐭𝐞 𝐑𝐨𝐰𝐬 𝐢𝐧 𝐒𝐐𝐋 Tired of duplicate rows cluttering your tables? Here are 5 effective SQL techniques every data engineer should know: 1. ROW_NUMBER() with CTE Assigns row numbers to duplicates and keeps the first one. Fast, scalable, and works well with large datasets. 2. Self-Join Approach Joins the table with itself and deletes rows with higher IDs.Simple and compatible across most SQL databases. 3. DELETE with EXISTS Uses a correlated subquery to check for duplicates. More efficient than JOIN in many cases. 4. Backup Table & TRUNCATE Backup unique records → Truncate table → Reload. Ensures data safety during cleanup. 5. NOT IN with MIN(id) Keeps the row with the smallest ID per group. Works great for small to medium datasets. Best Method? ROW_NUMBER() with CTE Because it’s precise, performant, and universally reliable! Credit: All thanks to Kishan Soni for putting this practical guide together! Like this post Comment your go-to method Repost to help your network Follow Monojit Saha for more Data Engineering content 𝐉𝐨𝐢𝐧 𝐌𝐲 𝐂𝐡𝐚𝐧𝐧𝐞𝐥𝐬 𝐟𝐨𝐫 𝐔𝐩𝐝𝐚𝐭𝐞𝐬 📌Book a 1:1 session on Topmate: https://lnkd.in/gzcV5_ua 📌𝐖𝐡𝐚𝐭𝐬𝐀𝐩𝐩 𝐔𝐩𝐝𝐚𝐭𝐞𝐬: https://lnkd.in/e6MfYSnD 📌𝐃𝐚𝐭𝐚 𝐄𝐧𝐠𝐢𝐧𝐞𝐞𝐫𝐢𝐧𝐠: t.me/+4dDVlzXpYz1hM2Rl 📌𝐉𝐨𝐛 𝐀𝐥𝐞𝐫𝐭𝐬: t.me/+2FsyYrGsLzlhMzJl #SQL #DataEngineering #SQLPerformance #DataQuality #DataCleaning #ETL #Analytics #JobSearch #HiringNow #HR #TalentAcquisition #TechCareers #LinkedInTech #DataJobs #LearningSQL #DataEngineers #SQLTips #InterviewReady

𝗗𝗮𝘁𝗮 𝗰𝗹𝗲𝗮𝗻𝗶𝗻𝗴 — 𝘁𝗵𝗲 𝗲𝗻𝗴𝗶𝗻𝗲 𝗼𝗳 𝗲𝘃𝗲𝗿𝘆 𝗱𝗮𝘁𝗮 𝗽𝗿𝗼𝗷𝗲𝗰𝘁 When you work as a 𝗗𝗮𝘁𝗮 𝗘𝗻𝗴𝗶𝗻𝗲𝗲𝗿, you quickly realize that no matter how advanced your pipelines are — if your data isn’t clean, your insights aren’t reliable. I recently came across a beautifully structured SQL Data Cleaning Guide by Guest that explains every essential function used to clean, transform, and standardize data. Here’s what it covers 𝗛𝗮𝗻𝗱𝗹𝗲 𝗠𝗶𝘀𝘀𝗶𝗻𝗴 𝗩𝗮𝗹𝘂𝗲𝘀: Using `COALESCE()`, `IFNULL()`, and `ISNULL()` to fill nulls and maintain consistency. 𝗥𝗲𝗺𝗼𝘃𝗲 𝗗𝘂𝗽𝗹𝗶𝗰𝗮𝘁𝗲𝘀: Learn when to use `DISTINCT()` vs. `ROW_NUMBER()` — a game changer for de-duplication strategies. 𝗦𝘁𝗮𝗻𝗱𝗮𝗿𝗱𝗶𝘇𝗲 𝗧𝗲𝘅𝘁: Functions like `LOWER()`, `UPPER()`, and `TRIM()` ensure clean and uniform string data. 𝗖𝗼𝗿𝗿𝗲𝗰𝘁 𝗜𝗻𝗰𝗼𝗻𝘀𝗶𝘀𝘁𝗲𝗻𝘁 𝗗𝗮𝘁𝗮: With `SUBSTR()` and `CONCAT()`, even the messiest product codes can be standardized. 𝗖𝗵𝗮𝗻𝗴𝗲 𝗗𝗮𝘁𝗮 𝗧𝘆𝗽𝗲𝘀: `CAST()` and `CONVERT()` — small functions, huge impact in type conversions and formatting. 𝗛𝗮𝗻𝗱𝗹𝗲 𝗗𝗮𝘁𝗲 𝗙𝗼𝗿𝗺𝗮𝘁𝘀: Use `STR_TO_DATE()`, `EXTRACT()`, `DATE_FORMAT()`, and `NOW()` to convert, extract, and align inconsistent date fields. 𝗘𝗻𝗳𝗼𝗿𝗰𝗲 𝗗𝗮𝘁𝗮 𝗜𝗻𝘁𝗲𝗴𝗿𝗶𝘁𝘆: `CHECK` and `FOREIGN KEY` constraints keep your database reliable and secure. 𝗛𝗮𝗻𝗱𝗹𝗲 𝗡𝘂𝗺𝗲𝗿𝗶𝗰 𝗩𝗮𝗹𝘂𝗲𝘀: `ROUND()`, `CEIL()`, `FLOOR()`, and `ABS()` — precision meets consistency. Each concept is backed by clear 𝗦𝗤𝗟 𝗲𝘅𝗮𝗺𝗽𝗹𝗲𝘀, making it a must-read for anyone working with real-world data. If you’re looking to strengthen your SQL foundation and ensure data quality at scale, this guide is pure gold. Kudos to the creator — Guest — for such a detailed resource. Like, Comment, and Repost to help more data professionals learn. Follow me for more data engineering insights and learning materials. 📌 𝗝𝗼𝗶𝗻 𝗠𝘆 𝗖𝗵𝗮𝗻𝗻𝗲𝗹𝘀 𝗳𝗼𝗿 𝗨𝗽𝗱𝗮𝘁𝗲𝘀 Book a 1:1 session on Topmate: [https://lnkd.in/guW8yRW4) Data Engineering:t.me/+4dDVlzXpYz1hM2Rl Job Alerts: t.me/+2FsyYrGsLzlhMzJl WhatsApp Updates: https://lnkd.in/e6MfYSnD #DataCleaning #SQL #DataEngineering #ETL #DataTransformation #AzureDataEngineer #Python #Spark #DataQuality #Hiring #TalentAcquisition #Analytics #BigData #DataEngineers #LinkedInLearning #MonojitSaha**