Solved Invalid Tweets problem on LeetCode with SQL LENGTH() function

SQL Progress: self join, numeric, avg, group by!  Today I solved a very interesting challenge on LeetCode: "Average Time of Process per Machine". It was a great exercise for using Joins and Math functions together. What I learned today: 1. Self-Join: I learned how to join the same table with itself (Activity a1, Activity a2). This is the best way to compare two different rows—like a "start" and an "end" timestamp—for the same process. 2. Aggregate Functions: I used AVG() to find the average time and ROUND(..., 3) to keep the results clean and precise as required. 3. PostgreSQL Specifics: I learned that using ::numeric is important for the ROUND function to work correctly in PostgreSQL. 4. Grouping: Used GROUP BY machine_id to make sure the calculation is done for each machine separately. I’ve attached my solution below.  Question for the experts: Is there a way to solve this without using a Join? I’d love to hear your thoughts! قليل مستمر خير من كثير منقطع #SQL #DataEngineering #PostgreSQL #LeetCode #100DaysOfCode #ProblemSolving

I used to think I "knew" SQL. Then I started building my own project and realized that "knowing" the syntax isn't the same as "understanding" the logic. I’ve spent the last few hours with Mosh Hamedani’s course, un-learning my bad habits. 3 things I’m focusing on today: JOIN Logic: Understanding exactly which "circle" of the Venn diagram I need. Subqueries: When to use them (and when they’re a performance trap). Optimization: Thinking like the Database Engine. Consistency isn't about how much you learn in a day; it’s about showing up even when the logic gets tough. Are you Team #SQL or Team #NoSQL for your personal projects? #CodeWithMosh #Backend #DeveloperLife #Consistency #TechGrowth

I handwrote a complete SQL Roadmap so you don't have to google "where to start with SQL" ever again. 🗺️ Here's everything covered (Beginner → Advanced): 📌 SQL Commands → DDL: CREATE, ALTER, DROP, TRUNCATE → DML: INSERT, UPDATE, DELETE → TCL: COMMIT, ROLLBACK → DCL: GRANT, REVOKE → DQL: SELECT 📌 Clauses → WHERE, HAVING, GROUP BY, ORDER BY, FROM 📌 Joins → INNER, LEFT, RIGHT, FULL OUTER, CROSS, SELF 📌 Subqueries → Scalar, Inline, Correlated, CTE 📌 Indexes → Unique, Bitmap, B-Tree, Composite 📌 Functions → Aggregate, Arithmetic, Date, Char, Analytical, REGEXP 📌 Views, Constraints, Normalization, ACID Properties 📌 Optimization → Explain Plan, Cost, Cardinality, Logical & Relational Sets This is everything you need to go from zero to SQL pro. 💪 💾 Save this post — you'll need it. 🔁 Repost to help someone learning SQL right now. 👉 Follow me for more handcrafted roadmaps like this! Which topic from this roadmap do YOU find hardest? Comment below 👇 #SQL #DataAnalytics #DataEngineering #DataScience #Programming #OracleDatabase #Tech #CareerGrowth #Data #100DaysOfCode

Common Table Expressions (CTEs) are one of the most underrated features in SQL. They help break complex queries into simple, readable steps using the WITH clause—making your code cleaner, easier to debug, and more maintainable. From handling multiple transformations to solving hierarchical problems with recursive CTEs, they’re a must-know for any data professional. If you’re still relying heavily on nested subqueries, it might be time to switch to CTEs 🚀 #SQL #DataAnalytics #DataEngineering #Learning #TechSkills

Well these days I'm busy with wondered how your SQL queries are actually processed under the hood? ⚙️ And I came up with the actual logical order of execution might surprise you! 💡 While we write them with SELECT at the top, the database engine follows a specific sequence. Understanding this order is crucial for: ✅ Writing efficient and optimized queries 🚀 ✅ Accurate debugging and troubleshooting 🔍 ✅ Avoiding common pitfalls and unexpected results ⚠️ Check out this visual breakdown from 1 to 9! 👇 1. 🏗️ FROM & JOIN (Gathering and combining source tables) 2. 🏷️ ON (Applying join conditions) 3. ❌ WHERE (Filtering rows before grouping) 4. 📊 GROUP BY (Aggregating rows into groups) 5. ⚖️ HAVING (Filtering groups after grouping) 6. ✨ SELECT (Specifying columns and calculations) 7. 💎 DISTINCT (Removing duplicate rows, if applicable) 8. 🔢 ORDER BY (Sorting the final result set) 9. 📋 LIMIT/TOP/OFFSET (Selecting a subset of sorted rows) Mastering this concept is a game-changer for anyone working with databases. Happy querying! 💻 #SQL #DataAnalytics #DataScience #Database #DataEngineering #Programming #LearnSQL #TechSkills #BigData #LogicalOrder

