SQL practice: Pivoting: Turning Rows Into Columns

For every branch that has orders, show branch_name and three counts: delivered, pending and cancelled. Order by branch_name.

Write the same delivered count using COUNT with a CASE expression instead of FILTER. Show branch_name and delivered, ordered by branch_name.

For each region, show total revenue from Delivered orders only, in a column named delivered_revenue, alongside region. Revenue is quantity times unit_price from order_items. Order by region.

For each branch show branch_name, revenue from orders before 1 March 2024 as jan_feb, and revenue from 1 March onwards as mar_on. Use 0 rather than NULL where there is nothing. Order by branch_name.

Show one row per product category with the number of ACP-sheet-thickness bands: thin_3mm for thickness 3, standard_4mm for 4, and thick_6mm for 6. Show category too, ordered by category.

Your pivot query hard-codes three order statuses. Someone adds a fourth status to the table. What happens to the query?

Build a monthly cohort retention grid for orders. In first_orders, find each customer's first order month as cohort_month. In activity, join orders and calculate month_number as the number of elapsed months between cohort_month and order_date: (extract(year from o.order_date) - extract(year from f.cohort_month)) * 12 + (extract(month from o.order_date) - extract(month from f.cohort_month)). Return cohort_month formatted as 'YYYY-MM', cohort_size (total distinct customers in that cohort), and distinct active customer counts for month offsets 0, 1, 2, and 3 as m0, m1, m2, and m3 using FILTER. Order by cohort_month.