SQL Self Join, AVG, Group By Challenge on LeetCode

SQL Progress: Logic & CASE Statements! Today I solved another Medium challenge on LeetCode. This problem was a great lesson in how to calculate percentages and rates directly in SQL. What I learned today: 1. AVG with CASE WHEN: I learned that I can use AVG(CASE WHEN condition THEN 1.0 ELSE 0.0 END) to calculate a rate. It’s a very clear way. 2. Handling NULLs in Rates: By using a LEFT JOIN between the Signups and Confirmations tables, I ensured that users with no actions are still included, and the AVG function automatically treats them as 0 if they don't meet the "confirmed" criteria. 3. Precision with ROUND: Used ROUND(..., 2) to make sure the final confirmation rate is clean and meets the required format(0.00). I would love to learn from your experience: is ther another methods cleaner? قليل مستمر خير من كثير منقطع #SQL #DataEngineering #PostgreSQL #LeetCode #100DaysOfCode #DataAnalytics #ProblemSolving

Day 5/50 – #SQLChallenge 🚀 Solved “Invalid Tweets” problem on LeetCode. ✅ Approach: Used LENGTH() to filter tweets exceeding character limits ✅ Key Concept: Data validation using string functions 💡 Advanced Insight: While LENGTH() works well here, it’s important to note that different databases handle string length differently (e.g., LENGTH vs CHAR_LENGTH in MySQL). Choosing the right function ensures accurate results, especially with multi-byte characters. 🔍 Takeaway: Writing queries isn’t just about solving the problem — it’s about understanding how functions behave across real-world scenarios. Consistency over intensity 💪 #SQL #LeetCode #Database #CodingChallenge #ProblemSolving #LearningInPublic #DeveloperJourney #TechGrowth

I just shipped something I'm really proud of. 🚀 Semicolon — an open-source SQL formatter that turns messy, unreadable queries into clean, structured code in seconds. You don’t need to decode a wall of SQL just to find where the JOIN stops and the WHERE starts. You don’t need to spend minutes formatting it perfectly. SemiColon handles it for you instantly. Just install it, point it at your SQL, and you’re done. → pip install semicolonfmt → semicolon query.sql (format a file) → semicolon . (format everything in a directory) What it does: ✅ Formats messy SQL into clean, consistent, scannable queries ✅ Works on single files or entire directories ✅ CI/CD check mode so unformatted SQL never slips into prod ✅ Pre-commit hook support ✅ Zero config. Just run it. It's open source, it's free, and it's just getting started. ⭐ If you like it, give it a star on GitHub 🔧 Test it, push it to the limits, and open a PR if you spot something off 🔗 https://lnkd.in/dmYG-t4c Clean SQL is not a nice-to-have. It's a craft. Let's treat it like one. 💪 #OpenSource #SQL #PostgreSQL #Python #DevTools #BuildingInPublic #CleanCode

‼️ The JOIN nobody teaches you in tutorials: LATERAL JOIN ‼️ Every SQL tutorial teaches you INNER JOIN and LEFT JOIN. Nobody talks about LATERAL JOIN 🤯 I stumbled across it while trying to solve a problem that was turning my subqueries into a mess. Here’s what it does and when you actually need it 👇 ――― 🔍 The problem it solves Imagine a customers table and an interactions table. You want the last 3 interactions per customer — not just the latest one. Most people try something like this 👇 ❌ THE MESSY WAY SELECT c.customer_id, (SELECT event_type FROM interactions i WHERE i.customer_id = c.customer_id ORDER BY created_at DESC LIMIT 1) AS last_event FROM customers c; This works for 1 row. But getting the last 3? You’d need 3 subqueries 😵 It breaks down fast. ――― ⚡ LATERAL JOIN to the rescue ✅ CLEAN APPROACH SELECT c.customer_id, i.event_type, i.created_at FROM customers c JOIN LATERAL ( SELECT event_type, created_at FROM interactions WHERE customer_id = c.customer_id ORDER BY created_at DESC LIMIT 3 ) i ON true; 💡 LATERAL means: For each row in customers, run this subquery using that row’s values. 👉 It’s basically a for-loop inside SQL ――― 🚀 Why this matters at scale In real-world systems, this pattern shows up everywhere: • Last N orders per user 📦 • Top 5 products per category 🛒 • Recent activity per account 📊 👉 LATERAL JOIN handles all of them cleanly No repeated subqueries ❌ No messy self-joins ❌ No Python post-processing ❌ ――― 🧠 Where it works ✔ PostgreSQL ✔ BigQuery (as CROSS JOIN LATERAL) ✔ Redshift ✔ Most modern databases ――― 🔥 Day 2 done. 28 to go. #SQL #LearningInPublic #DataEngineering #SQLTips #TechLearning #CareerGrowth #30DayChallenge #PostgreSQL #AdvancedSQL #DataAnalytics

