Wawancara SQL: Temukan Tiga Produk Teratas per Kategori

Prompt dan Konteks yang Berlaku

Menggunakan PostgreSQL, kembalikan tiga tingkat pendapatan produk yang berbeda teratas di setiap kategori untuk Q2 2026, termasuk setiap produk yang seri di tingkat ketiga. Nyatakan kontrak data, tulis SQL-nya, dan jelaskan mengapa ROW_NUMBER, RANK, dan DENSE_RANK menghasilkan hasil yang berbeda.

Skemanya adalah:

orders( order_id bigint primary key, ordered_at timestamptz, status text )

order_items( order_id bigint, category_id bigint, product_id bigint, quantity integer, unit_price numeric(12, 2) )

Gunakan kontrak yang disepakati ini: hitung hanya pesanan COMPLETED; definisikan kuartal sebagai interval setengah-terbuka UTC [2026-04-01, 2026-07-01); pendapatan produk adalah jumlah dari quantity * unit_price atas item baris yang memenuhi syarat; pengembalian dana berada di luar model yang disediakan; dan "tiga teratas" berarti tiga tingkat pendapatan berbeda tertinggi per kategori, sehingga setiap produk yang seri di tingkat ketiga harus dikembalikan. Produk tanpa penjualan yang memenuhi syarat tidak muncul, dan kategori dengan kurang dari tiga tingkat mengembalikan semua tingkat yang dimilikinya.

Pertanyaan tingkat menengah ini cocok untuk peran analis data, insinyur data, dan backend yang memerlukan SQL analitis. Materi wawancara SQL 2026 yang tersedia untuk umum masih menyajikan top-N-per-group, fungsi jendela, dan perilaku seri sebagai latihan eksplisit. Tidak ada atribusi perusahaan yang dapat diverifikasi, sehingga diperlakukan sebagai pertanyaan wawancara SQL yang representatif.

Apa yang Dievaluasi oleh Pewawancara

Sinyal pertama adalah apakah Anda menyatakan tingkat output. Satu baris order_items adalah satu item baris, tetapi targetnya adalah satu baris per pasangan kategori-produk. Meranking item baris mentah memberikan satu produk beberapa posisi. SQL yang valid secara sintaksis masih bisa menjawab pertanyaan bisnis yang salah.

Sinyal kedua adalah apakah Anda menerjemahkan "tiga teratas" ke dalam semantik seri yang eksplisit. ROW_NUMBER, RANK, dan DENSE_RANK semuanya dapat mengurutkan baris dalam sebuah kategori, tetapi masing-masing menjawab pertanyaan yang berbeda. Kandidat yang kuat pertama-tama menanyakan apakah hasilnya membutuhkan tepat tiga baris, peringkat kompetisi, atau tiga tingkat pendapatan yang berbeda.

Sinyal ketiga adalah pengetahuan tentang urutan evaluasi logis SQL. Fungsi jendela berjalan setelah WHERE, GROUP BY, HAVING, dan agregasi biasa. Agregasikan pendapatan produk terlebih dahulu, lalu beri peringkat di tingkat berikutnya, dan filter peringkat di kueri luar. PostgreSQL tidak dapat menggunakan alias jendela di klausa WHERE pada tingkat kueri yang sama.

Sinyal terakhir adalah kemampuan verifikasi dan pemeliharaan. Jawaban yang kuat mendefinisikan batas waktu, status yang memenuhi syarat, tipe uang, urutan presentasi yang deterministik, dan fixture pengujian. Pada skala besar, ia terlebih dahulu mengurangi baris yang masuk ke tahap perankingan alih-alih menawarkan "tambahkan indeks" sebagai solusi yang tidak didukung.

Pertanyaan untuk Diklarifikasi Sebelum Menjawab

