Archive Database Step 8: Rewrite for Reading

Take the most complicated query you have written so far and rewrite it using CTEs, one stage per CTE, each named for what it produces. Then show both versions to somebody and ask which they can explain back to you. Keep the one that wins. Then write one query using a subquery as a value, and one anti-join written two ways, once with NOT EXISTS and once with a LEFT JOIN, and confirm they agree.