-- Created on savesnippets.com · https://savesnippets.com/zgdizJxnft2cFw
-- Most recent order per user
SELECT *
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY user_id
           ORDER     BY created_at DESC
         ) AS rn
  FROM   orders o
) t
WHERE rn = 1;

-- Top 3 highest-priced products per category
SELECT *
FROM (
  SELECT p.*,
         ROW_NUMBER() OVER (
           PARTITION BY category_id
           ORDER     BY price DESC
         ) AS rn
  FROM   products p
) t
WHERE rn <= 3;

-- PostgreSQL shortcut: DISTINCT ON (user_id) does the same for "first per group"
SELECT DISTINCT ON (user_id) *
FROM   orders
ORDER  BY user_id, created_at DESC;