When I started writing SQL against transaction data in fintech and securities brokerage environments, my queries were a mess. Three levels of nesting, the same aggregate computed twice, and a debugging process that involved commenting out chunks until something ran. The shift came when I stopped treating subqueries as the only way to compose logic and started picking the right structure for the job.
This article walks through:
what a subquery actually is
the four shapes a subquery can take
when a subquery is the correct choice
what a Common Table Expression (CTE) is
non-recursive and recursive CTEs, with examples drawn from real reporting work
a side-by-side comparison covering readability, debugging, and performance
the rule of thumb I now use to decide between the two
Most of the examples below are loosely modelled on the kinds of tables I work with day to day β member records in a SACCO, KYC verification logs, trade tickets, and transaction ledgers.β¦