{"id":4317,"date":"2024-10-23T07:03:53","date_gmt":"2024-10-22T23:03:53","guid":{"rendered":"https:\/\/five.co\/?p=4317"},"modified":"2024-10-23T07:03:55","modified_gmt":"2024-10-22T23:03:55","slug":"sql-server-with-clause","status":"publish","type":"post","link":"https:\/\/preview-php85.dzine.org\/five\/blog\/sql-server-with-clause\/","title":{"rendered":"SQL Server WITH Clause: Complete Guide"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Breakdown of SQL Server WITH Clause<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The WITH clause in SQL Server (also known as Common Table Expressions or CTEs) lets you break down complex queries into readable, manageable chunks. Instead of nesting multiple subqueries, you can write them as separate, named pieces. Let&#8217;s see how to use them effectively.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n<div class=\"root-eb-toc-ymqxo wp-block-essential-blocks-table-of-contents\"><div class=\"eb-parent-wrapper eb-parent-eb-toc-ymqxo \"><div class=\"eb-toc-container eb-toc-ymqxo  eb-toc-is-not-sticky eb-toc-not-collapsible eb-toc-initially-not-collapsed eb-toc-scrollToTop style-1 list-style-none\" data-scroll-top=\"false\" data-scroll-top-icon=\"fas fa-angle-up\" data-collapsible=\"false\" data-sticky-hide-mobile=\"false\" data-sticky=\"false\" data-scroll-target=\"scroll_to_toc\" data-copy-link=\"false\" data-editor-type=\"\" data-hide-desktop=\"false\" data-hide-tab=\"false\" data-hide-mobile=\"false\" data-itemcollapsed=\"false\" data-highlight-scroll=\"false\"><div class=\"eb-toc-header\"><h2 class=\"eb-toc-title\">Table of Contents<\/h2><\/div><div class=\"eb-toc-wrapper \" data-headers=\"[{&quot;level&quot;:2,&quot;content&quot;:&quot;Breakdown of SQL Server WITH Clause&quot;,&quot;text&quot;:&quot;Breakdown of SQL Server WITH Clause&quot;,&quot;link&quot;:&quot;breakdown-of-sql-server-with-clause&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Basic WITH Clause Structure&quot;,&quot;text&quot;:&quot;Basic WITH Clause Structure&quot;,&quot;link&quot;:&quot;basic-with-clause-structure&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Real-World Applications&quot;,&quot;text&quot;:&quot;Real-World Applications&quot;,&quot;link&quot;:&quot;real-world-applications&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;1. Finding Top Performers by Department&quot;,&quot;text&quot;:&quot;1. Finding Top Performers by Department&quot;,&quot;link&quot;:&quot;1-finding-top-performers-by-department&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;2. Sales Analysis with Running Totals&quot;,&quot;text&quot;:&quot;2. Sales Analysis with Running Totals&quot;,&quot;link&quot;:&quot;2-sales-analysis-with-running-totals&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;3. Customer Segmentation&quot;,&quot;text&quot;:&quot;3. Customer Segmentation&quot;,&quot;link&quot;:&quot;3-customer-segmentation&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;4. Hierarchical Data Navigation&quot;,&quot;text&quot;:&quot;4. Hierarchical Data Navigation&quot;,&quot;link&quot;:&quot;4-hierarchical-data-navigation&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Advanced Techniques&quot;,&quot;text&quot;:&quot;Advanced Techniques&quot;,&quot;link&quot;:&quot;advanced-techniques&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;1. Using Multiple CTEs for Complex Analysis&quot;,&quot;text&quot;:&quot;1. Using Multiple CTEs for Complex Analysis&quot;,&quot;link&quot;:&quot;1-using-multiple-ctes-for-complex-analysis&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;2. Temporary Results for Complex Calculations&quot;,&quot;text&quot;:&quot;2. Temporary Results for Complex Calculations&quot;,&quot;link&quot;:&quot;2-temporary-results-for-complex-calculations&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Common Pitfalls and Solutions&quot;,&quot;text&quot;:&quot;Common Pitfalls and Solutions&quot;,&quot;link&quot;:&quot;common-pitfalls-and-solutions&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;1. Recursive CTE Infinite Loops&quot;,&quot;text&quot;:&quot;1. Recursive CTE Infinite Loops&quot;,&quot;link&quot;:&quot;1-recursive-cte-infinite-loops&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;2. Performance Considerations&quot;,&quot;text&quot;:&quot;2. Performance Considerations&quot;,&quot;link&quot;:&quot;2-performance-considerations&quot;}]\" data-visible=\"[true,true,true,true,true,true]\" data-delete-headers=\"[{&quot;label&quot;:&quot;Breakdown of SQL Server WITH Clause&quot;,&quot;value&quot;:&quot;breakdown-of-sql-server-with-clause&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Basic WITH Clause Structure&quot;,&quot;value&quot;:&quot;basic-with-clause-structure&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Real-World Applications&quot;,&quot;value&quot;:&quot;real-world-applications&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;1. Finding Top Performers by Department&quot;,&quot;value&quot;:&quot;1-finding-top-performers-by-department&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;2. Sales Analysis with Running Totals&quot;,&quot;value&quot;:&quot;2-sales-analysis-with-running-totals&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;3. Customer Segmentation&quot;,&quot;value&quot;:&quot;3-customer-segmentation&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;4. Hierarchical Data Navigation&quot;,&quot;value&quot;:&quot;4-hierarchical-data-navigation&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Advanced Techniques&quot;,&quot;value&quot;:&quot;advanced-techniques&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;1. Using Multiple CTEs for Complex Analysis&quot;,&quot;value&quot;:&quot;1-using-multiple-ctes-for-complex-analysis&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;2. Temporary Results for Complex Calculations&quot;,&quot;value&quot;:&quot;2-temporary-results-for-complex-calculations&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Common Pitfalls and Solutions&quot;,&quot;value&quot;:&quot;common-pitfalls-and-solutions&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;1. Recursive CTE Infinite Loops&quot;,&quot;value&quot;:&quot;1-recursive-cte-infinite-loops&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;2. Performance Considerations&quot;,&quot;value&quot;:&quot;2-performance-considerations&quot;,&quot;isDelete&quot;:true}]\" data-smooth=\"true\" data-top-offset=\"\"><div class=\"eb-toc__list-wrap\"><ul class=\"eb-toc__list\"><li><a href=\"#breakdown-of-sql-server-with-clause\">Breakdown of SQL Server WITH Clause<\/a><li><a href=\"#basic-with-clause-structure\">Basic WITH Clause Structure<\/a><li><a href=\"#real-world-applications\">Real-World Applications<\/a><li><a href=\"#advanced-techniques\">Advanced Techniques<\/a><li><a href=\"#common-pitfalls-and-solutions\">Common Pitfalls and Solutions<\/a><\/ul><\/div><\/div><\/div><\/div><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Basic WITH Clause Structure<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s the fundamental syntax:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"WITH CTE_Name AS (\n    SELECT columns\n    FROM table\n    WHERE conditions\n)\nSELECT *\nFROM CTE_Name;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> CTE_Name <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> columns<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">table<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> conditions<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> *<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> CTE_Name;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">A simple example:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"WITH SalesSummary AS (\n    SELECT \n        CustomerID,\n        COUNT(*) AS OrderCount,\n        SUM(TotalAmount) AS TotalSpent\n    FROM Orders\n    GROUP BY CustomerID\n)\nSELECT *\nFROM SalesSummary\nWHERE TotalSpent &gt; 1000;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> SalesSummary <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        CustomerID,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> OrderCount,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(TotalAmount) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> TotalSpent<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Orders<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> CustomerID<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> *<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> SalesSummary<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> TotalSpent <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1000<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Real-World Applications<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">1. Finding Top Performers by Department<\/h3>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"WITH DepartmentStats AS (\n    SELECT \n        DepartmentID,\n        AVG(Salary) AS AvgSalary\n    FROM Employees\n    GROUP BY DepartmentID\n),\nTopPerformers AS (\n    SELECT \n        e.EmployeeID,\n        e.Name,\n        e.DepartmentID,\n        e.Salary,\n        d.AvgSalary,\n        (e.Salary - d.AvgSalary) AS SalaryDifference\n    FROM Employees e\n    JOIN DepartmentStats d ON e.DepartmentID = d.DepartmentID\n    WHERE e.Salary &gt; d.AvgSalary\n)\nSELECT \n    Name,\n    Salary,\n    AvgSalary,\n    FORMAT(SalaryDifference, 'C') AS AboveAverage\nFROM TopPerformers\nORDER BY SalaryDifference DESC;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> DepartmentStats <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        DepartmentID,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">AVG<\/span><span style=\"color: #ABB2BF\">(Salary) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> AvgSalary<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Employees<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> DepartmentID<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">TopPerformers <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">EmployeeID<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">Name<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">DepartmentID<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">Salary<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">d<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">AvgSalary<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        (<\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">Salary<\/span><span style=\"color: #ABB2BF\"> - <\/span><span style=\"color: #D19A66\">d<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">AvgSalary<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> SalaryDifference<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Employees e<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">JOIN<\/span><span style=\"color: #ABB2BF\"> DepartmentStats d <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">DepartmentID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">d<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">DepartmentID<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">Salary<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">d<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">AvgSalary<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">Name<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    Salary,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    AvgSalary,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">FORMAT<\/span><span style=\"color: #ABB2BF\">(SalaryDifference, <\/span><span style=\"color: #98C379\">&#39;C&#39;<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> AboveAverage<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> TopPerformers<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> SalaryDifference <\/span><span style=\"color: #C678DD\">DESC<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h3 class=\"wp-block-heading\">2. Sales Analysis with Running Totals<\/h3>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"WITH MonthlySales AS (\n    SELECT \n        DATETRUNC('month', OrderDate) AS Month,\n        SUM(Amount) AS MonthlyTotal,\n        COUNT(*) AS OrderCount\n    FROM Orders\n    WHERE YEAR(OrderDate) = 2024\n    GROUP BY DATETRUNC('month', OrderDate)\n),\nRunningTotals AS (\n    SELECT \n        Month,\n        MonthlyTotal,\n        OrderCount,\n        SUM(MonthlyTotal) OVER (\n            ORDER BY Month\n        ) AS RunningTotal,\n        AVG(MonthlyTotal) OVER (\n            ORDER BY Month\n            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW\n        ) AS ThreeMonthAvg\n    FROM MonthlySales\n)\nSELECT \n    FORMAT(Month, 'MMM yyyy') AS MonthYear,\n    FORMAT(MonthlyTotal, 'C') AS Revenue,\n    OrderCount,\n    FORMAT(RunningTotal, 'C') AS YearToDate,\n    FORMAT(ThreeMonthAvg, 'C') AS QuarterlyAvg\nFROM RunningTotals\nORDER BY Month;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> MonthlySales <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">DATETRUNC<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #98C379\">&#39;month&#39;<\/span><span style=\"color: #ABB2BF\">, OrderDate) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">Month<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(Amount) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> MonthlyTotal,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> OrderCount<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Orders<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">YEAR<\/span><span style=\"color: #ABB2BF\">(OrderDate) <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">2024<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">DATETRUNC<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #98C379\">&#39;month&#39;<\/span><span style=\"color: #ABB2BF\">, OrderDate)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">RunningTotals <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">Month<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        MonthlyTotal,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        OrderCount,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(MonthlyTotal) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">Month<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        ) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> RunningTotal,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">AVG<\/span><span style=\"color: #ABB2BF\">(MonthlyTotal) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">Month<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">ROWS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BETWEEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">2<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PRECEDING<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> CURRENT <\/span><span style=\"color: #C678DD\">ROW<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        ) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> ThreeMonthAvg<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> MonthlySales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">FORMAT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">Month<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;MMM yyyy&#39;<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> MonthYear,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">FORMAT<\/span><span style=\"color: #ABB2BF\">(MonthlyTotal, <\/span><span style=\"color: #98C379\">&#39;C&#39;<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> Revenue,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    OrderCount,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">FORMAT<\/span><span style=\"color: #ABB2BF\">(RunningTotal, <\/span><span style=\"color: #98C379\">&#39;C&#39;<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> YearToDate,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">FORMAT<\/span><span style=\"color: #ABB2BF\">(ThreeMonthAvg, <\/span><span style=\"color: #98C379\">&#39;C&#39;<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> QuarterlyAvg<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> RunningTotals<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">Month<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h3 class=\"wp-block-heading\">3. Customer Segmentation<\/h3>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"WITH CustomerMetrics AS (\n    SELECT \n        CustomerID,\n        COUNT(*) AS OrderCount,\n        SUM(Amount) AS TotalSpent,\n        MAX(OrderDate) AS LastOrderDate,\n        DATEDIFF(DAY, MAX(OrderDate), GETDATE()) AS DaysSinceLastOrder\n    FROM Orders\n    GROUP BY CustomerID\n),\nSegments AS (\n    SELECT \n        CustomerID,\n        OrderCount,\n        TotalSpent,\n        LastOrderDate,\n        DaysSinceLastOrder,\n        CASE\n            WHEN OrderCount &gt;= 10 AND DaysSinceLastOrder <= 90 \n                THEN 'VIP'\n            WHEN OrderCount &gt;= 5 AND DaysSinceLastOrder <= 180 \n                THEN 'Regular'\n            WHEN DaysSinceLastOrder &gt; 365 \n                THEN 'Inactive'\n            ELSE 'Occasional'\n        END AS CustomerSegment\n    FROM CustomerMetrics\n)\nSELECT \n    CustomerSegment,\n    COUNT(*) AS CustomersInSegment,\n    FORMAT(AVG(TotalSpent), 'C') AS AvgSpentPerCustomer,\n    FORMAT(SUM(TotalSpent), 'C') AS TotalRevenue\nFROM Segments\nGROUP BY CustomerSegment\nORDER BY AVG(TotalSpent) DESC;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> CustomerMetrics <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        CustomerID,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> OrderCount,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(Amount) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> TotalSpent,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">MAX<\/span><span style=\"color: #ABB2BF\">(OrderDate) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> LastOrderDate,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">DATEDIFF<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">DAY<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #56B6C2\">MAX<\/span><span style=\"color: #ABB2BF\">(OrderDate), <\/span><span style=\"color: #56B6C2\">GETDATE<\/span><span style=\"color: #ABB2BF\">()) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> DaysSinceLastOrder<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Orders<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> CustomerID<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">Segments <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        CustomerID,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        OrderCount,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        TotalSpent,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        LastOrderDate,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        DaysSinceLastOrder,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">CASE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> OrderCount <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">10<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> DaysSinceLastOrder <\/span><span style=\"color: #56B6C2\">&lt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">90<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">                <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;VIP&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> OrderCount <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">5<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> DaysSinceLastOrder <\/span><span style=\"color: #56B6C2\">&lt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">180<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">                <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Regular&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> DaysSinceLastOrder <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">365<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">                <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Inactive&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">ELSE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Occasional&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> CustomerSegment<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> CustomerMetrics<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    CustomerSegment,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> CustomersInSegment,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">FORMAT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #56B6C2\">AVG<\/span><span style=\"color: #ABB2BF\">(TotalSpent), <\/span><span style=\"color: #98C379\">&#39;C&#39;<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> AvgSpentPerCustomer,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">FORMAT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(TotalSpent), <\/span><span style=\"color: #98C379\">&#39;C&#39;<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> TotalRevenue<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Segments<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> CustomerSegment<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">AVG<\/span><span style=\"color: #ABB2BF\">(TotalSpent) <\/span><span style=\"color: #C678DD\">DESC<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h3 class=\"wp-block-heading\">4. Hierarchical Data Navigation<\/h3>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"WITH EmployeeHierarchy AS (\n    -- Base case: employees with no manager\n    SELECT \n        EmployeeID,\n        Name,\n        ManagerID,\n        0 AS Level,\n        CAST(Name AS VARCHAR(1000)) AS Path\n    FROM Employees\n    WHERE ManagerID IS NULL\n\n    UNION ALL\n\n    -- Recursive case: employees and their managers\n    SELECT \n        e.EmployeeID,\n        e.Name,\n        e.ManagerID,\n        eh.Level + 1,\n        CAST(eh.Path + ' &gt; ' + e.Name AS VARCHAR(1000))\n    FROM Employees e\n    INNER JOIN EmployeeHierarchy eh \n        ON e.ManagerID = eh.EmployeeID\n)\nSELECT \n    Level,\n    Path,\n    (SELECT COUNT(*) - 1 \n     FROM STRING_SPLIT(Path, '&gt;')) AS ReportsCount\nFROM EmployeeHierarchy\nORDER BY Path;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> EmployeeHierarchy <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #7F848E; font-style: italic\">-- Base case: employees with no manager<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        EmployeeID,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">Name<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        ManagerID,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">0<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">Level<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">CAST<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">Name<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">1000<\/span><span style=\"color: #ABB2BF\">)) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">Path<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Employees<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> ManagerID <\/span><span style=\"color: #C678DD\">IS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NULL<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">UNION ALL<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #7F848E; font-style: italic\">-- Recursive case: employees and their managers<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">EmployeeID<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">Name<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">ManagerID<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">eh<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">Level<\/span><span style=\"color: #ABB2BF\"> + <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">CAST<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">eh<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">Path<\/span><span style=\"color: #ABB2BF\"> + <\/span><span style=\"color: #98C379\">&#39; &gt; &#39;<\/span><span style=\"color: #ABB2BF\"> + <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">Name<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">1000<\/span><span style=\"color: #ABB2BF\">))<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Employees e<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">INNER JOIN<\/span><span style=\"color: #ABB2BF\"> EmployeeHierarchy eh <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">ManagerID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">eh<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">EmployeeID<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">Level<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">Path<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    (<\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) - <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">     <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">STRING_SPLIT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">Path<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;&gt;&#39;<\/span><span style=\"color: #ABB2BF\">)) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> ReportsCount<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> EmployeeHierarchy<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">Path<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"has-text-align-center wp-block-paragraph\"><strong><mark style=\"background-color:#0693e3\" class=\"has-inline-color has-white-color\">Build a Web Application using only SQL<\/mark><\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Building an entire web app using only SQL is not only possible but can be incredibly efficient with the right tools. With Five, a rapid application development environment, you can create a fully responsive SQL app that runs on top of a MySQL database without needing any additional programming languages or frameworks.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/code-along\/how-to-build-a-sql-app\/\">The step-by-step tutorial available here<\/a> will guide you through creating:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>A MySQL-based database for your web app.<\/li>\n\n\n\n<li>A form-based user interface that dynamically interacts with the data.<\/li>\n\n\n\n<li>Interactive charts and comprehensive PDF reports directly from SQL queries.<\/li>\n\n\n\n<li>A secure login system, turning your SQL queries into a multiuser app.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Using Five, the development process is simplified. The platform automatically generates forms, charts, reports, and other front-end elements based on your SQL schema, meaning you can build an entire application with just SQL and Five\u2019s intuitive visual tools.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">By the end the tutorial available here &#8220;<a href=\"https:\/\/preview-php85.dzine.org\/five\/code-along\/how-to-build-a-sql-app\/\">How To Build a SQL App<\/a>&#8220;, you will have a fully functional web app that uses SQL to manage data, generate reports, and provide a dynamic user experience \u2014 all without writing any additional code outside SQL.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\/\">Get started with free access<\/a> to Five\u2019s development environment and begin building your SQL-powered web app today!<\/p>\n\n\n\n<div style=\"background-color: #001524;\"><hr style=\"height: 5px;\" \/>\n<pre style=\"text-align: center; overflow: hidden; white-space: pre-line;\"><span style=\"color: #f1ebda; background-color: #4588d8; font-size: calc(18px + 0.390625vw);\"><strong>Build Your SQL Web App In 3 Steps<\/strong><br \/><span style=\"font-size: 14pt;\">Start Developing Today<\/span><\/span><\/pre>\n<p style=\"text-align: center;\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\" target=\"_blank\" rel=\"noopener\"><button style=\"background-color: #f8b92b; border: none; color: black; padding: 20px; text-align: center; text-decoration: none; display: inline-block; font-size: 18px; cursor: pointer; margin: 4px 2px; border-radius: 5px;\"><strong>Get Instant Access<\/strong><\/button><br \/><\/a><\/p>\n<hr style=\"height: 5px;\" \/><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Advanced Techniques<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">1. Using Multiple CTEs for Complex Analysis<\/h3>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"WITH OrderStats AS (\n    SELECT \n        ProductID,\n        COUNT(*) AS TimesOrdered,\n        SUM(Quantity) AS TotalQuantity\n    FROM OrderDetails\n    GROUP BY ProductID\n),\nInventoryStats AS (\n    SELECT \n        ProductID,\n        StockLevel,\n        ReorderPoint,\n        LeadTimeDays\n    FROM Inventory\n),\nProductMetrics AS (\n    SELECT \n        p.ProductID,\n        p.Name,\n        os.TimesOrdered,\n        os.TotalQuantity,\n        i.StockLevel,\n        i.ReorderPoint,\n        CASE\n            WHEN i.StockLevel <= i.ReorderPoint \n                AND os.TimesOrdered &gt; 10\n                THEN 'Urgent Reorder'\n            WHEN i.StockLevel <= i.ReorderPoint\n                THEN 'Regular Reorder'\n            WHEN os.TimesOrdered = 0\n                THEN 'Dead Stock'\n            ELSE 'Adequate Stock'\n        END AS StockStatus\n    FROM Products p\n    LEFT JOIN OrderStats os ON p.ProductID = os.ProductID\n    LEFT JOIN InventoryStats i ON p.ProductID = i.ProductID\n)\nSELECT \n    StockStatus,\n    COUNT(*) AS ProductCount,\n    SUM(StockLevel) AS TotalUnits\nFROM ProductMetrics\nGROUP BY StockStatus\nORDER BY ProductCount DESC;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> OrderStats <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        ProductID,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> TimesOrdered,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(Quantity) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> TotalQuantity<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> OrderDetails<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> ProductID<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">InventoryStats <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        ProductID,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        StockLevel,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        ReorderPoint,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        LeadTimeDays<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Inventory<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">ProductMetrics <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">ProductID<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">Name<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">os<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">TimesOrdered<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">os<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">TotalQuantity<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">i<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">StockLevel<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">i<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">ReorderPoint<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">CASE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">i<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">StockLevel<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&lt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">i<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">ReorderPoint<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">                <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">os<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">TimesOrdered<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">10<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">                <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Urgent Reorder&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">i<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">StockLevel<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&lt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">i<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">ReorderPoint<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">                <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Regular Reorder&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">os<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">TimesOrdered<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">0<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">                <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Dead Stock&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">ELSE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Adequate Stock&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> StockStatus<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Products p<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">LEFT JOIN<\/span><span style=\"color: #ABB2BF\"> OrderStats os <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">ProductID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">os<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">ProductID<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">LEFT JOIN<\/span><span style=\"color: #ABB2BF\"> InventoryStats i <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">ProductID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">i<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">ProductID<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    StockStatus,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> ProductCount,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(StockLevel) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> TotalUnits<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> ProductMetrics<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> StockStatus<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> ProductCount <\/span><span style=\"color: #C678DD\">DESC<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h3 class=\"wp-block-heading\">2. Temporary Results for Complex Calculations<\/h3>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"WITH SalesData AS (\n    SELECT \n        ProductID,\n        SUM(Amount) AS Revenue,\n        SUM(Quantity) AS UnitsSold,\n        SUM(Amount - (Cost * Quantity)) AS Profit\n    FROM Sales\n    GROUP BY ProductID\n),\nProductRanks AS (\n    SELECT \n        ProductID,\n        Revenue,\n        UnitsSold,\n        Profit,\n        NTILE(4) OVER (ORDER BY Revenue DESC) AS RevenueQuartile,\n        NTILE(4) OVER (ORDER BY Profit DESC) AS ProfitQuartile\n    FROM SalesData\n),\nPerformance AS (\n    SELECT \n        ProductID,\n        CASE \n            WHEN RevenueQuartile = 1 AND ProfitQuartile = 1 \n                THEN 'Star'\n            WHEN RevenueQuartile <= 2 AND ProfitQuartile &gt; 2\n                THEN 'High Volume Low Margin'\n            WHEN RevenueQuartile &gt; 2 AND ProfitQuartile <= 2\n                THEN 'Low Volume High Margin'\n            ELSE 'Underperforming'\n        END AS Category\n    FROM ProductRanks\n)\nSELECT \n    p.Category,\n    COUNT(*) AS ProductCount,\n    FORMAT(AVG(s.Revenue), 'C') AS AvgRevenue,\n    FORMAT(AVG(s.Profit), 'C') AS AvgProfit\nFROM Performance p\nJOIN SalesData s ON p.ProductID = s.ProductID\nGROUP BY p.Category\nORDER BY AvgProfit DESC;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> SalesData <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        ProductID,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(Amount) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> Revenue,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(Quantity) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> UnitsSold,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(Amount - (Cost * Quantity)) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> Profit<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> ProductID<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">ProductRanks <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        ProductID,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        Revenue,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        UnitsSold,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        Profit,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">NTILE<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">4<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> Revenue <\/span><span style=\"color: #C678DD\">DESC<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> RevenueQuartile,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">NTILE<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">4<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">OVER<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> Profit <\/span><span style=\"color: #C678DD\">DESC<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> ProfitQuartile<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> SalesData<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">Performance <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        ProductID,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> RevenueQuartile <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> ProfitQuartile <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">                <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Star&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> RevenueQuartile <\/span><span style=\"color: #56B6C2\">&lt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">2<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> ProfitQuartile <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">2<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">                <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;High Volume Low Margin&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> RevenueQuartile <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">2<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> ProfitQuartile <\/span><span style=\"color: #56B6C2\">&lt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">2<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">                <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Low Volume High Margin&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">ELSE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Underperforming&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> Category<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> ProductRanks<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">Category<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> ProductCount,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">FORMAT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #56B6C2\">AVG<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">Revenue<\/span><span style=\"color: #ABB2BF\">), <\/span><span style=\"color: #98C379\">&#39;C&#39;<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> AvgRevenue,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">FORMAT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #56B6C2\">AVG<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">Profit<\/span><span style=\"color: #ABB2BF\">), <\/span><span style=\"color: #98C379\">&#39;C&#39;<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> AvgProfit<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Performance p<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">JOIN<\/span><span style=\"color: #ABB2BF\"> SalesData s <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">ProductID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">s<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">ProductID<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">Category<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> AvgProfit <\/span><span style=\"color: #C678DD\">DESC<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Common Pitfalls and Solutions<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">1. Recursive CTE Infinite Loops<\/h3>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"-- Problematic recursive CTE\nWITH BadRecursion AS (\n    SELECT ID, ParentID\n    FROM Hierarchy\n    WHERE ID = 1\n\n    UNION ALL\n\n    SELECT h.ID, h.ParentID\n    FROM Hierarchy h\n    JOIN BadRecursion b ON h.ParentID = b.ID\n    -- Missing termination condition!\n)\n\n-- Fixed version with MAXRECURSION hint\nWITH SafeRecursion AS (\n    SELECT ID, ParentID, 1 AS Level\n    FROM Hierarchy\n    WHERE ID = 1\n\n    UNION ALL\n\n    SELECT h.ID, h.ParentID, r.Level + 1\n    FROM Hierarchy h\n    JOIN SafeRecursion r ON h.ParentID = r.ID\n    WHERE r.Level < 10  -- Prevents infinite recursion\n)\nSELECT *\nFROM SafeRecursion\nOPTION (MAXRECURSION 10);\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Problematic recursive CTE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> BadRecursion <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> ID, ParentID<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Hierarchy<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> ID <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">UNION ALL<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">h<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">ID<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">h<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">ParentID<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Hierarchy h<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">JOIN<\/span><span style=\"color: #ABB2BF\"> BadRecursion b <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">h<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">ParentID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">b<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">ID<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #7F848E; font-style: italic\">-- Missing termination condition!<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Fixed version with MAXRECURSION hint<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> SafeRecursion <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> ID, ParentID, <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">Level<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Hierarchy<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> ID <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">UNION ALL<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">h<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">ID<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">h<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">ParentID<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">r<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">Level<\/span><span style=\"color: #ABB2BF\"> + <\/span><span style=\"color: #D19A66\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Hierarchy h<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">JOIN<\/span><span style=\"color: #ABB2BF\"> SafeRecursion r <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">h<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">ParentID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">r<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">ID<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">r<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">Level<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&lt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">10<\/span><span style=\"color: #ABB2BF\">  <\/span><span style=\"color: #7F848E; font-style: italic\">-- Prevents infinite recursion<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> *<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> SafeRecursion<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">OPTION<\/span><span style=\"color: #ABB2BF\"> (MAXRECURSION <\/span><span style=\"color: #D19A66\">10<\/span><span style=\"color: #ABB2BF\">);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h3 class=\"wp-block-heading\">2. Performance Considerations<\/h3>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"-- Instead of multiple separate CTEs\nWITH Orders2024 AS (\n    SELECT * FROM Orders WHERE YEAR(OrderDate) = 2024\n),\nOrders2023 AS (\n    SELECT * FROM Orders WHERE YEAR(OrderDate) = 2023\n)\n\n-- Use a single CTE with conditional logic\nWITH YearlyOrders AS (\n    SELECT \n        *,\n        YEAR(OrderDate) AS OrderYear\n    FROM Orders\n    WHERE YEAR(OrderDate) IN (2023, 2024)\n)\nSELECT \n    OrderYear,\n    COUNT(*) AS OrderCount,\n    SUM(Amount) AS TotalRevenue\nFROM YearlyOrders\nGROUP BY OrderYear;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Instead of multiple separate CTEs<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> Orders2024 <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Orders <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">YEAR<\/span><span style=\"color: #ABB2BF\">(OrderDate) <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">2024<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">Orders2023 <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Orders <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">YEAR<\/span><span style=\"color: #ABB2BF\">(OrderDate) <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">2023<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Use a single CTE with conditional logic<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> YearlyOrders <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        *,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #56B6C2\">YEAR<\/span><span style=\"color: #ABB2BF\">(OrderDate) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> OrderYear<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Orders<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">YEAR<\/span><span style=\"color: #ABB2BF\">(OrderDate) <\/span><span style=\"color: #C678DD\">IN<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #D19A66\">2023<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">2024<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    OrderYear,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">COUNT<\/span><span style=\"color: #ABB2BF\">(*) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> OrderCount,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(Amount) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> TotalRevenue<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> YearlyOrders<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> OrderYear;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">The WITH clause helps you write clearer, more maintainable SQL. Use it to break down complex problems into smaller pieces, making your queries easier to understand and debug. Just remember to keep your CTEs focused and avoid unnecessary nesting that could impact performance.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Breakdown of SQL Server WITH Clause The WITH clause in SQL Server (also known as Common Table Expressions or CTEs) lets you break down complex queries into readable, manageable chunks. Instead of nesting multiple subqueries, you can write them as separate, named pieces. Let&#8217;s see how to use them effectively. Basic WITH Clause Structure Here&#8217;s [&hellip;]<\/p>\n","protected":false},"author":11,"featured_media":4318,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_eb_attr":"","_uag_custom_page_level_css":"","footnotes":""},"categories":[21],"tags":[],"class_list":["post-4317","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog"],"acf":[],"uagb_featured_image_src":{"full":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Server-WITH-Clause-Complete-Guide.jpg",1280,720,false],"thumbnail":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Server-WITH-Clause-Complete-Guide-150x150.jpg",150,150,true],"medium":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Server-WITH-Clause-Complete-Guide-300x169.jpg",300,169,true],"medium_large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Server-WITH-Clause-Complete-Guide-768x432.jpg",768,432,true],"large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Server-WITH-Clause-Complete-Guide-1024x576.jpg",1024,576,true],"1536x1536":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Server-WITH-Clause-Complete-Guide.jpg",1280,720,false],"2048x2048":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Server-WITH-Clause-Complete-Guide.jpg",1280,720,false]},"uagb_author_info":{"display_name":"Ryan Forrester","author_link":"https:\/\/preview-php85.dzine.org\/five\/author\/f1v5_ed9_au\/"},"uagb_comment_info":0,"uagb_excerpt":"Breakdown of SQL Server WITH Clause The WITH clause in SQL Server (also known as Common Table Expressions or CTEs) lets you break down complex queries into readable, manageable chunks. Instead of nesting multiple subqueries, you can write them as separate, named pieces. Let&#8217;s see how to use them effectively. Basic WITH Clause Structure Here&#8217;s&hellip;","_links":{"self":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4317","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/comments?post=4317"}],"version-history":[{"count":0,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4317\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media\/4318"}],"wp:attachment":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media?parent=4317"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/categories?post=4317"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/tags?post=4317"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}