TL;DR
50 soal interview SQL dibagi 3 level: 15 Basic (SELECT, WHERE, ORDER BY), 20 Intermediate (JOIN, GROUP BY, Subquery), 15 Advanced (Window Functions, CTE, Optimization). Semua pakai konteks perusahaan tech Indonesia.
Mau apply jadi Data Analyst, Data Scientist, atau Data Engineer di perusahaan tech Indonesia? SQL pasti jadi salah satu skill yang di-test. Hampir semua perusahaan kayak Gojek, Tokopedia, Shopee, Traveloka, dan startup lainnya punya technical interview SQL.
Aku udah compile 50 pertanyaan yang sering muncul di interview, dari pengalaman sendiri dan temen-temen yang udah kerja di berbagai perusahaan. Semua contoh pakai konteks bisnis Indonesia biar lebih relatable.
Untuk semua soal, kita pakai dataset e-commerce dengan 4 tabel:
Tabel: users
| user_id | nama | kota | tanggal_daftar |
|---|---|---|---|
| 1 | Budi Santoso | Jakarta | 2024-01-15 |
| 2 | Siti Rahayu | Bandung | 2024-02-20 |
| 3 | Andi Wijaya | Surabaya | 2024-01-10 |
Tabel: orders
| order_id | user_id | total_amount | order_date | status |
|---|---|---|---|---|
| 101 | 1 | 150000 | 2024-03-01 | completed |
| 102 | 1 | 250000 | 2024-03-15 | completed |
| 103 | 2 | 100000 | 2024-03-10 | cancelled |
Tabel: products
| product_id | nama_produk | kategori | harga |
|---|---|---|---|
| 1 | Kaos Polos | Fashion | 75000 |
| 2 | Sepatu Sneakers | Fashion | 450000 |
| 3 | Headphone | Elektronik | 250000 |
Tabel: order_items
| id | order_id | product_id | quantity | price |
|---|---|---|---|---|
| 1 | 101 | 1 | 2 | 75000 |
| 2 | 101 | 3 | 1 | 250000 |
| 3 | 102 | 2 | 1 | 450000 |
Pertanyaan: Tampilkan semua data dari tabel users.
SELECT * FROM users;
Pertanyaan: Tampilkan nama dan kota dari semua users.
SELECT nama, kota FROM users;
Pertanyaan: Tampilkan semua users yang tinggal di Jakarta.
SELECT * FROM users WHERE kota = 'Jakarta';
Pertanyaan: Tampilkan orders yang statusnya 'completed' DAN total_amount lebih dari 200000.
SELECT * FROM orders
WHERE status = 'completed' AND total_amount > 200000;
Pertanyaan: Tampilkan semua products, urutkan dari harga termahal.
SELECT * FROM products ORDER BY harga DESC;
Pertanyaan: Tampilkan 5 orders terbaru.
SELECT * FROM orders ORDER BY order_date DESC LIMIT 5;
Pertanyaan: Tampilkan daftar kota unik dari tabel users.
SELECT DISTINCT kota FROM users;
Pertanyaan: Tampilkan users yang tinggal di Jakarta atau Bandung.
SELECT * FROM users WHERE kota IN ('Jakarta', 'Bandung');
SELECT * FROM users WHERE kota = 'Jakarta' OR kota = 'Bandung';
Pertanyaan: Tampilkan products dengan harga antara 100000 dan 300000.
SELECT * FROM products WHERE harga BETWEEN 100000 AND 300000;
Pertanyaan: Tampilkan users yang namanya diawali huruf 'S'.
SELECT * FROM users WHERE nama LIKE 'S%';
Pertanyaan: Tampilkan orders yang statusnya NULL.
SELECT * FROM orders WHERE status IS NULL;
Pertanyaan: Hitung jumlah total users.
SELECT COUNT(*) AS total_users FROM users;
Pertanyaan: Hitung total revenue dari semua orders yang completed.
SELECT SUM(total_amount) AS total_revenue
FROM orders
WHERE status = 'completed';
Pertanyaan: Hitung rata-rata harga produk.
SELECT AVG(harga) AS rata_rata_harga FROM products;
Pertanyaan: Tampilkan nama user dan kota, dengan nama kolom "Nama Lengkap" dan "Domisili".
SELECT
nama AS "Nama Lengkap",
kota AS "Domisili"
FROM users;
Pertanyaan: Tampilkan nama user beserta order_id dan total_amount dari orders mereka.
SELECT u.nama, o.order_id, o.total_amount
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;
Pertanyaan: Tampilkan semua users beserta orders mereka (termasuk user yang belum pernah order).
SELECT u.nama, o.order_id, o.total_amount
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;
Pertanyaan: Tampilkan nama user dan total order mereka, hanya untuk orders yang completed.
SELECT u.nama, o.order_id, o.total_amount
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE o.status = 'completed';
Pertanyaan: Tampilkan order_id, nama user, nama produk, dan quantity untuk setiap item yang diorder.
SELECT
o.order_id,
u.nama AS nama_user,
p.nama_produk,
oi.quantity
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id;
Pertanyaan: Hitung jumlah orders per user.
SELECT user_id, COUNT(*) AS jumlah_order
FROM orders
GROUP BY user_id;
Pertanyaan: Tampilkan nama user dan total spending mereka.
SELECT u.nama, SUM(o.total_amount) AS total_spending
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.user_id, u.nama;
Pertanyaan: Tampilkan user yang punya total spending lebih dari 200000.
SELECT u.nama, SUM(o.total_amount) AS total_spending
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.user_id, u.nama
HAVING SUM(o.total_amount) > 200000;
Pertanyaan: Tampilkan users yang pernah order dengan total_amount di atas rata-rata.
SELECT DISTINCT u.nama
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE o.total_amount > (SELECT AVG(total_amount) FROM orders);
Pertanyaan: Tampilkan rata-rata jumlah order per user.
SELECT AVG(order_count) AS avg_orders_per_user
FROM (
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
) AS user_orders;
Pertanyaan: Kategorikan users berdasarkan total spending: 'Bronze' (<100k), 'Silver' (100k-300k), 'Gold' (>300k).
SELECT
u.nama,
SUM(o.total_amount) AS total_spending,
CASE
WHEN SUM(o.total_amount) < 100000 THEN 'Bronze'
WHEN SUM(o.total_amount) BETWEEN 100000 AND 300000 THEN 'Silver'
ELSE 'Gold'
END AS tier
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id AND o.status = 'completed'
GROUP BY u.user_id, u.nama;
Pertanyaan: Tampilkan orders yang terjadi di bulan Maret 2024.
-- PostgreSQL
SELECT * FROM orders
WHERE EXTRACT(MONTH FROM order_date) = 3
AND EXTRACT(YEAR FROM order_date) = 2024;
-- MySQL
SELECT * FROM orders
WHERE MONTH(order_date) = 3 AND YEAR(order_date) = 2024;
-- Alternatif universal
SELECT * FROM orders
WHERE order_date >= '2024-03-01' AND order_date < '2024-04-01';
Pertanyaan: Tampilkan nama user dan total spending, ganti NULL dengan 0.
SELECT
u.nama,
COALESCE(SUM(o.total_amount), 0) AS total_spending
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id AND o.status = 'completed'
GROUP BY u.user_id, u.nama;
Pertanyaan: Cari pasangan produk yang harganya sama.
SELECT
p1.nama_produk AS produk_1,
p2.nama_produk AS produk_2,
p1.harga
FROM products p1
INNER JOIN products p2 ON p1.harga = p2.harga
WHERE p1.product_id < p2.product_id;
Pertanyaan: Gabungkan daftar nama user dan nama produk dalam satu kolom.
SELECT nama AS nama, 'user' AS tipe FROM users
UNION
SELECT nama_produk AS nama, 'produk' AS tipe FROM products;
Pertanyaan: Tampilkan users yang pernah melakukan order.
SELECT * FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.user_id
);
Pertanyaan: Tampilkan users yang BELUM pernah order.
SELECT * FROM users
WHERE user_id NOT IN (SELECT DISTINCT user_id FROM orders);
SELECT u.* FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.order_id IS NULL;
Pertanyaan: Tampilkan nama user dalam huruf kapital dan 3 huruf pertama kotanya.
SELECT
UPPER(nama) AS nama_kapital,
LEFT(kota, 3) AS kode_kota
FROM users;
Pertanyaan: Hitung berapa hari sejak user mendaftar sampai order pertama mereka.
SELECT
u.nama,
u.tanggal_daftar,
MIN(o.order_date) AS first_order,
MIN(o.order_date) - u.tanggal_daftar AS days_to_first_order
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.nama, u.tanggal_daftar;
Pertanyaan: Hitung persentase orders yang completed vs total orders.
SELECT
ROUND(
COUNT(CASE WHEN status = 'completed' THEN 1 END) * 100.0 / COUNT(*),
2
) AS completion_rate
FROM orders;
Pertanyaan: Tampilkan produk termahal di setiap kategori.
SELECT p1.*
FROM products p1
WHERE p1.harga = (
SELECT MAX(p2.harga)
FROM products p2
WHERE p2.kategori = p1.kategori
);
Pertanyaan: Beri ranking users berdasarkan total spending mereka.
SELECT
u.nama,
SUM(o.total_amount) AS total_spending,
ROW_NUMBER() OVER (ORDER BY SUM(o.total_amount) DESC) AS ranking
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.user_id, u.nama;
Pertanyaan: Beri ranking produk berdasarkan harga. Jelaskan perbedaan RANK dan DENSE_RANK.
SELECT
nama_produk,
harga,
RANK() OVER (ORDER BY harga DESC) AS rank_biasa,
DENSE_RANK() OVER (ORDER BY harga DESC) AS dense_rank
FROM products;
Pertanyaan: Untuk setiap order, tampilkan total_amount order sebelumnya dari user yang sama.
SELECT
user_id,
order_id,
order_date,
total_amount,
LAG(total_amount) OVER (PARTITION BY user_id ORDER BY order_date) AS prev_order_amount
FROM orders;
Pertanyaan: Hitung running total revenue per tanggal.
SELECT
order_date,
total_amount,
SUM(total_amount) OVER (ORDER BY order_date) AS running_total
FROM orders
WHERE status = 'completed';
Pertanyaan: Hitung 3-day moving average dari daily revenue.
WITH daily_revenue AS (
SELECT
order_date,
SUM(total_amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY order_date
)
SELECT
order_date,
revenue,
AVG(revenue) OVER (
ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3day
FROM daily_revenue;
Pertanyaan: Gunakan CTE untuk mencari users dengan spending di atas rata-rata.
WITH user_spending AS (
SELECT
u.user_id,
u.nama,
SUM(o.total_amount) AS total_spending
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.user_id, u.nama
),
avg_spending AS (
SELECT AVG(total_spending) AS avg_spend FROM user_spending
)
SELECT us.*
FROM user_spending us, avg_spending
WHERE us.total_spending > avg_spending.avg_spend;
Pertanyaan: Generate deret angka 1 sampai 10 menggunakan Recursive CTE.
WITH RECURSIVE numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;
Pertanyaan: Hitung retention rate user berdasarkan bulan registrasi.
WITH user_cohort AS (
SELECT
user_id,
DATE_TRUNC('month', tanggal_daftar) AS cohort_month
FROM users
),
user_activity AS (
SELECT
o.user_id,
DATE_TRUNC('month', o.order_date) AS activity_month
FROM orders o
)
SELECT
uc.cohort_month,
ua.activity_month,
COUNT(DISTINCT uc.user_id) AS users
FROM user_cohort uc
LEFT JOIN user_activity ua ON uc.user_id = ua.user_id
GROUP BY uc.cohort_month, ua.activity_month
ORDER BY uc.cohort_month, ua.activity_month;
Pertanyaan: Buat pivot table: baris = kategori, kolom = bulan, value = total penjualan.
SELECT
p.kategori,
SUM(CASE WHEN EXTRACT(MONTH FROM o.order_date) = 1 THEN oi.quantity * oi.price ELSE 0 END) AS jan,
SUM(CASE WHEN EXTRACT(MONTH FROM o.order_date) = 2 THEN oi.quantity * oi.price ELSE 0 END) AS feb,
SUM(CASE WHEN EXTRACT(MONTH FROM o.order_date) = 3 THEN oi.quantity * oi.price ELSE 0 END) AS mar
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.order_id
GROUP BY p.kategori;
Pertanyaan: Hitung conversion rate dari user daftar → order pertama → order kedua.
WITH user_orders AS (
SELECT
user_id,
COUNT(*) AS order_count
FROM orders
GROUP BY user_id
)
SELECT
COUNT(DISTINCT u.user_id) AS total_users,
COUNT(DISTINCT uo.user_id) AS users_with_order,
COUNT(DISTINCT CASE WHEN uo.order_count >= 2 THEN uo.user_id END) AS users_with_2_orders,
ROUND(COUNT(DISTINCT uo.user_id) * 100.0 / COUNT(DISTINCT u.user_id), 2) AS conv_to_first_order,
ROUND(COUNT(DISTINCT CASE WHEN uo.order_count >= 2 THEN uo.user_id END) * 100.0 / COUNT(DISTINCT uo.user_id), 2) AS conv_to_second_order
FROM users u
LEFT JOIN user_orders uo ON u.user_id = uo.user_id;
Pertanyaan: Cari user yang punya email duplikat (asumsikan ada kolom email).
SELECT email, COUNT(*) AS count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
SELECT * FROM users
WHERE email IN (
SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1
);
Pertanyaan: Identifikasi periode consecutive days dimana ada order.
WITH dated_orders AS (
SELECT DISTINCT order_date,
order_date - ROW_NUMBER() OVER (ORDER BY order_date) * INTERVAL '1 day' AS grp
FROM orders
)
SELECT
MIN(order_date) AS start_date,
MAX(order_date) AS end_date,
COUNT(*) AS consecutive_days
FROM dated_orders
GROUP BY grp
ORDER BY start_date;
Pertanyaan: Query ini lambat. Gimana cara optimize-nya?
SELECT * FROM orders
WHERE YEAR(order_date) = 2024 AND user_id IN (SELECT user_id FROM users WHERE kota = 'Jakarta');
-- Optimized version
SELECT o.*
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id
WHERE o.order_date >= '2024-01-01'
AND o.order_date < '2025-01-01'
AND u.kota = 'Jakarta';
Pertanyaan: Apa yang dilihat dari EXPLAIN dan gimana interpretasinya?
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
Pertanyaan: Untuk setiap user, hitung: total orders, completed orders, cancelled orders, completion rate, dan average order value.
SELECT
u.nama,
COUNT(o.order_id) AS total_orders,
COUNT(CASE WHEN o.status = 'completed' THEN 1 END) AS completed_orders,
COUNT(CASE WHEN o.status = 'cancelled' THEN 1 END) AS cancelled_orders,
ROUND(
COUNT(CASE WHEN o.status = 'completed' THEN 1 END) * 100.0 / NULLIF(COUNT(o.order_id), 0),
2
) AS completion_rate,
ROUND(AVG(CASE WHEN o.status = 'completed' THEN o.total_amount END), 0) AS avg_order_value
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.nama
ORDER BY total_orders DESC;
Basic (Level 1) fokus ke SELECT, WHERE, ORDER BY, dan aggregate functions. Ini dasar yang wajib dikuasai duluan.
Intermediate (Level 2) fokus ke JOIN, GROUP BY, HAVING, dan subquery. Di sini mulai bisa analisis data yang lebih kompleks.
Advanced (Level 3) fokus ke Window Functions, CTE, dan optimization. Ini yang membedakan junior dan senior.
Praktek > Teori - Ga cukup baca doang, harus coba sendiri.
Pahami bisnis - Query yang bagus adalah query yang menjawab pertanyaan bisnis dengan efisien.
Kalau udah paham 50 soal ini, next step-nya:
- Window Functions SQL: ROW_NUMBER, RANK, DENSE_RANK
- Common Table Expression (CTE) di SQL
- Cohort Analysis dengan SQL
Mau latihan lebih? Cek NgulikSQL buat soal-soal dengan konteks Indonesia. Atau join komunitas Discord kita buat diskusi dan tanya jawab.
Good luck buat interview-nya.
Mau praktek langsung? Mulai latihan SQL gratis
Latihan interaktif, langsung di browser.
Cara pakai DATE_TRUNC di SQL buat memotong timestamp jadi awal bulan, minggu, atau jam. Kunci bikin laporan penjualan per periode yang rapi.
Cara mengisi tanggal kosong di time series SQL pakai generate_series dan LEFT JOIN biar grafik dan laporan gak bolong. Contoh penjualan harian UMKM.
Analisa per kuartal, hari kerja, atau musim jadi ribet kalau tiap query ngitung ulang atribut tanggal. Tabel kalender nyimpen semua atribut itu sekali, biar tinggal di-JOIN.