Why CTEs are the most misunderstood SQL language | Nick Holt posted on the topic | LinkedIn (2025)

I think CTEs are the most misunderstood element of SQL.Recent posts I've seen here on this platform demonstrate a frightening lack of awareness about their function, affect on performance and overall usefulness.There’s an awful lot of rubbish spoken about CTEs both in LinkedIn and in articles.Some key headlines about CTEs:📌They exist to aid readability📌You can split up a long and complex query into small bitesize chunks📌There’s no practical limit to the number of CTEs you can have in a query📌CTEs enable easier troubleshooting of a query.📌They CAN help improve performance (as advised by Brent Ozar), but in most cases there’s little or no performance benefit.📌CTEs last only as long as the current query execution. 📌You can nest CTEs using a technique called ‘daisy-chaining’ where you refer to one CTE inside another. I wouldn’t recommend this personally.---------------------------------------------------------------------------About me:I help individuals learn SQL to advance their own careersI help businesses get their data/BI teams skilled up in SQLVisit my website and there you can book an appointment to speak to me about how I can help you---------------------------------------------------------------------------#sql#thebischool#learnsql#sqluk#CTEs

34

4 Comments

🚀 Unlocking the Power of SQL with CTEs! 🚀I recently used a Common Table Expression (CTE) to solve a tricky data analysis problem and wanted to share how helpful they can be. Here's a snippet of the query:-----------------------------------------------------------------------------------with cte as (select user_id,sum(case when activity_type = 'open' then time_spent else 0 end) as ot,sum(case when activity_type = 'send' then time_spent else 0 end) as stfrom activitiesgroup by user_id)select a.age_bucket , (c.st/(c.st+c.ot))*100.0 as send_perc, (c.ot/(c.st+c.ot))*100.0as open_percfrom cte cRight join age_breakdown aon c.user_id = a.user_id-------------------------------------------------------------------------------------🔍 Why CTEs Rock:- Clarity: Breaks complex queries into manageable chunks.- Reusability: Reference CTEs multiple times in your query.- Organization: Keeps your main query clean and focused.CTEs have made my SQL queries so much cleaner and easier to understand. If you haven't used them yet, give them a try!How have you used CTEs in your work? Share your experiences below!#DataScience #SQL #CTE #DataAnalytics #TechTips

4

1 Comment

🌟 𝗘𝗻𝗵𝗮𝗻𝗰𝗲 𝗬𝗼𝘂𝗿 𝗦𝗤𝗟 𝗦𝗸𝗶𝗹𝗹𝘀 𝘄𝗶𝘁𝗵 𝗮 𝗗𝗔𝗧𝗘-𝗳𝗼𝗰𝘂𝘀𝗲𝗱 𝗖𝗵𝗮𝗹𝗹𝗲𝗻𝗴𝗲! 🌟In the world of data, SQL stands as a cornerstone, enabling us to navigate through complex databases with ease. Today, I want to spotlight a unique aspect of SQL that often presents intriguing challenges: the 𝗗𝗔𝗧𝗘 𝗱𝗮𝘁𝗮𝘁𝘆𝗽𝗲.Handling dates in SQL can be tricky, yet it's crucial for generating insightful analyses and reports. To help you sharpen your skills in this area, I'm sharing 𝘁𝗵𝗿𝗲𝗲 𝗟𝗲𝗲𝘁𝗖𝗼𝗱𝗲 𝗰𝗵𝗮𝗹𝗹𝗲𝗻𝗴𝗲𝘀 that are perfect for anyone looking to delve deeper into SQL's capabilities with dates:𝗠𝗼𝗻𝘁𝗵𝗹𝘆 𝗧𝗿𝗮𝗻𝘀𝗮𝗰𝘁𝗶𝗼𝗻𝘀 𝗜 - A fantastic starting point to practice aggregating data over time. Check it out here : https://lnkd.in/gXhfZUEd𝗜𝗺𝗺𝗲𝗱𝗶𝗮𝘁𝗲 𝗙𝗼𝗼𝗱 𝗗𝗲𝗹𝗶𝘃𝗲𝗿𝘆 𝗜𝗜 - This challenge takes you a step further, asking you to calculate rates and percentages over specific time periods. Dive in here: https://lnkd.in/gtd9K2Ug𝗚𝗮𝗺𝗲 𝗣𝗹𝗮𝘆 𝗔𝗻𝗮𝗹𝘆𝘀𝗶𝘀 𝗜𝗩 - A more advanced challenge that tests your ability to perform complex analyses over different time frames. Give it a go here: https://lnkd.in/gdGWUE_pWhy not take this opportunity to challenge yourself and perhaps even a colleague? Let's share our solutions and learning experiences. Drop your thoughts, tips, or any questions you might have in the comments below. Let's grow together in our SQL journey!𝗛𝗮𝗽𝗽𝘆 𝗤𝘂𝗲𝗿𝘆𝗶𝗻𝗴! 🚀#SQL #DataAnalysis #CodingChallenge #LeetCode #ProfessionalDevelopment #ContinuousLearning