• Apakah "tiga teratas" berarti tiga baris, tiga posisi kompetisi, atau tiga nilai yang berbeda? Gunakan ROW_NUMBER untuk tiga baris, RANK untuk perankingan kompetisi, dan DENSE_RANK untuk persyaratan ini: tiga tingkat pendapatan yang berbeda dengan seri yang dipertahankan.
• Peristiwa dan zona waktu mana yang mengaitkan pendapatan ke suatu periode? Waktu pesanan, pembayaran, penyelesaian, dan pengembalian dana menghasilkan laporan yang berbeda. Prompt ini menggunakan orders.ordered_at dalam UTC dan interval setengah-terbuka yang tidak tumpang tindih.
• Status mana yang memenuhi syarat? Memasukkan pesanan yang dibatalkan, uji coba, atau gagal akan menggelembungkan pendapatan. Kontrak ini hanya menghitung COMPLETED.
• Di mana pengembalian dana, diskon, pajak, dan mata uang? Model yang disediakan hanya berisi kuantitas dan harga satuan transaksi. Jika tabel pengembalian dana atau beberapa mata uang ditambahkan, hitung pendapatan bersih atau konversikan ke satu mata uang sebelum meranking.
• Bisakah satu produk termasuk dalam beberapa kategori? Kueri ini memperlakukan (category_id, product_id) pada item baris sebagai kunci fakta. Menggabungkan dimensi produk saat ini dapat menulis ulang atribusi kategori historis; gunakan snapshot waktu-pesanan atau dimensi bertanggal-efektif ketika riwayat tersebut penting.
• Haruskah produk dengan nol penjualan muncul? Produk tersebut tidak muncul di sini. Jika diperlukan, lakukan left join agregat ke set kategori-produk yang lengkap dan tentukan apakah nol berpartisipasi dalam tiga tingkat teratas.
• Haruskah urutan output bersifat deterministik? Beri peringkat hanya berdasarkan pendapatan; menambahkan product_id ke jendela perankingan akan menghancurkan seri. Stabilkan presentasi secara terpisah dengan ORDER BY category_id, revenue_rank, product_id.

Kerangka Jawaban 30 Detik

"Saya akan mengagregasikan pesanan UTC Q2 yang selesai ke satu baris per kategori dan produk, lalu menggunakan DENSE_RANK berdasarkan kategori dan pendapatan menurun karena persyaratannya adalah tiga tingkat berbeda dengan seri. Sebuah CTE menghitung peringkat, kueri luar memfilter revenue_rank <= 3, dan pengurutan akhir hanya menstabilkan tampilan. Saya akan menguji seri, batas kuartal, pesanan yang dibatalkan, dan kategori dengan kurang dari tiga tingkat."

Jawaban yang lengkap juga harus menjelaskan mengapa agregasi mendahului perankingan, bagaimana ketiga fungsi perankingan berbeda, dan bagaimana memvalidasi kontrak pelaporan maupun rencana eksekusi pada skala besar.

Jawaban Mendalam Langkah demi Langkah

Langkah 1: Ungkapkan Pertanyaan Bisnis sebagai Tingkat dan Invarian

Kunci hasil bukan order_id; melainkan (category_id, product_id). Setiap kunci harus muncul sekali di tahap agregat, dan revenue harus sama dengan jumlah semua jumlah item baris yang memenuhi syarat untuk kunci tersebut. Perankingan menambahkan tingkat lokal-kategori tanpa mengubah tingkat agregasi.

Nyatakan tiga invarian sebelum menulis sintaksis: setiap item baris yang memenuhi syarat berkontribusi tepat satu kali; produk yang sama di berbagai pesanan digabungkan; dan revenue_rank sebuah kategori hanya bergantung pada pendapatan di dalam kategori tersebut. Invarian-invarian ini mengungkap join duplikat, perankingan item baris, dan perankingan global dengan cepat.

Langkah 2: Filter Fakta Sebelum Mengagregasikan ke Tingkat Produk

Gunakan >= untuk awal dan < untuk awal kuartal berikutnya. Tidak seperti BETWEEN atau 2026-06-30 23:59:59, interval setengah-terbuka tidak melewatkan presisi timestamp yang lebih tinggi dan tersusun dengan bersih bersama kuartal berikutnya. +00 eksplisit pada literal timestamptz membuat kontrak independen dari zona waktu sesi database.

Setelah penggabungan, filter status dan waktu, lalu kelompokkan berdasarkan category_id, product_id. Dalam model PostgreSQL ini, quantity * unit_price tetap merupakan ekspresi numerik yang tepat, dan SUM mempertahankan aritmetika uang yang tepat. Jangan mengonversinya ke floating point demi kenyamanan. Laporan pendapatan produksi juga memerlukan semantik mata uang dan pengembalian dana, tetapi kolom yang disediakan tidak dapat menghasilkannya.

Langkah 3: Pilih Fungsi Perankingan dari Kontrak Seri

Misalkan satu kategori memiliki pendapatan produk 100, 100, 90, 80:

