SQL Tingkat Lanjut untuk Wawancara Data Analyst: Subquery, Pivot, dan Optimasi Query 2026

Dalam dunia analisis data modern, kemampuan menulis query SQL yang kompleks dan efisien menjadi salah satu kompetensi yang paling sering diuji dalam wawancara kerja. Banyak kandidat yang mampu menulis query dasar seperti SELECT, JOIN, dan GROUP BY, namun hanya sedikit yang benar-benar menguasai teknik-teknik lanjutan seperti subquery berkorelasi, pivot query, dan optimasi performa melalui analisis execution plan. Artikel ini membahas secara mendalam berbagai konsep SQL tingkat lanjut yang kerap muncul dalam sesi wawancara untuk posisi Data Analyst, lengkap dengan contoh kode yang dapat langsung dipraktikkan.

Correlated vs Regular Subqueries

Pemahaman mendalam tentang perbedaan antara subquery reguler dan subquery berkorelasi merupakan fondasi penting yang wajib dikuasai oleh setiap calon Data Analyst. Kedua jenis subquery ini memiliki mekanisme eksekusi yang sangat berbeda, dan pemilihan yang tepat dapat berdampak signifikan terhadap performa query secara keseluruhan.

Subquery reguler dieksekusi satu kali secara independen, kemudian hasilnya digunakan oleh query utama. Pendekatan ini efisien karena database engine hanya perlu mengevaluasi subquery tersebut sekali saja.

-- regular_subquery_threshold.sql
-- Find all orders above the average order value
SELECT
  order_id,
  customer_id,
  total_amount
FROM orders
WHERE total_amount > (
  -- Executes once, returns a single scalar value
  SELECT AVG(total_amount)
  FROM orders
);

Sebaliknya, subquery berkorelasi mereferensikan kolom dari query utama, sehingga harus dievaluasi ulang untuk setiap baris yang diproses. Hal ini menjadikannya jauh lebih intensif secara komputasi, terutama pada tabel berukuran besar.

-- correlated_subquery_department.sql
-- Employees earning above their department average
SELECT
  e.employee_id,
  e.name,
  e.department,
  e.salary
FROM employees e
WHERE e.salary > (
  -- Re-evaluated for each employee's department
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department = e.department
);

Pada contoh di atas, subquery AVG(e2.salary) dihitung ulang untuk setiap departemen yang ditemui pada tabel employees. Jika terdapat 10.000 karyawan dan 50 departemen, database harus menjalankan subquery tersebut hingga 10.000 kali dalam skenario terburuk.

Solusi yang lebih optimal adalah menggunakan Common Table Expression (CTE) untuk menghitung rata-rata per departemen terlebih dahulu, kemudian melakukan JOIN dengan tabel utama. Pendekatan ini hanya memerlukan satu kali pemindaian data.

-- optimized_with_cte.sql
-- Same result, single pass over the data
WITH dept_avg AS (
  SELECT
    department,
    AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT
  e.employee_id,
  e.name,
  e.department,
  e.salary
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_salary;

Dalam konteks wawancara, kemampuan untuk mengidentifikasi kapan subquery berkorelasi dapat direfaktor menjadi CTE atau JOIN menunjukkan pemahaman yang matang terhadap optimasi query.

Pola-Pola Subquery yang Sering Diujikan

Selain perbedaan dasar antara subquery reguler dan berkorelasi, terdapat beberapa pola subquery spesifik yang kerap muncul dalam sesi wawancara teknis.

EXISTS vs IN

Pola EXISTS dan IN sering kali menghasilkan output yang sama, namun perilaku dan performanya bisa sangat berbeda. Klausa EXISTS berhenti memproses segera setelah menemukan satu baris yang cocok (short-circuit evaluation), sementara IN pada umumnya mengumpulkan seluruh hasil subquery terlebih dahulu sebelum melakukan pencocokan.

-- exists_vs_in.sql
-- Customers who placed at least one order in 2026 (EXISTS — preferred)
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.order_date >= '2026-01-01'
);

-- Equivalent with IN (slower on large datasets)
SELECT customer_id, name
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  WHERE order_date >= '2026-01-01'
);