42

3 Comments

Once you've learned/mastered the fundamentals of SQL, try learning these:- 𝐉𝐎𝐈𝐍𝐬: LEFT, RIGHT, INNER, OUTER joins.- 𝐀𝐠𝐠𝐫𝐞𝐠𝐚𝐭𝐞 𝐅𝐮𝐧𝐜𝐭𝐢𝐨𝐧𝐬: Utilize SUM, COUNT, AVG, and others for efficient data summarization.- 𝐂𝐀𝐒𝐄 𝐖𝐇𝐄𝐍 𝐒𝐭𝐚𝐭𝐞𝐦𝐞𝐧𝐭𝐬: Use conditional logic to tailor query results.- 𝐃𝐚𝐭𝐞 𝐓𝐢𝐦𝐞 𝐅𝐮𝐧𝐜𝐭𝐢𝐨𝐧𝐬: Master manipulating dates and times for precise analysis.Next, explore advanced methods to structure and reuse SQL code effectively:- 𝐂𝐨𝐦𝐦𝐨𝐧 𝐓𝐚𝐛𝐥𝐞 𝐄𝐱𝐩𝐫𝐞𝐬𝐬𝐢𝐨𝐧𝐬 (𝐂𝐓𝐄𝐬): Simplify complex queries into manageable parts to increase the readability.- 𝐒𝐮𝐛𝐪𝐮𝐞𝐫𝐢𝐞𝐬: Nest queries for more granular data retrieval.- 𝐓𝐞𝐦𝐩𝐨𝐫𝐚𝐫𝐲 𝐓𝐚𝐛𝐥𝐞𝐬: Create and manipulate temporary data sets for specific tasks.Then, move on to advanced ones:- 𝐖𝐢𝐧𝐝𝐨𝐰 𝐅𝐮𝐧𝐜𝐭𝐢𝐨𝐧𝐬: Perform advanced calculations over sets of rows with ease.- 𝐒𝐭𝐨𝐫𝐞𝐝 𝐏𝐫𝐨𝐜𝐞𝐝𝐮𝐫𝐞𝐬: Create reusable SQL routines for streamlined operations.- 𝐓𝐫𝐢𝐠𝐠𝐞𝐫𝐬: Automate database actions based on specific events.- 𝐑𝐞𝐜𝐮𝐫𝐬𝐢𝐯𝐞 𝐂𝐓𝐄𝐬: Solve complex problems using recursive queries.- 𝐎𝐩𝐭𝐢𝐦𝐢𝐳𝐚𝐭𝐢𝐨𝐧 𝐨𝐟 𝐐𝐮𝐞𝐫𝐢𝐞𝐬: Techniques to enhance performance and efficiency.What other essential SQL skills would you add to this learning journey? 𝐎𝐧𝐜𝐞 𝐲𝐨𝐮 𝐥𝐞𝐚𝐫𝐧 𝐨𝐫 𝐚𝐬 𝐲𝐨𝐮 𝐥𝐞𝐚𝐫𝐧, 𝐩𝐫𝐚𝐜𝐭𝐢𝐜𝐞 𝐒𝐐𝐋 𝐡𝐞𝐫𝐞:1. Dataford - https://lnkd.in/dau5UwmN2. InterviewQuery - https://lnkd.in/dzJET9aC3. LeetCode - https://lnkd.in/gkCpv7NA4. HackerRank - https://lnkd.in/exe3kXzD#sql #dataanalytics #interviewprep

325

54 Comments

Hey everyone! After a break from SQL, I've recently jumped back into the world of data, and it’s like coming home!But, as with any return, I’ve stumbled upon a few bumps along the way.Here are some common mistakes I’ve caught myself making: ❌ Syntax Errors: Missing semicolons or using wrong keywords.❌ JOIN Mistakes: Mixing up INNER JOIN and OUTER JOIN or joining on incorrect columns.❌ GROUP BY Problems: Not using aggregate functions properly. ❌ NULL Handling: Forgetting to use IS NULL or IS NOT NULL. ❌ SELECT * Overuse: Slowing down queries by selecting unnecessary data.❌ Ignoring Indexes: Not adding indexes for faster querying.It’s been a learning curve, but every mistake is a chance to improve.When I first returned to SQL, I found that while I remembered the core concepts, some syntax and details had slipped my mind.I also had to readdress my tendency to overuse SELECT *, which was impacting query performance.To get back on track, here’s what helped me:Revisit the Basics: Refreshing my memory with tutorials and documentation.Practice Regularly: Working on small exercises and sample databases.Use a Cheat Sheet: Keeping a reference guide handy for quick reminders.Write and Review Code: Practicing queries and reviewing them to spot mistakes.It’s all about patience and persistence. Each mistake is a stepping stone towards mastery.How do you handle jumping back into skills after a break? Let’s share tips and support each other!#SQL #SQLMistakes #Linkedin

26

2 Comments

