Choosing Between CTEs and Subqueries in SQL

📊 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

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

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

CTEs vs. Subqueries (Clean Your Code!) ✍️ Title: Stop writing "Inception" style SQL queries. We’ve all seen them—queries nested inside queries, inside more queries. They are hard to read, impossible to debug, and a nightmare for your teammates. The Solution? Common Table Expressions (CTEs). The Old Way (Subqueries): It’s like reading a book from the middle. You have to jump to the innermost brackets to understand what's happening. The Better Way (CTEs): Using the WITH clause allows you to write your logic from top to bottom. It breaks your complex logic into small, named "steps." Why I prefer CTEs: Readability: It reads like a story. Step A, then Step B, then the Final Result. Relabability: You can reference the same CTE multiple times in your main query. Debugging: You can comment out parts of the query easily to test each step. Are you Team CTE or Team Subquery? Let’s settle the debate below! 👇 #SQL #CleanCode #DataEngineering #ProgrammingTips #DataAnalytics #SoftwareDevelopment

Stop writing "Spaghetti SQL" 🍝.... Everyone has opened a SQL script and finded a mountain of nested subqueries that make you ill. In that cenario changes become risky, and debugging feels like a nightmare. That’s why CTEs (Common Table Expressions) come in to save everyones sanity. As shown in the infographic, a CTE allows you to define a temporary result set that you can reference within your main query. I think of it as creating a "lable" for your data. Here is some reasons for use CTEs in every complex pipeline: ✅ Readability: It breaks down complex logic into sequential, logical steps. You read it from top to bottom, like a story. ✅ Reusability: Define a complex calculation once and reference it multiple times in the same query. No more Copy-Paste! ✅ Maintainability: Need to update the logic? You only change it in one place (the WITH clause), and it propagates everywhere. ✅ Debugging: You can quickly test individual blocks of your query before running the whole thing. How has using CTEs changed the way you write SQL? Let’s discuss in the comments! 👇 #SQL #DataEngineering #Analytics #DataScience #CodingTips #Database #CleanCode #TechCommunity #DataAnalytics

sqlc is the perfect abstraction. I've been writing a go backend api for a life planning app called "plannkit" — sqlc has completely changed how I think about database access. 🚀 The idea is simple but brilliant: you write plain SQL, and sqlc generates type-safe Go code from it. That's it. No ORM magic, no reflection, no runtime surprises. Here's what I love about it: ✅ Your queries are real SQL — readable, debuggable, and portable ✅ Compile-time guarantees — if your query is wrong, you know before it hits production ✅ Zero runtime overhead — generated code is just plain Go structs and functions I used to dread adding a new database query — migration, struct, scan boilerplate, null handling... now I write the SQL, run sqlc generate, and I'm done. It's genuinely joyful. Your "repository" layer and types are "automated" If you're building Go services and you're still hand-writing rows.Scan(...) everywhere — do yourself a favour and try sqlc this week. Your future self will thank you. #golang #go #sql #sqlc #backend #softwaredevelopment #databases #productivity

I reduced an API response from ~3.8s to ~40ms without changing application code. The fix? One composite index. Here's the indexing strategy most backend developers skip: 𝗪𝗵𝗮𝘁 𝗮𝗰𝘁𝘂𝗮𝗹𝗹𝘆 𝘀𝗹𝗼𝘄𝘀 𝘆𝗼𝘂𝗿 𝗗𝗕: → Full table scans on WHERE clauses   → Missing indexes on JOIN columns   → Selecting * instead of specific columns   → N+1 queries masquerading as "features"  𝗧𝗵𝗲 𝗰𝗼𝗺𝗽𝗼𝘀𝗶𝘁𝗲 𝗶𝗻𝗱𝗲𝘅 𝗿𝘂𝗹𝗲 𝗻𝗼𝗯𝗼𝗱𝘆 𝘁𝗲𝗮𝗰𝗵𝗲𝘀: Index column order matters. Put the most selective column first (highest cardinality)   AND match the index order with your WHERE clause. Example:   WHERE user_id = ? AND status = ?   → index on (user_id, status)  NOT (status, user_id) Bonus: If your query has ORDER BY, include it in the index to avoid an extra sort step. 𝗪𝗵𝗲𝗻 𝗡𝗢𝗧 𝘁𝗼 𝗶𝗻𝗱𝗲𝘅: → Small tables (often <10k rows)   → Write-heavy tables   → Low cardinality columns (boolean, status with few values)  𝗛𝗲𝗿𝗲’𝘀 𝗲𝘅𝗮𝗰𝘁𝗹𝘆 𝗵𝗼𝘄 𝗜 𝗱𝗲𝗯𝘂𝗴𝗴𝗲𝗱 𝗶𝘁: 1. Run EXPLAIN ANALYZE on your slowest queries   2. Look for "Seq Scan" or high "Rows Removed by Filter"   3. Add indexes strategically — not blindly  Performance is free. You just have to know where to look. #Backend #Database #PostgreSQL #Performance #SQL #SoftwareEngineering

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

🚀 High Performance SQL Query on LeetCode! Just solved “Customer Placing the Largest Number of Orders” and this one felt extra satisfying 😄 📊 Result: ✔️ Accepted ✅ (19/19 test cases passed) ✔️ Runtime: 427 ms ⚡ ✔️ Beats ~80% of submissions 🚀 💡 Problem Insight: The task was to find the customer who placed the maximum number of orders — a great use case of GROUP BY + aggregation + subquery. 🧠 My Approach: Counted orders per customer Compared it with the maximum order count using a subquery Returned the customer with the highest count SELECT customer_number FROM Orders GROUP BY customer_number HAVING COUNT(order_number) = ( SELECT MAX(order_count) FROM ( SELECT COUNT(order_number) AS order_count FROM Orders GROUP BY customer_number ) AS temp ); 🔥 Key Takeaway: Combining aggregation + nested queries helps solve ranking-type problems efficiently. Consistency is paying off — step by step improving both logic and performance 💪 #SQL #LeetCode #CodingJourney #Database #Learning #Tech #PlacementPreparation #Consistency

Day 29 & 30 of My SQL Learning Journey 🚀 Over the last two days, I focused on subqueries based on location. 🔹 SELECT Subquery Used when the subquery returns a single value that can be displayed in the output. 🔹 WHERE Subquery Used to filter records based on conditions from another query. 🔹 FROM Subquery Used when the result of one query is treated as a temporary table for the main query. I also practiced solving SQL LeetCode problems to strengthen my understanding: • LeetCode 185 – Department Top Three Salaries Learned how to use dense ranking and partitions to find the top salaries in each department. • LeetCode 1341 – Movie Rating Worked on joining multiple tables and using aggregate functions to identify top users and highest-rated movies. • LeetCode 602 – Friend Requests II: Who Has the Most Friends Understood how to combine sender and receiver data to count total friendships. • LeetCode 1978 – Employees Whose Manager Left the Company Learned to use subqueries and conditions to identify employees whose managers are no longer in the company. These problems helped me improve my understanding of joins, aggregate functions, ranking functions, and subqueries in real-time scenarios. #SQL #MySQL #LeetCode #DataAnalytics #SQLLearning #Subqueries #LearningJourney #DatabaseManagement #AspiringDataAnalyst