SQL practice: generate_series and LATERAL: Rows From Nothing

Produce the numbers 1 to 12 in a column named month_number.

Produce the first day of every month from January 2024 to June 2024 inclusive, in a column named month.

Count orders in every month from January to June 2024, including months with none. Show month formatted as YYYY-MM and a count named orders, ordered by month.

For every branch, show its two most recent orders. Return branch_name, order_id and order_date, ordered by branch_name then order_date descending. Use LATERAL.

Do the same again, but keep branches that have no orders at all, showing NULLs for them. Return branch_name, order_id and order_date, ordered by branch_name then order_date descending.

For every branch show its name and the value of its single largest order line, computed by a LATERAL subquery. Name the column biggest_line, ordered by branch_name. Branches with no orders should not appear.

Expand order 1 so that each item appears once per unit ordered — 50 sheets become 50 rows. Return order_id and product_id only, ordered by product_id. Use generate_series.

Why does a plain (non-LATERAL) subquery in FROM fail when it references a column from a table listed beside it?

Find each customer's streaks of consecutive active months using the gaps-and-islands technique. First, find distinct customer_id and month numbers (1 to 12) from orders. In a second CTE, calculate an island group by subtracting ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY mo) from the month number. Return customer_id, start_month (minimum month of the streak), end_month (maximum month of the streak), and streak_length (count of months in the streak). Order by customer_id, start_month.