Here's how you can create dynamic projections in your reporting. We'll use SQL for the example, but the same logic can be applied to whatever tool your using.Here's the code from the video so you can copy it down and follow along on your own: -- number of days in current monthSELECT DAY(LAST_DAY(NOW()))-- ,NOW()-- ,LAST_DAY(NOW());-- first day of current month SELECT DATE_FORMAT(NOW(),'%Y-%m-01') AS first_day;-- seconds into this month so farSELECT TIMESTAMPDIFF(SECOND, DATE_FORMAT(NOW(),'%Y-%m-01'), NOW()) AS secs;-- total seconds in this month SELECT DAY(LAST_DAY(NOW())) * 86400; SELECT DAY(LAST_DAY(NOW())) * 86400 / TIMESTAMPDIFF(SECOND, DATE_FORMAT(NOW(),'%Y-%m-01'), NOW())AS total_to_current_ratio , TIMESTAMPDIFF(SECOND, DATE_FORMAT(NOW(),'%Y-%m-01'), NOW()) / (DAY(LAST_DAY(NOW())) * 86400)AS pct_through_monthFor folks who are interesting in improving their SQL skills, check out our MySQL Specialist Path or our individual courses. MYSQL SPECIALIST LEARNING PATH: https://lnkd.in/eTpUrqwZ#learning #sql #data #analysis #datascience #businessintelligence #careers

44

2 Comments

Here's how you can create dynamic projections in your reporting. We'll use SQL for the example, but the same logic can be applied to whatever tool your using.Here's the code from the video so you can copy it down and follow along on your own: -- number of days in current monthSELECT DAY(LAST_DAY(NOW()))-- ,NOW()-- ,LAST_DAY(NOW());-- first day of current month SELECT DATE_FORMAT(NOW(),'%Y-%m-01') AS first_day;-- seconds into this month so farSELECT TIMESTAMPDIFF(SECOND, DATE_FORMAT(NOW(),'%Y-%m-01'), NOW()) AS secs;-- total seconds in this month SELECT DAY(LAST_DAY(NOW())) * 86400; SELECT DAY(LAST_DAY(NOW())) * 86400 / TIMESTAMPDIFF(SECOND, DATE_FORMAT(NOW(),'%Y-%m-01'), NOW())AS total_to_current_ratio , TIMESTAMPDIFF(SECOND, DATE_FORMAT(NOW(),'%Y-%m-01'), NOW()) / (DAY(LAST_DAY(NOW())) * 86400)AS pct_through_monthFor folks who are interesting in improving their SQL skills, check out our MySQL Specialist Path or our individual courses. MYSQL SPECIALIST LEARNING PATH: https://lnkd.in/eTpUrqwZ#learning #sql #data #analysis #datascience #businessintelligence #careers

79

4 Comments

💡 Heres a tip by John Pauler if you would love to learn 'how to add dynamic projections to your reporting ' . ⚡ Dynamic reporting dynamically updates reports in response to changes in underlying data sources. ⚡ We can discover how to calculate an estimated projection for the end of the month, like May, dynamically for any Key Performance Indicator (KPI).#dataanalyst #reporting #sqlqueries

12

2 Comments

While working on a SQL problem this morning, I reflected on how my problem-solving approach has significantly evolved over time. It’s fascinating to observe how practice has refined my thinking process compared to when I first began.Now, when faced with a SQL challenge, I’ve discovered that taking a thoughtful and deliberate approach yields far better results than rushing to a solution. I start by thoroughly analyzing the data—reviewing its attributes and key information to gain a comprehensive understanding of its structure.Once I have a clear grasp of the data, I carefully read the problem statement to ensure I fully comprehend what is being asked. Before diving into writing queries, I take a moment to draft a step-by-step plan. This strategy is particularly useful for tackling complex problems that involve subqueries, joins, or regular expressions.By investing time in planning my solution, I find that I can approach problems more efficiently and accurately.How do you approach SQL challenges? comment below 👇

1

🚀 Day 7 of My SQL Learning Journey: Unlocking the Power of LIKE Queries! 🚀Today, I explored the versatility of LIKE queries in SQL, which are essential for pattern matching and flexible data retrieval. 🔍🔑Here’s what I accomplished on Day 7:- 📚 Learned how to use the LIKE keyword to search for patterns within text data.- 🧩 Practiced using wildcards (%) and (_) to create flexible search conditions.- 🛠️ Combined LIKE with SELECT, WHERE, and other SQL clauses for dynamic queries.- 🌟 Explored practical examples, such as finding all records with a certain prefix, suffix, or substring.LIKE queries are incredibly powerful for filtering and extracting data based on specific patterns, making them indispensable for data analysis and reporting.I’m excited to continue my SQL journey and apply these techniques to real-world scenarios. Your support and insights have been invaluable!If you have any tips or interesting use cases for LIKE queries, I’d love to hear them. Let’s keep pushing the boundaries of what we can do with SQL! 🚀#SQL #DataManagement #LearningJourney #ProfessionalDevelopment #Day7 #DataAnalytics #LikeQueries

16