Code Compass

  Introduction SQL allows you to write flexible and powerful queries using subqueries and Common Table Expressions (CTEs). Both help break down complex logic, reuse code, and improve readability but they work a bit differently. What is a Subquery? A subquery is a query inside another query. Below is a query which shows customers whose remaining amount is above the average. SELECT    [Customer No_],    [Remaining Amount] FROM [Customer Ledger Entry] WHERE [Remaining Amount] > (   SELECT AVG([Remaining Amount])   FROM [Customer Ledger Entry] ); What is a CTE (Common Table Expression)? A CTE is a temporary result set you can reference in a main query. It starts with the WITH keyword and improves readability, especially with multi-step logic. -- Step 1: Define the CTE WITH AvgBalance AS (   SELECT      AVG([Remaining Amount]) AS avg_balance   FROM [Customer Ledger Entry] ) ...

Introduction Window functions in SQL let you do calculations across rows related to the current row , without combining them into one. Unlike regular aggregate functions, window functions keep each row in the result; they just look at other rows while calculating. For example, you can calculate: the rank of each salesperson within their region, a running total of sales for each customer, or the difference between current and previous month’s revenue. SELECT   [Customer No_],   [Location Code],   [Remaining Amount],   SUM([Remaining Amount]) OVER (     PARTITION BY [Location Code]     ORDER BY [Customer No_]   ) AS LocationOutstandingTotal FROM [Customer Ledger Entry]; We’re calculating the sum of [Remaining Amount] OVER (...) turns it into a window function PARTITION BY [Location Code] groups the rows by location ORDER BY [Customer No_] gives a running total within each location Note: Yo...