• ROW_NUMBER menghasilkan 1, 2, 3, 4. Tanpa kunci tambahan, dua baris 100 memiliki urutan relatif yang tidak ditentukan. Contoh ini mengembalikan kedua baris 100 dan satu baris 90, tetapi seri yang melintasi batas baris ketiga akan dipotong secara sewenang-wenang. Ini menjamin tiga baris, bukan seri yang lengkap.
• RANK menghasilkan 1, 1, 3, 4. Memfilter pada 3 mengembalikan pendapatan 100 dan 90, hanya dua tingkat yang berbeda.
• DENSE_RANK menghasilkan 1, 1, 2, 3. Memfilter pada 3 mengembalikan semua empat produk dan tepat tiga tingkat yang berbeda.

Oleh karena itu prompt ini memerlukan DENSE_RANK. Jendela ORDER BY-nya harus hanya berisi revenue DESC. Menambahkan product_id berarti baris dengan pendapatan yang sama tidak lagi menjadi sejawat, secara diam-diam menggantikan "pertahankan seri" dengan urutan yang dibuat-buat.

Langkah 4: Pisahkan Agregasi, Perankingan, dan Pemfilteran dengan Dua CTE

Kueri PostgreSQL yang lengkap adalah:

WITH product_revenue AS ( SELECT oi.category_id, oi.product_id, SUM(oi.quantity * oi.unit_price) AS revenue FROM orders AS o JOIN order_items AS oi ON oi.orderid = o.orderid WHERE o.status = 'COMPLETED' AND o.ordered_at >= TIMESTAMPTZ '2026-04-01 00:00:00+00' AND o.ordered_at < TIMESTAMPTZ '2026-07-01 00:00:00+00' GROUP BY oi.categoryid, oi.productid ), ranked AS ( SELECT category_id, product_id, revenue, DENSE_RANK() OVER ( PARTITION BY category_id ORDER BY revenue DESC ) AS revenue_rank FROM product_revenue ) SELECT categoryid, productid, revenue, revenue_rank FROM ranked WHERE revenue_rank <= 3 ORDER BY categoryid, revenuerank, product_id;

Tahap pertama mereduksi banyak item pesanan menjadi fakta produk. Tahap kedua hanya mengurutkan set produk di dalam setiap kategori. Kueri luar kemudian dapat melihat dan memfilter hasil jendela. Tahap-tahap tersebut mencerminkan kontrak metrik, kontrak perankingan, dan kontrak output, sehingga tingkat agregasi dan jumlah baris mereka dapat diperiksa secara independen.

Langkah 5: Buktikan Kebenaran Alih-alih Hanya Menampilkan Sintaksis

Untuk kategori mana pun, tahap pertama membuat satu total per produk. PARTITION BY category_id mengecualikan setiap kategori lain dari perbandingan, dan ORDER BY revenue DESC mendefinisikan total yang sama sebagai satu kelompok sejawat. DENSE_RANK menomori kelompok sejawat secara berurutan tanpa celah, sehingga <= 3 memilih tepat tiga total berbeda tertinggi beserta setiap produk yang termasuk di dalamnya.

ORDER BY akhir tidak memengaruhi peringkat; ia hanya mengurutkan baris yang dikembalikan. Perbedaan ini penting: pengurutan di dalam jendela mendefinisikan peringkat bisnis, sedangkan pengurutan di akhir mendefinisikan presentasi yang dapat diulang. Keduanya mungkin menggunakan kunci yang berbeda.

Langkah 6: Kaitkan Klaim Performa dengan Bukti

Jika R item baris yang memenuhi syarat menjadi G kelompok kategori-produk, pekerjaan logis memindai dan mengagregasikan R baris, lalu mengurutkan G baris di dalam kategori. Pengurutan dapat digambarkan sebagai jumlah dari G_c log G_c di seluruh partisi kategori, tetapi PostgreSQL mungkin memilih agregasi hash, agregasi sort, eksekusi paralel, atau spill ke disk. Ekspresi tersebut bukan janji tentang rencana fisik.

Gunakan EXPLAIN (ANALYZE, BUFFERS) pada replika skala produksi untuk memeriksa baris yang difilter, strategi join, baris agregat, memori sort, dan file sementara. orders(status, ordered_at, order_id) dan order_items(order_id) mungkin membantu pemfilteran dan penggabungan, tergantung pada selektivitas status, distribusi, dan partisi. Partisi waktu dapat memangkas tabel fakta yang besar. Untuk laporan yang sering, pertimbangkan agregat kategori-produk harian yang direkonsiliasi. Pra-agregasi menukar kesegaran, backfill, dan kompleksitas koreksi dengan kecepatan kueri; "buat materialized view" bukan merupakan desain yang lengkap.