This week was all about going beyond just “learning SQL”. I didn’t just study PostgreSQL — I actually built with it. Here’s what I worked on: 𝗖𝗼𝗿𝗲 𝗰𝗼𝗻𝗰𝗲𝗽𝘁𝘀 • Joins (INNER, LEFT, RIGHT, FULL) • Indexing & query optimization • Transactions & ACID properties 𝗔𝗱𝘃𝗮𝗻𝗰𝗲𝗱 𝗦𝗤𝗟 • CTEs (including recursive) • Window functions (ROW_NUMBER, RANK, LAG) • CASE, COALESCE, ROLLUP Most importantly — applied learning 𝗜 𝗱𝗲𝘀𝗶𝗴𝗻𝗲𝗱 𝗿𝗲𝗮𝗹-𝘄𝗼𝗿𝗹𝗱 𝗱𝗮𝘁𝗮𝗯𝗮𝘀𝗲 𝘀𝘆𝘀𝘁𝗲𝗺𝘀: • 𝗜𝗻𝘀𝘁𝗮𝗴𝗿𝗮𝗺 𝗧𝗵𝗿𝗶𝗳𝘁 𝗦𝘁𝗼𝗿𝗲 𝗗𝗮𝘁𝗮𝗯𝗮𝘀𝗲 • 𝗙𝗶𝘁𝗻𝗲𝘀𝘀 𝗖𝗼𝗮𝗰𝗵𝗶𝗻𝗴 𝗣𝗹𝗮𝘁𝗳𝗼𝗿𝗺 𝗗𝗮𝘁𝗮𝗯𝗮𝘀𝗲 Worked on: • Table relationships (1-1, 1-M, M-M) • Foreign keys & constraints • Structuring data like real applications Big realization: SQL isn’t just about writing queries — it’s about thinking like a system designer. Still a long way to go, but this week felt like a solid step forward. You can also check the two DB designs in my 𝗚𝗶𝘁𝗛𝘂𝗯 𝗿𝗲𝗽𝗼: https://lnkd.in/gHsgtx4W Would love feedback on my DB designs. Thanks Hitesh Choudhary Piyush Garg Akash Kadlag Jay Kadlag Suraj Kumar Jha Chai Aur Code #SQL #PostgreSQL #DatabaseDesign #BackendDevelopment #LearningInPublic

Just earned the SQL 50 badge on LeetCode 🎯 Honestly, if you’re starting with SQL - this is all you need. These 50 problems cover almost every important concept, from basics to advanced queries. Big takeaway? 👉 Window Functions are a game changer. Once you get them, a lot of “complex” problems become straightforward. If you’re preparing for interviews or strengthening your backend/data skills, I highly recommend going through this set. 📌 Want MySQL notes covering everything from basic to advanced? Check out my repository: https://lnkd.in/g8CTyMKC Consistency > Everything. #SQL #LeetCode #DataStructures #CodingJourney #BackendDevelopment #Learning #Tech

My updates on DB(SQL) Learning this week! 'Foreign key' is a column in one table which relate to another table's 'primary key'. Managing the data using CASCADE(deletion upto connected references), SET NULL(soft deletion) and RESTRICT(no deletion at all). Learned and understood INNER , LEFT and FULL OUTER JOIN how they are useful based on the requirements. Then I come to know about 'EXPLAIN ANALYZE' diagnostic tool shows realtime statistics! further deep dive into INDEXING how we create index and how its reduce the query execution time to 'B-Tree , O(log n)' and something 'non-key value index' which store the value on its leaf level to reduce the lookup ! Next, I learnt about the Transactions which include Begin , Update , Commit or Rollback and got to know about the 'dirty read' is basically showing the values before commit which is not good! PostgreSQL don't have 'dirty read'. Finally Learnt ACID compliance in databases which has 4 things , ATOMICITY means its neither half or fraction, it will be full else Rollback ie, transactions must be fully commit, then we have CONSISTENCY which mean transaction brings form one valid state to another. ISOLATION , concurrent transactions don't interface with each other and last is DURABILITY which means once transaction is Committed it remains permanently recored even in the event of system crash or power outage!!!! #PostgreSQL #SQL #Database

SQL Progress: Aggregations & Joins! What I learned and practiced today: 1. Aggregate Functions: It’s becoming easier to decide when to use GROUP BY and how to handle multiple aggregations in one query. 2. Data Precision: Handling decimal calculations and rounding is becoming second nature now. It’s all about making the final output clean and professional. I love to learn from you! If you have any tips or a better way to solve these, please type them in the comments! قليل مستمر خير من كثير منقطع #SQL #DataEngineering #PostgreSQL #LeetCode #100DaysOfCode #LearningInPublic #ProblemSolving #DataAnalytics

The most important engineering decision isn't which framework to use. It's your database schema. What I've learned building with PostgreSQL: 1. Design for the queries you'll actually run. Write your 10 most critical queries first. Then design the schema. 2. Normalise - but know when to stop. 3NF is great until you're doing 7-table JOINs for a simple dashboard. 3. Index what you filter and sort on. Every column in WHERE, ORDER BY, or JOIN on high-traffic tables needs an index. 4. Use UUIDs as primary keys for public-facing data. 5. Add created_at and updated_at to every table. Every. Single. Table. 6. Never delete data. Add a deleted_at column instead. Soft deletes are recoverable. What's the hardest schema decision you've had to make? Follow Sachin Shah - backend engineering decisions that matter in production. #PostgreSQL #BackendDevelopment #SystemDesign #SoftwareEngineering #BuildInPublic #SQL #TechTips

🚀 Just Published My New Blog on MySQL! As part of my Data Science journey, I recently worked on a blog explaining MySQL commands using flowcharts — and it really helped me understand concepts more clearly. Instead of memorizing queries, I focused on understanding the flow and purpose behind each SQL command category: 🔹 DDL – Structure 🔹 DML – Data operations 🔹 DCL – Access control 🔹 TCL – Transactions 🔹 DQL – Queries 📊 I also included a simple flowchart and real examples to make it beginner-friendly. Big thanks to my mentor Koduri Srihari and trainer Manohar Chary .V for their guidance and support throughout this learning process 🙌 Grateful to Innomatics Research Labs for providing such structured learning opportunities. 🔗 Read the full blog here: https://lnkd.in/gXVnDhYN Would love to hear your feedback! #MySQL #SQL #DataScience #LearningJourney #BeginnerFriendly #Database #Growth