✅ Solved a SQL problem on LeetCode — Day 44 of my SQL Journey 💪 IP addresses look structured… but validating them isn’t always simple 🌐 Today’s problem was about identifying invalid IP addresses — not just formatting issues, but logical errors inside each segment. I used string functions and conditional checks to: • Split IP into individual octets using string operations • Validate the number of segments (must be 4) • Check if each octet is within the valid range (0–255) • Detect non-numeric values using pattern matching • Count how often each invalid IP appears What I practised: • Working with string parsing in SQL • Using SUBSTRING_INDEX() for segment extraction • Applying REGEXP for pattern validation • Combining multiple validation rules in one query What stood out — Validation isn’t just about format… it’s about the rules behind the format. A value can look correct, but still logically invalid. That’s where careful checking matters. SQL isn’t just for analysis. It can also enforce data quality. Consistent learning, one query at a time 🚀 #SQL #LeetCode #SQLPractice #DataAnalytics #LearningInPublic

✅ Solved a SQL problem on LeetCode — Day 43 of my SQL Journey 💪 Text looks simple… until you try to handle every edge case ✍️ Today’s problem was about transforming text — capitalising the first letter of each word, while handling special cases like hyphens correctly. I used recursive logic and string operations to: • Break text into individual characters using recursive CTE • Track previous characters using window functions • Identify word boundaries and special cases • Apply conditional uppercase/lowercase transformations • Reconstruct the final string using GROUP_CONCAT What I practised: • Recursive CTEs for step-by-step processing • Using LAG() to track character-level context • Writing precise CASE conditions for formatting • Handling edge cases like hyphenated words What stood out — Text transformations aren’t just formatting… they’re about handling context. A single character can change the logic, and missing one condition can break everything. That’s where attention to detail matters most. SQL isn’t just for numbers and aggregations. It can handle complex text logic too. Consistent learning, one query at a time 🚀 #SQL #LeetCode #SQLPractice #DataAnalytics #LearningInPublic

📊 Recently, I took some time to revisit core SQL concepts while practicing query-based problems on LeetCode. It was a great way to strengthen fundamentals like: • JOINs and subqueries • GROUP BY & aggregation • Window functions • Writing optimized queries for real-world scenarios Along with problem-solving, I also revised important database concepts such as ACID properties and Normalization, which are essential for designing reliable and scalable systems. To keep everything in one place, I’ve created a concise PDF that includes: ✔ Common SQL queries (interview-focused) ✔ Clear explanation of ACID properties ✔ Normalization concepts with examples Sharing it here in case it helps others who are preparing for SQL interviews or brushing up their basics. #SQL #LeetCode #Database #BackendDevelopment #InterviewPreparation #LearningJourney

Stop writing SQL "Inception." Use CTEs instead. 🛑📖 We’ve all been there: opening a query only to find a subquery, inside a subquery, inside another subquery. It’s hard to read, impossible to debug, and a nightmare for your future self. If you want to move from a "Junior" to a "Senior" SQL mindset, you need to master CTEs (Common Table Expressions). Why CTEs are a game-changer: Readability: They allow you to read code from top to bottom, like a story, rather than from the inside out. Reusability: You can reference the same CTE multiple times in one query. No more copy-pasting the same subquery logic! Debugging: You can test each "layer" of your data transformation individually before joining them all together. The Golden Rule: If your logic has more than two levels of nesting, it’s time for a WITH clause. #SQL #DataEngineering #Database #CodingTips #CleanCode #DataAnalytics #CareerGrowth

✅ Solved a SQL problem on LeetCode — Day 48 of my SQL Journey 💪 Not all active users are valuable… Some look busy 👀 Today’s problem was about identifying zombie sessions — users who scroll endlessly but never click, never buy, never engage. Active on paper. Dead in reality. The approach: • Calculated session duration using MIN() and MAX() timestamps • Counted scrolls and clicks with conditional aggregation • Computed click-to-scroll ratio inside HAVING • Filtered sessions: duration > 30 min, scrolls ≥ 5, ratio < 0.20, zero purchases What I practised: • Conditional aggregation using CASE WHEN • Session-level grouping • Ratio logic inside HAVING • Translating behaviour into SQL filters What stood out — Scrolling is passive. Clicking is intent. Buying is a commitment. A session full of scrolls but no clicks It's just a ghost. That’s where the real insight lies. SQL doesn’t just count events. It reads the story behind them. Consistent learning, one query at a time 🚀 #SQL #LeetCode #DataAnalytics #LearningInPublic #SQLPractice