Subqueries, CTEs and window functions

What you'll practice

1. A query inside a query. A subquery in brackets runs first, and its result is used by the outer query.
2. IN a subquery. A subquery can give a list, for use with IN.
3. Value of an order. You can calculate inside SUM. Here each line is worth its quantity times its unit_price.
4. Name a step with WITH. WITH gives a subquery a name, known as a CTE. A long query then reads from top to bottom, one step at a time.
5. Label with CASE. CASE chooses a value by condition, as an "if" inside a query.
6. Rank inside a group. A window function calculates across rows, and keeps them all, unlike GROUP BY. ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) numbers the rows inside each group.

See also

• SQL Dialect Reference
• SQL Playground
• All topics