SubQueries & CTEs

CTE (Common Table Expression)

Its a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. Think of it as a "query variable" — you define it once at the top, then reference it by name anywhere in your main query.

One rule worth keeping across all of them: name the CTE after what it contains, not what it does. 𝗱𝗿𝗶𝘃𝗲𝗿𝘁𝗿𝗶𝗽𝗰𝗼𝘂𝗻𝘁s tells the next reader what's in the result set before they read a single line of the definition. temp1 tells them nothing;

The WITH keyword is used to introduce a CTE with the name vehicle_total in this case defining the name of the cte for easy calling in the main query.

CTES are useful during deduplication during data cleaning:
Below I used a CTE to delete duplicate rows in my Postgres database.

The Delete is unreversible operation Use Select first to verify Duplicates

Recursive CTEs

Recursive CTEs reference themselves till it hits a stopping point. They are useful executing hierarchical data.
Recursive CTEs have to follow 3 procedures for effectivity;

• The Anchor Member: Runs exactly once at the beginning to establish your starting point.

• The Recursive Member: The loop engine. It executes repeatedly, taking the output of the previous step and joining it back against the source table to find the next level down.

• The Termination Condition: Hidden inside the JOIN logic. The loop automatically breaks when a query execution yields zero new matching rows:

The query starts with the anchor where the top-management does not have managers and assigns it level 1, this is where the loop builds on.
The UNION ALL in this statement Combines anchor and recursive results while preserving all rows without deduplication.
The recursive member joins employees back to the CTE itself (organization) to walk down the hierarchy one level at a time.
here is the point the real recursion occurs as the loop runs it finds the next layer down in the org chart and increments the level counter.

SUBQUERIES

A subquery is a query placed inside another SQL statement. It is wrapped in parentheses, and its result is used by the outer query.
Here is an example of a subquery, I used it to find what rows are duplicated of BK006 that will be deleted:

From this Image:
It finds all duplicate rows for booking_id = 'BK0006' while keeping the first physical row (lowest ctid) and discarding the rest.
The subquery finds the smallest ctid for that booking.
The outer query returns every row for BK0006 except that first one. which is useful for cleaning.