Langkah 7: Buktikan Semantik dengan Data Kecil dan Biaya dengan Data Besar

Fixture kebenaran minimal mencakup kategori 10 dengan pendapatan 100, 100, 90, 80, 70; harapkan empat produk pertama dengan peringkat 1, 1, 2, 3. Kategori 20 hanya memiliki 50, 40; harapkan keduanya. Sertakan satu pesanan yang dibatalkan, yang tidak boleh berkontribusi, dan satu pesanan tepat pada 2026-07-01 00:00:00+00, yang harus dikecualikan.

Tambahkan beberapa pesanan dan item baris untuk produk yang sama guna memastikannya menghasilkan satu total. Berikan ID produk yang berbeda total yang sama untuk memverifikasi urutan akhir yang stabil tetapi peringkat yang sama. Validasi skala kemudian memeriksa partisi yang dipindai, kardinalitas agregat, spill sort, waktu berlalu, dan sumber daya puncak. Output yang benar dan biaya yang dapat diterima adalah dua bukti yang terpisah.

Contoh Jawaban Berkualitas Tinggi

"Saya pertama-tama akan mengklarifikasi perilaku seri, atribusi waktu, dan status yang memenuhi syarat. Persyaratannya adalah tiga tingkat pendapatan berbeda tertinggi per kategori dengan setiap seri di tingkat ketiga, sehingga saya akan menggunakan DENSE_RANK; saya hanya akan menggunakan ROW_NUMBER untuk tepat tiga baris.

Tingkat target adalah satu baris per kategori dan produk, sementara tingkat sumber adalah satu item pesanan. CTE pertama saya oleh karena itu menggabungkan pesanan ke item, menyimpan pesanan COMPLETED di UTC Q2, dan mengagregasikan SUM(quantity * unit_price) berdasarkan category_id, product_id. Saya menggunakan 1 April inklusif dan 1 Juli eksklusif agar kuartal yang berdekatan tidak tumpang tindih.

CTE kedua menerapkan DENSE_RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC). Saya tidak menambahkan product_id ke jendela tersebut karena akan memisahkan sejawat berpendapatan sama. PostgreSQL tidak dapat memfilter hasil jendela di klausa WHERE pada tingkat kueri yang sama, sehingga kueri luar menerapkan revenue_rank <= 3 dan kemudian mengurutkan berdasarkan kategori, peringkat, dan ID produk untuk tampilan yang stabil.

Pendapatan 100, 100, 90, 80 menjadi peringkat 1, 1, 2, 3, sehingga keempat produk dikembalikan. Saya juga akan menguji pesanan yang dibatalkan, batas kanan kuartal, item baris berulang untuk satu produk, dan kategori dengan kurang dari tiga tingkat. Pada skala besar, saya akan mengukur seberapa jauh pemfilteran dan agregasi mengurangi R baris sumber sebelum menggunakan rencana eksekusi untuk membenarkan pemangkasan partisi, indeks, penyetelan spill, atau pra-agregasi."

Jawaban ini menetapkan semantik bisnis sebelum sintaksis, kemudian menyediakan struktur kueri, bukti kebenaran, dan jalur performa yang terukur. Ini tidak memperlakukan nama fungsi atau saran indeks yang belum diuji sebagai jawaban.

Kesalahan Umum

• Meranking item pesanan secara langsung → satu produk menempati beberapa posisi dan tingkat output salah → agregasikan berdasarkan kategori dan produk sebelum meranking.
• Menggunakan ORDER BY ... LIMIT 3 global → ia mengembalikan tiga baris untuk seluruh set data → partisi perankingan berdasarkan category_id.
• Memilih ROW_NUMBER tanpa kontrak seri → produk yang seri dapat dipotong secara sewenang-wenang → definisikan terlebih dahulu tiga baris, posisi kompetisi, atau tiga nilai yang berbeda.
• Menambahkan product_id ke pengurutan DENSE_RANK → pendapatan yang sama berhenti menjadi sejawat → beri peringkat hanya pada kunci peringkat bisnis dan stabilkan tampilan di ORDER BY akhir.
• Memfilter revenue_rank di WHERE yang sama → hasil jendela tidak ada pada tahap logis tersebut → beri peringkat di CTE atau subkueri dan filter di luar.
• Mengakhiri kuartal pada 30 Juni 23:59:59 → presisi timestamp yang lebih tinggi dapat terlewat dan periode yang berdekatan menjadi canggung → gunakan [start, next_start).
• Menggabungkan dimensi kategori saat ini tanpa semantik historis → rekategorisasi produk menulis ulang riwayat → gunakan snapshot waktu-pesanan atau dimensi bertanggal-efektif dan nyatakan kontraknya.
• Hanya menawarkan indeks → selektivitas rendah mungkin membuatnya tidak berguna, dan tidak dapat memperbaiki tingkat yang salah → ukur kardinalitas, rencana, spill, dan bottleneck yang sebenarnya.
• Mengakumulasikan uang dalam floating point → pembulatan dapat mengubah kelompok seri → pertahankan tipe numerik yang tepat dan definisikan aturan mata uang dan pendapatan bersih.

