Top 3 produk terlaris per kategori (PostgreSQL window)

Pertanyaan klasik di dashboard analytics: “Tampilkan top 3 produk per kategori berdasarkan total revenue bulan ini.” Tanpa window function, ini biasanya jadi tiga subquery atau correlated subquery yang lambat. Pakai ROW_NUMBER() jauh lebih bersih.

Schema (asumsi)

CREATE TABLE products (
  id            SERIAL PRIMARY KEY,
  name          TEXT NOT NULL,
  category_id   INT NOT NULL
);

CREATE TABLE order_items (
  order_id      INT,
  product_id    INT REFERENCES products(id),
  quantity      INT,
  price         NUMERIC(12, 2),
  created_at    TIMESTAMPTZ
);

CREATE TABLE categories (
  id    SERIAL PRIMARY KEY,
  name  TEXT NOT NULL
);

Kode

WITH revenue_per_product AS (
  SELECT
    p.id                                AS product_id,
    p.name                              AS product_name,
    c.id                                AS category_id,
    c.name                              AS category_name,
    SUM(oi.quantity * oi.price)         AS total_revenue
  FROM products p
  JOIN categories c        ON c.id = p.category_id
  JOIN order_items oi      ON oi.product_id = p.id
  WHERE oi.created_at >= date_trunc('month', CURRENT_DATE)
    AND oi.created_at <  date_trunc('month', CURRENT_DATE) + INTERVAL '1 month'
  GROUP BY p.id, p.name, c.id, c.name
),
ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY category_id
      ORDER BY total_revenue DESC
    ) AS rank_in_category
  FROM revenue_per_product
)
SELECT
  category_name,
  rank_in_category,
  product_name,
  total_revenue
FROM ranked
WHERE rank_in_category <= 3
ORDER BY category_name, rank_in_category;

Output

 category_name | rank_in_category | product_name        | total_revenue
---------------+------------------+---------------------+---------------
 Elektronik    |                1 | iPhone 17 Pro        |   142500000.00
 Elektronik    |                2 | Samsung Galaxy S26   |    87300000.00
 Elektronik    |                3 | MacBook Air M4       |    54800000.00
 Fashion       |                1 | Sepatu Compass       |    23500000.00
 Fashion       |                2 | Kemeja Erigo         |    19200000.00
 ...

Kapan dipakai

• Dashboard analytics monthly recap.
• Email report ke client/manager.
• Validasi data sebelum migrasi — “produk terlaris harus tetap sama setelah migrasi.”

Variasi: RANK() vs DENSE_RANK() vs ROW_NUMBER()

• ROW_NUMBER(): Setiap baris dapat nomor unik. Kalau ada tie (revenue sama), urutannya random.
• RANK(): Tie dapat rank sama. Rank berikutnya skip. Misal: 1, 2, 2, 4.
• DENSE_RANK(): Tie dapat rank sama. Rank berikutnya tidak skip. Misal: 1, 2, 2, 3.

Untuk “top 3”, biasanya ROW_NUMBER() cukup. Kalau bisnis butuh “semua yang tie di posisi 3 ikut”, pakai DENSE_RANK().

Catatan

• PostgreSQL dan MySQL 8+ sama-sama support window function. MySQL 5.x tidak.
• Performa: untuk dataset > 10 juta baris, pertimbangkan index di order_items(product_id, created_at). Window function bukan magic — masih perlu data hash.
• Kalau category banyak (>1000) dan dataset super besar, partition revenue_per_product ke materialized view yang refresh harian.

Window function ini juga bisa dipakai untuk: “session terakhir per user”, “transaksi pertama per kustomer”, “gap terbesar antar event” (lag/lead). Belajar 4-5 window function dasar dapat 80% kebutuhan analytics.