Top N per Group

Kapan Pakai Query Ini

Ketika kamu butuh top 3 produk per region, top 5 sales rep per quarter, atau top 10 customer per tier. Bukan top N secara global, tapi top N di dalam setiap grup. Polanya: hitung rank pakai ROW_NUMBER() OVER (PARTITION BY grup ORDER BY metrik DESC), lalu filter rank <= N. Di BigQuery dan Snowflake pakai QUALIFY langsung; di PostgreSQL dan MySQL pakai subquery.

Yang Perlu Diganti

• your_schema.orders → nama tabel transaksi kamu
• region → kolom yang jadi grup (misal: product_category, tier, cohort_month)
• product_name → kolom item yang mau di-rank dalam tiap grup
• revenue → kolom metrik untuk ranking (misal: order_count, profit, user_count)
• 3 di <= 3 → ganti dengan N yang kamu mau (top 5, top 10, dll.)
• status = 'completed' → filter status yang valid (hapus kalau nggak perlu)

Query

Salin
SELECT
  region,
  product_name,
  SUM(revenue)  AS total_revenue,
  COUNT(*)      AS total_orders,
  ROW_NUMBER() OVER (
    PARTITION BY region
    ORDER BY SUM(revenue) DESC
  )             AS rank_in_region
FROM your_schema.orders
WHERE status = 'completed'
GROUP BY region, product_name
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY region
  ORDER BY SUM(revenue) DESC
) <= 3;

Contoh Output

Variasi

• Ganti ROW_NUMBER() dengan DENSE_RANK() kalau kamu mau dua produk dengan revenue sama dapat rank yang sama (bukan diputus secara arbitrary)
• Ganti <= 3 dengan = 1 untuk ambil hanya yang terbaik per grup
• Tambah kolom RANK() OVER (...) AS global_rank untuk bisa lihat rank global sekaligus rank dalam grup

Variasi Dialect

PostgreSQL

PostgreSQL tidak punya QUALIFY. Harus wrap di subquery:

Salin
SELECT
  region,
  product_name,
  total_revenue,
  total_orders,
  rank_in_region
FROM (
  SELECT
    region,
    product_name,
    SUM(revenue)  AS total_revenue,
    COUNT(*)      AS total_orders,
    ROW_NUMBER() OVER (
      PARTITION BY region
      ORDER BY SUM(revenue) DESC
    )             AS rank_in_region
  FROM your_schema.orders
  WHERE status = 'completed'
  GROUP BY region, product_name
) ranked
WHERE rank_in_region <= 3;

Snowflake

Snowflake mendukung QUALIFY. Syntax sama persis dengan BigQuery di atas.

MySQL

MySQL 8.0+ punya window functions tapi tidak punya QUALIFY. Pakai subquery seperti PostgreSQL:

Salin
SELECT
  region,
  product_name,
  total_revenue,
  total_orders,
  rank_in_region
FROM (
  SELECT
    region,
    product_name,
    SUM(revenue)  AS total_revenue,
    COUNT(*)      AS total_orders,
    ROW_NUMBER() OVER (
      PARTITION BY region
      ORDER BY SUM(revenue) DESC
    )             AS rank_in_region
  FROM your_schema.orders
  WHERE status = 'completed'
  GROUP BY region, product_name
) ranked
WHERE rank_in_region <= 3;