Sebagai pedoman umum, EXISTS lebih direkomendasikan ketika subquery menghasilkan dataset yang besar, sedangkan IN masih dapat diterima untuk dataset yang relatif kecil dan tidak mengandung nilai NULL.

Scalar Subquery dalam SELECT

Teknik ini menempatkan subquery di dalam klausa SELECT untuk menghitung nilai agregat yang berkaitan dengan setiap baris hasil. Meskipun elegan secara sintaks, perlu diperhatikan bahwa scalar subquery dalam SELECT pada dasarnya bersifat berkorelasi dan dapat menimbulkan masalah performa pada tabel besar.

-- scalar_subquery_select.sql
-- Each product with its category's total revenue
SELECT
  p.product_id,
  p.product_name,
  p.category,
  (
    SELECT SUM(oi.quantity * oi.unit_price)
    FROM order_items oi
    JOIN products p2 ON oi.product_id = p2.product_id
    WHERE p2.category = p.category
  ) AS category_total_revenue
FROM products p;

Dalam wawancara, kandidat yang mampu menulis query seperti ini sekaligus menjelaskan implikasi performanya akan mendapatkan nilai lebih tinggi dibandingkan yang hanya fokus pada kebenaran output.

Pivot Query dengan Conditional Aggregation

Kemampuan mengubah data dari format baris menjadi format kolom (pivoting) merupakan keterampilan yang sangat dihargai dalam analisis data. Teknik conditional aggregation menggunakan kombinasi fungsi agregat dengan CASE WHEN merupakan pendekatan yang paling portabel dan banyak didukung oleh berbagai sistem database.

Contoh berikut menunjukkan cara membuat laporan pendapatan bulanan dalam format pivot, dengan setiap bulan ditampilkan sebagai kolom terpisah.

-- pivot_monthly_revenue.sql
-- Monthly revenue pivot: one row per product, one column per month
SELECT
  product_id,
  product_name,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 1
    THEN quantity * unit_price ELSE 0 END) AS jan_revenue,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 2
    THEN quantity * unit_price ELSE 0 END) AS feb_revenue,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 3
    THEN quantity * unit_price ELSE 0 END) AS mar_revenue,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 4
    THEN quantity * unit_price ELSE 0 END) AS apr_revenue,
  -- Repeat for remaining months
  SUM(quantity * unit_price) AS total_revenue
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2026-01-01'
GROUP BY product_id, product_name
ORDER BY total_revenue DESC;

Pola ini juga sangat berguna untuk analisis perilaku pengguna. Contoh berikut memperlihatkan cara menghitung distribusi sesi berdasarkan tipe perangkat, termasuk perhitungan persentase.

-- pivot_user_activity.sql
-- User activity: sessions by day of week and device type
SELECT
  user_id,
  COUNT(CASE WHEN device_type = 'mobile' THEN 1 END) AS mobile_sessions,
  COUNT(CASE WHEN device_type = 'desktop' THEN 1 END) AS desktop_sessions,
  COUNT(CASE WHEN device_type = 'tablet' THEN 1 END) AS tablet_sessions,
  ROUND(
    COUNT(CASE WHEN device_type = 'mobile' THEN 1 END) * 100.0 / COUNT(*),
    1
  ) AS mobile_pct
FROM user_sessions
WHERE session_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY user_id
HAVING COUNT(*) >= 5  -- Only users with meaningful activity
ORDER BY mobile_pct DESC;

Perhatikan penggunaan klausa HAVING untuk memfilter pengguna dengan aktivitas minimal. Detail semacam ini menunjukkan kepekaan analitis yang dicari oleh pewawancara.

Dynamic Pivot dengan CROSSTAB

Untuk pengguna PostgreSQL, fungsi CROSSTAB dari ekstensi tablefunc menyediakan cara yang lebih terstruktur untuk melakukan pivot data. Pendekatan ini sangat berguna ketika kategori pivot sudah diketahui sebelumnya dan bersifat tetap, seperti kuartal dalam setahun.

