Why should you care?

Flipkart wants each product’s price rank within its category on the same row as the product — window functions, not self-joins.

See it live — copy this example

Run in pgAdmin or psql.

SELECT
  product_id,
  category,
  price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank
FROM products;

What happened?

• PARTITION BY category resets ranking per category.
• ORDER BY price DESC makes expensive items rank 1.
• Every product row stays; rank appears beside it.

Practice next

1. Run the window query on products.
2. Compare with GROUP BY — note row count difference.
3. Add LAG(price) OVER (PARTITION BY category ORDER BY price) for previous price.
4. SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY created_at) for running spend.
5. ROW_NUMBER to dedupe: keep latest row per customer_id in a CTE.

Remember

OVER keeps detail rows while adding analytics columns. PARTITION BY is like GROUP BY without collapsing. LAG/LEAD compare to neighbor rows.

PostgresVerse category leaderboard

Merchandising UI shows price_rank per category without a second API call.

Outcome: PM spots overpriced SKUs sitting at rank 1 in budget categories.