Pertanyaan Lanjutan dan Tanggapan

Lanjutan 1: Bagaimana jika produk memerlukan tepat tiga baris per kategori?

Gunakan ROW_NUMBER dan definisikan urutan sekunder deterministik yang dapat dijelaskan untuk pendapatan yang sama, seperti product_id ASC atau aturan unit-terjual atau tanggal-peluncuran yang diminta secara eksplisit. Aturan tersebut dengan sengaja memecah seri, sehingga termasuk dalam kontrak output. Kategori dengan kurang dari tiga produk tetap mengembalikan lebih sedikit baris kecuali placeholder diminta secara eksplisit.

Lanjutan 2: Bagaimana jika "tiga teratas" menggunakan perankingan kompetisi?

Gunakan RANK. Pendapatan 100, 100, 90, 80 menerima 1, 1, 3, 4, dan memfilter pada 3 mengembalikan tingkat 100 dan 90. Ini mempertahankan celah setelah seri, cocok dengan "produk berikutnya adalah ketiga." Itu berbeda dari meminta tiga nilai berbeda teratas dengan DENSE_RANK.

Lanjutan 3: Bisakah Anda menghindari CTE dan memfilter peringkat di HAVING?

Anda tidak dapat memfilter hasil jendela di HAVING atau WHERE pada tingkat kueri yang sama, karena fungsi jendela secara logis lebih belakangan. Subkueri yang setara berfungsi. Beberapa gudang data mendukung QUALIFY, tetapi PostgreSQL 18 tidak. CTE di sini membuat batas agregasi, perankingan, dan pemfilteran menjadi eksplisit.

Lanjutan 4: Bagaimana jika pengembalian dana terjadi di kuartal berikutnya?

Definisikan kontrak akuntansi terlebih dahulu. Laporan atribusi-pesanan dapat menyatakan ulang kuartal pesanan asli; laporan arus kas dapat mencatat peristiwa negatif di kuartal pengembalian dana. Keduanya memerlukan waktu peristiwa dan tabel fakta yang berbeda. Jangan mengurangkan tabel pengembalian dana yang tidak memiliki semantik jumlah dan waktu peristiwa; modelkan fakta pesanan dan pengembalian dana yang dapat ditelusuri serta nyatakan kebijakan penyataan ulang atau penyesuaian.

Lanjutan 5: Bagaimana Anda menangani satu miliar item baris kuartalan?

Pangkas partisi tanggal dan filter status sebelum meranking, lalu periksa indeks kunci join, paralelisme agregat, dan spill sort. Jika laporan sering dan dapat mentolerir penundaan, pertahankan agregat kategori-produk harian yang tepat dan jumlahkan untuk kuartal tersebut. Pesanan terlambat, pengembalian dana, dan koreksi memerlukan backfill dan rekonsiliasi idempoten ke fakta mentah. Hanya rencana eksekusi dan beban kerja yang representatif yang dapat memilih di antara pengindeksan, partisi, dan pra-agregasi.

Lanjutan 6: Bagaimana Anda mendeteksi join yang menggandakan pendapatan?

Periksa tingkat agregasi dan konservasi di setiap tahap: apakah jumlah baris di seluruh join item yang memenuhi syarat sesuai dengan kardinalitas yang diharapkan, apakah orders.order_id unik, dan apakah jumlah agregat produk sama dengan jumlah jumlah item yang difilter. Tambahkan contoh tandingan dengan dua item dalam satu pesanan dan kunci dimensi duplikat. Jika dimensi tidak unik, pilih satu versi bertanggal-efektif sebelum bergabung; DISTINCT akhir hanya menyembunyikan kontribusi yang terduplikasi.