-- crosstab_dynamic_pivot.sql
-- Enable the extension (once per database)
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- Revenue by product and quarter using CROSSTAB
SELECT *
FROM crosstab(
  $$
    SELECT
      product_name,
      'Q' || EXTRACT(QUARTER FROM order_date)::TEXT AS quarter,
      SUM(quantity * unit_price) AS revenue
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE EXTRACT(YEAR FROM o.order_date) = 2026
    GROUP BY product_name, quarter
    ORDER BY product_name, quarter
  $$,
  $$ VALUES ('Q1'), ('Q2'), ('Q3'), ('Q4') $$
) AS pivot_table(
  product_name TEXT,
  q1_revenue NUMERIC,
  q2_revenue NUMERIC,
  q3_revenue NUMERIC,
  q4_revenue NUMERIC
);

Perlu dicatat bahwa CROSSTAB merupakan fitur spesifik PostgreSQL dan tidak tersedia di semua sistem database. Dalam wawancara, menyebutkan perbedaan antara pendekatan portabel (conditional aggregation) dan pendekatan spesifik database (CROSSTAB) menunjukkan keluasan pengetahuan kandidat.

EXPLAIN Plans dan Analisis Biaya Query

Kemampuan membaca dan menginterpretasikan execution plan merupakan keterampilan yang membedakan Data Analyst biasa dari yang benar-benar kompeten. Perintah EXPLAIN ANALYZE tidak hanya menampilkan rencana eksekusi query, tetapi juga menjalankan query tersebut secara aktual dan melaporkan waktu serta sumber daya yang digunakan pada setiap tahap.

-- explain_analyze_example.sql
-- Analyze a slow query to identify bottlenecks
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
  c.customer_id,
  c.name,
  COUNT(o.order_id) AS order_count,
  SUM(o.total_amount) AS lifetime_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2025-01-01'
GROUP BY c.customer_id, c.name
HAVING SUM(o.total_amount) > 1000
ORDER BY lifetime_value DESC;

Beberapa elemen kunci yang perlu diperhatikan dalam output EXPLAIN ANALYZE antara lain:

• Seq Scan vs Index Scan: Sequential scan membaca seluruh tabel, sementara index scan hanya mengakses baris yang relevan. Seq scan pada tabel besar merupakan indikasi kuat bahwa indeks yang tepat belum dibuat.
• Nested Loop vs Hash Join: Nested loop efisien untuk join antara tabel kecil dan besar (dengan indeks), sedangkan hash join lebih cocok untuk join antara dua tabel besar.
• Actual Time vs Estimated Rows: Perbedaan besar antara estimasi dan aktual menandakan statistik tabel yang sudah usang dan perlu diperbarui dengan ANALYZE.
• Buffers (shared hit/read): Menunjukkan berapa banyak data yang dibaca dari cache versus disk. Rasio cache hit yang rendah mengindikasikan memori yang tidak memadai atau working set yang terlalu besar.

Strategi Indexing untuk Performa Optimal

Setelah mengidentifikasi bottleneck melalui EXPLAIN ANALYZE, langkah selanjutnya adalah menerapkan strategi indexing yang tepat. Pembuatan indeks yang efektif memerlukan pemahaman tentang pola akses data yang digunakan oleh query.

-- indexing_strategies.sql
-- Index on order_date: filters a small percentage of rows
CREATE INDEX idx_orders_date ON orders (order_date);

-- Composite index for queries filtering on both columns
CREATE INDEX idx_orders_customer_date
  ON orders (customer_id, order_date);

-- Covering index: includes columns needed in SELECT
-- Enables Index Only Scan (no table access)
CREATE INDEX idx_orders_covering
  ON orders (customer_id, order_date)
  INCLUDE (total_amount, status);

Beberapa prinsip penting dalam strategi indexing:

• Urutan kolom dalam composite index sangat penting. Kolom yang digunakan untuk filter kesetaraan (=) sebaiknya ditempatkan sebelum kolom yang digunakan untuk range filter (>=, BETWEEN).
• Covering index dengan klausa INCLUDE memungkinkan database mengembalikan hasil langsung dari indeks tanpa mengakses tabel utama, menghasilkan Index Only Scan yang sangat efisien.
• Partial index hanya mengindeks subset data yang memenuhi kondisi tertentu, menghasilkan indeks yang lebih kecil dan lebih cepat.

