SQL LeetCode Challenge: Text Transformation with Recursive CTE

✅ Solved a SQL problem on LeetCode — Day 47 of my SQL Journey 💪 Learning doesn’t always move in a straight line… sometimes it spirals 🔄 Today’s problem was about identifying students who follow a study spiral pattern — studying multiple subjects in a structured, repeating cycle. The approach: • Tracked session order using ROW_NUMBER() • Measured gaps between sessions with LAG() and DATEDIFF() • Filtered sequences with gaps longer than 2 days • Detected repeating cycles using MOD() on row position • Counted students with at least 3 subjects across multiple cycles What I practised: • Window functions for sequence tracking • Time gap detection using date functions • Sequential pattern recognition • Using HAVING for conditional aggregation What stood out — A single session tells you nothing… A sequence tells you everything. Patterns don’t announce themselves, They hide in the order of events. That’s where the real insight lies. SQL doesn’t just query data. It helps read the story behind it. Consistent learning, one query at a time 🚀 #SQL #LeetCode #DataAnalytics #LearningInPublic #SQLPractice

call me old fashioned but there’s still something thrilling about writing a sql query solo on a tight deadline. character building.

**Day 9 of my 30 Days SQL Series 🚀** Today’s question was “Not Boring Movies” from LeetCode. At first, it looked like a simple filtering problem, but while solving it, I got stuck on a small concept. --- ### 💡 What the question was asking: We were given a table of movies with: * id * movie name * description * rating And we had to: 👉 select movies with **odd IDs** 👉 remove movies where description = **"boring"** 👉 sort the final result by **rating (highest first)** --- ### 😵💫 Where I got stuck: The condition for odd IDs was: `id % 2 = 1` I didn’t understand: 👉 why we are dividing by 2 👉 and what `%` actually does --- ### 🧠 What I understood: `%` is the **modulo operator**, which gives the remainder after division When we divide numbers by 2: * Even numbers → remainder = 0 * Odd numbers → remainder = 1 👉 So: `id % 2 = 1` means selecting **only odd IDs** --- ### ⚙️ Approach: * Used `% 2 = 1` to filter odd IDs * Removed rows where description = 'boring' * Sorted results using `ORDER BY rating DESC` --- ### 🧠 What I learned today: * `%` is not something to memorize, it’s a **logic to identify patterns** * Using 2 helps check even/odd, but `%` can be used with any number * Even simple questions can teach small but important concepts --- Today’s problem was easy, but it helped me understand the logic behind something I was just applying before. Learning step by step… 💪 #Day9 #SQL #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

🚀 Day 31 & 32 – SQL Journey: From CTEs to Recursive Queries Over the last two days, I explored how to make SQL queries more structured, readable, and powerful using CTEs and Recursive CTEs. 🔹 What I Learned: 📌 CTEs (Common Table Expressions) • Temporary result sets created using the "WITH" clause • Help break complex queries into simple, step-by-step logic • Improve readability and make queries easier to debug • Replace deeply nested subqueries 📌 Recursive CTEs & Hierarchical Queries • Built using Anchor + Recursive part • Execute repeatedly until a condition is met • Useful for working with structured data like trees and sequences 📌 Hierarchy Concepts Practiced: • START WITH • CONNECT BY PRIOR • LEVEL • SYS_CONNECT_BY_PATH • CONNECT_BY_ROOT 🔹 Hands-on Practice: ✔️ Calculated aggregated results step-by-step using CTEs ✔️ Generated numbers from 1 to N using recursion ✔️ Identified missing values in sequences 🔹 What Changed: Earlier → Writing queries Now → Structuring logic + understanding execution flow step-by-step 💡 Key Insight: CTEs make SQL clean and modular, while recursive queries unlock the ability to work with hierarchical data and patterns — something very common in real-world scenarios. 🔥 Takeaway: Better structure → Better readability → Better problem solving 📈 Learning step by step 🚀 #SQL #CTE #RecursiveCTE #DataAnalytics #LearnSQL #SQLJourney

💡 One SQL Lesson That Changed My Approach Recently, I realized that writing a query is easy… but writing an efficient query is the real skill. While working on a large dataset, I noticed performance issues. After optimizing indexes and rewriting joins, the query execution time dropped significantly 🚀 👉 Key takeaway: Always think beyond “working code” — focus on performance and scalability. #SQL #DataEngineering #Learning #CareerGrowth

🧠 SQL Query Execution Order (The part that confuses EVERYONE 😵💫) You write SQL like this 👇 👉 SELECT → FROM → WHERE → GROUP BY… But SQL actually runs like this 👇 🔥 REAL Execution Order: 1️⃣ FROM / JOIN 2️⃣ WHERE 3️⃣ GROUP BY 4️⃣ HAVING 5️⃣ SELECT 6️⃣ DISTINCT 7️⃣ ORDER BY 8️⃣ LIMIT 💀 Plot twist: SQL reads your query like… “Nice order bro… I’ll do it my way.” ⚠️ Why people mess up: ❌ Using alias in WHERE ❌ Confusing HAVING vs WHERE ❌ Not understanding grouping 💡 Easy Trick: 👉 “F-W-G-H-S-D-O-L” (Yeah… sounds like a WiFi password 😂) 🎯 Pro Insight: WHERE = filter BEFORE grouping HAVING = filter AFTER grouping 🚀 If you understand this → Half of SQL problems become EASY 👉 Save this (you’ll forget it tomorrow 😭) 👉 Share with your coding friend 👉 Follow for more SQL hacks #SQL #DataAnalytics #Coding #InterviewPrep #LearnSQL #TechSkills #Programming

📝 Deep Dive into SQL Fundamentals. Data is only as good as the way you manage it. I’ve been documenting the core principles of Structure Query Language (SQL)—from basic syntax to advanced database relationships. My latest notes cover: 🔹 CURD Operations (Create, Update, READ, Drop). 🔹 Relational Keys (Primary vs. Foreign). 🔹 Logical Operators for precise data retrieval. Always learning, always growing. 📈 #SoftwareEngineering #SQL #Database #Programming #Notes #LearningJourney

🚀 Day 5/10 — Optimization Series SQL Performance Tips (Write Faster Queries) 👉 Basics are done. 👉 Now we move from working code → optimized code. You write a query… It gives correct results… But it’s slow on large data 😐 👉 That’s where optimization matters. 🔹 1. Avoid SELECT * SELECT * FROM employees; ❌ SELECT name, salary FROM employees; ✅ 👉 Fetch only what you need 🔹 2. Filter Early SELECT * FROM employees WHERE department = 'IT'; 👉 Reduces data before processing 🔹 3. Use Proper Indexes 👉 Index frequently used columns 👉 Improves query speed 🔹 4. Avoid Unnecessary Joins 👉 Join only required tables 👉 Extra joins = extra cost 🔹 5. Limit Data SELECT * FROM employees LIMIT 10; 👉 Useful for testing & performance 🔹 Why This Matters Faster execution Reduced resource usage Scalable queries 🔹 Real Insight 👉 SQL performance is not just about correctness 👉 It’s about efficiency 💡 Quick Summary Small changes → big performance impact 💡 Something to remember A correct query gives results… An optimized query gives results faster. #SQL #Python #DataEngineering #LearningInPublic #TechLearning

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