Per genre, show the count (n), average price to 2 dp (avg_price), and how many are out of stock (out_of_stock) — use FILTER.

Answer

SELECT genre, count(*) AS n, round(avg(price), 2) AS avg_price, count(*) FILTER (WHERE stock = 0) AS out_of_stock FROM books GROUP BY genre;