-- partial_index.sql
-- Only index active orders (smaller, faster index)
CREATE INDEX idx_orders_active
  ON orders (customer_id, order_date)
  WHERE status = 'active';

-- Only index recent data for dashboard queries
CREATE INDEX idx_orders_recent
  ON orders (order_date)
  WHERE order_date >= '2026-01-01';

Partial index sangat bermanfaat untuk skenario di mana query secara konsisten hanya mengakses subset data tertentu, misalnya hanya pesanan yang berstatus aktif atau data dari periode waktu terkini.

Anti-Pattern Query yang Harus Dihindari

Mengetahui apa yang sebaiknya tidak dilakukan sama pentingnya dengan mengetahui pendekatan yang benar. Bagian ini membahas dua anti-pattern paling umum yang sering ditemui dalam kode SQL produksi.

Fungsi pada Kolom Terindeks

Kesalahan ini merupakan salah satu penyebab paling sering dari query lambat yang sulit didiagnosis. Ketika sebuah fungsi diterapkan pada kolom dalam klausa WHERE, database tidak dapat menggunakan indeks pada kolom tersebut.

-- anti_patterns.sql
-- BAD: function on indexed column disables the index
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2026;

-- GOOD: range comparison uses the index
SELECT * FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date < '2027-01-01';

Kedua query menghasilkan output yang identik, namun versi pertama memaksa database memindai seluruh tabel, sedangkan versi kedua dapat memanfaatkan indeks pada kolom order_date secara penuh. Pada tabel dengan jutaan baris, perbedaan waktu eksekusi bisa mencapai ratusan kali lipat.

Jebakan NOT IN dengan NULL

Anti-pattern ini bersifat lebih berbahaya karena tidak hanya memengaruhi performa, tetapi juga dapat menghasilkan hasil yang salah secara diam-diam. Jika subquery pada NOT IN menghasilkan setidaknya satu nilai NULL, maka seluruh klausa NOT IN akan mengembalikan hasil kosong karena perbandingan apa pun dengan NULL menghasilkan UNKNOWN, bukan TRUE atau FALSE.

-- not_in_null_trap.sql
-- BAD: returns empty if any customer_id is NULL in subquery
SELECT * FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id FROM orders
);

-- GOOD: NOT EXISTS handles NULLs correctly
SELECT * FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
);

Penggunaan NOT EXISTS tidak hanya lebih aman terhadap nilai NULL, tetapi pada sebagian besar database modern juga menghasilkan execution plan yang lebih efisien dibandingkan NOT IN.

Kesimpulan

Berikut rangkuman poin-poin utama yang telah dibahas dalam artikel ini:

• Subquery berkorelasi dievaluasi ulang untuk setiap baris pada query utama dan sebaiknya direfaktor menjadi CTE atau JOIN ketika memungkinkan untuk meningkatkan performa.
• EXISTS lebih direkomendasikan daripada IN untuk pengecekan keberadaan data, terutama pada dataset besar, karena mekanisme short-circuit evaluation yang dimilikinya.
• Conditional aggregation dengan CASE WHEN merupakan teknik pivot yang paling portabel dan didukung oleh hampir semua sistem database relasional.
• EXPLAIN ANALYZE adalah alat utama untuk mendiagnosis bottleneck performa, dan kemampuan membaca output-nya merupakan kompetensi kritis bagi seorang Data Analyst.
• Strategi indexing yang efektif meliputi composite index, covering index, dan partial index, masing-masing dirancang untuk pola akses data yang berbeda.
• Anti-pattern seperti penggunaan fungsi pada kolom terindeks dan jebakan NOT IN dengan NULL harus dikenali dan dihindari secara konsisten.
• Penguasaan SQL tingkat lanjut bukan hanya tentang menulis query yang benar, tetapi juga tentang menulis query yang efisien, aman, dan mudah dipelihara dalam lingkungan produksi.