MIN_BY and MAX_BY in Snowflake

How to use MAX_BY and MIN_BY in Snowflake (and DuckDB, BigQuery, Databricks) to replace messy self-joins and windows when getting latest or top-N values per group.