Veri

SQL görevleri

Live coding'de çıkabilecek 15 PostgreSQL görevi: window function, recursive CTE, anti-join, fan-out tuzağı, upsert, koşullu stok düşme ve index. Örnek e-ticaret veritabanını kendi PostgreSQL'inde kurup çözebilirsin.

Şema ve veriyi indir (seed.sql)

Yerelde çalıştırma

bash

docker run --name sql-lab -e POSTGRES_PASSWORD=lab -p 5432:5432 -d postgres:17
psql postgresql://postgres:lab@localhost:5432/postgres -f public/sql-lab-seed.sql

Tarihler kurulum anına göre (now()) üretildiği için sonuçlar her kurulumda tutarlı kalır.

01KolayORDER BY · LIMIT/OFFSET · DISTINCT

Isınma: en yüksek ikinci ürün fiyatı

Klasik bir açılış sorusu: ürünler arasındaki en yüksek ikinci fiyatı tek satır, tek kolon olarak döndür. Aynı fiyattan birden fazla ürün olsaydı da doğru çalışmalı (fiyatlar tekil olarak düşünülmeli).

İpucu 1

Fiyatları azalan sırala ve ilkini atla.

İpucu 2

Aynı fiyattan iki ürün varsa ikincisi de en yüksek fiyatı tekrarlar. Tekilleştirmek için DISTINCT kullan.

İpucu 3

Alternatifler: DISTINCT + ORDER BY + OFFSET 1 LIMIT 1, max(price) WHERE price < (SELECT max(price) ...) ya da dense_rank() = 2.

Çözüm ve açıklama

sql

SELECT DISTINCT price
  FROM products
 ORDER BY price DESC
OFFSET 1
 LIMIT 1;

Üç geçerli yol var:

DISTINCT + OFFSET 1 LIMIT 1: kısa ve okunur.
SELECT max(price) FROM products WHERE price < (SELECT max(price) FROM products): sadece "ikinci" için çalışır, N'inci için genellenmez.
dense_rank() OVER (ORDER BY price DESC) = 2: N'inci değer ve "her kategoride ikinci" gibi gruplu versiyonlar için en esnek yol.

Mülakatçının devam sorusu: "Hiç ikinci fiyat yoksa ne döner?" OFFSET versiyonu boş sonuç, max versiyonu NULL döner. Hangisini istediğini sor.

02KolayJOIN · GROUP BY

Satıcı bazında teslim edilmiş ciro

Her satıcının teslim edilmiş (DELIVERED) siparişlerden elde ettiği toplam ciroyu bul. Ciro = quantity * unit_price.

Kolonlar: seller_name, revenue. Sıra: ciroya göre azalan.

İpucu 1

Satıcıdan sipariş satırına giden yol: sellers → products → order_items → orders.

İpucu 2

Statü filtresi orders tablosunda; WHERE ile gruplamadan önce uygula.

İpucu 3

GROUP BY'a s.id'yi de ekle: aynı isimli iki satıcı olabilir.

Çözüm ve açıklama

sql

SELECT s.name AS seller_name,
       sum(oi.quantity * oi.unit_price) AS revenue
  FROM sellers s
  JOIN products p     ON p.seller_id = s.id
  JOIN order_items oi ON oi.product_id = p.id
  JOIN orders o       ON o.id = oi.order_id
 WHERE o.status = 'DELIVERED'
 GROUP BY s.id, s.name
 ORDER BY revenue DESC;

Join zincirini doğru kurmak ve filtreyi doğru yerde uygulamak bu sorunun özü. unit_price sipariş satırında tutulur, çünkü ürünün bugünkü fiyatı değil, sipariş anındaki fiyat geçerlidir. Bu bilinçli bir denormalizasyondur; mülakatta "neden products.price kullanmadın" diye sorulursa bunu söyle.

GROUP BY s.id, s.name: Sadece isme göre gruplamak, aynı isimli satıcıları birleştirir. PostgreSQL'de PK'ya göre gruplarsan aynı tablonun diğer kolonlarını da seçebilirsin (functional dependency).

03KolayAnti-join · NOT EXISTS

Hiç sipariş vermemiş müşteriler

Kayıtlı olup hiç sipariş vermemiş müşterileri bul (herhangi bir statüde siparişi olmayanlar).

Kolonlar: id, name. Sıra: id artan.

İpucu 1

Bu bir anti-join: "karşı tabloda eşleşmesi olmayanlar".

İpucu 2

LEFT JOIN orders ... WHERE o.id IS NULL ya da NOT EXISTS kullan.

İpucu 3

NOT IN kullanacaksan alt sorguda NULL olup olmadığını düşün.

Çözüm ve açıklama

sql

SELECT c.id, c.name
  FROM customers c
 WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
 ORDER BY c.id;

Üç yol:

NOT EXISTS: en güvenli ve genelde en verimli. PostgreSQL bunu "Hash Anti Join" ile çalıştırır.
LEFT JOIN orders o ON ... WHERE o.id IS NULL: eşdeğer.
WHERE id NOT IN (SELECT customer_id FROM orders): tuzak. Alt sorgu tek bir NULL dönerse hiç satır gelmez, çünkü x NOT IN (..., NULL) UNKNOWN olur. customer_id NOT NULL olduğu için burada çalışır, ama alışkanlık olarak NOT EXISTS yaz.

04OrtaGROUP BY · HAVING · COUNT DISTINCT

En az 3 farklı kategoriden alışveriş yapanlar

Çapraz satış ekibi, teslim edilmiş siparişlerinde en az 3 farklı kategoriden ürün almış müşterileri istiyor.

Kolonlar: customer_id, name, category_count. Sıra: category_count azalan, sonra customer_id artan.

İpucu 1

Müşteri → sipariş → sipariş satırı → ürün → category_id.

İpucu 2

Aynı kategoriden iki ürün tek sayılmalı: count(DISTINCT ...).

İpucu 3

Aggregate üzerindeki koşul WHERE'e değil HAVING'e yazılır.

Çözüm ve açıklama

sql

SELECT c.id AS customer_id,
       c.name,
       count(DISTINCT p.category_id) AS category_count
  FROM customers c
  JOIN orders o       ON o.customer_id = c.id AND o.status = 'DELIVERED'
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p     ON p.id = oi.product_id
 GROUP BY c.id, c.name
HAVING count(DISTINCT p.category_id) >= 3
 ORDER BY category_count DESC, c.id;

WHERE ve HAVING: WHERE satırları gruplamadan önce, HAVING grupları gruplamadan sonra filtreler. count(...) >= 3 bir aggregate olduğu için HAVING'e yazılır. SQL'in mantıksal sırası: FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

Not: Statü koşulunu JOIN'in ON kısmına ya da WHERE'e yazmak INNER JOIN'de aynı sonucu verir. LEFT JOIN'de ise fark yaratır (bkz. iade oranı görevi).

05OrtaVeri kalitesi · lower() · string_agg

Mükerrer müşteri hesapları

Destek ekibi, aynı e-postayla (büyük-küçük harf farkı gözetmeden) birden fazla hesap açıldığını fark etti. Bu e-postaları bul.

Kolonlar: email (küçük harfli), account_count, customer_ids (id'ler virgülle ve artan sırada birleşik metin, ör. 1,10). Sıra: email.

İpucu 1

Gruplama anahtarı lower(email) olmalı.

İpucu 2

Birden fazla kaydı olan gruplar: HAVING count(*) > 1.

İpucu 3

string_agg(id::text, ',' ORDER BY id) ile id'leri sıralı birleştir.

Çözüm ve açıklama

sql

SELECT lower(email)                               AS email,
       count(*)                                   AS account_count,
       string_agg(id::text, ',' ORDER BY id)      AS customer_ids
  FROM customers
 GROUP BY lower(email)
HAVING count(*) > 1
 ORDER BY 1;

Bu bir veri kalitesi problemi; asıl çözüm önlemek:

Benzersizliği DB seviyesinde garanti et: CREATE UNIQUE INDEX ON customers (lower(email)); (expression index). Ya da citext tipi.
Uygulamada e-postayı normalize ederek kaydet (trim + lowercase). Türkçe karakterlerde toLowerCase(Locale.ROOT) kullan.
Mevcut mükerrer kayıtlar için birleştirme (merge) stratejisi: hangi hesap kalır, siparişler hangi hesaba taşınır? Bunu iş birimiyle konuş.

"Sadece uygulamada kontrol ederim" demek yetmez: iki eşzamanlı kayıt isteği check-then-act yarışına girer. Garantiyi unique index verir.

06OrtaDISTINCT ON · Grup başına son kayıt

Her müşterinin en son siparişi

Müşteri profili sayfası için her müşterinin en son verdiği siparişi (statüsü ne olursa olsun) getir.

Kolonlar: customer_id, order_id, status, created_at. Sıra: customer_id. Aynı anda iki sipariş varsa büyük id'li olan seçilsin.

İpucu 1

"Grup başına en üstteki satır" problemi (greatest-n-per-group).

İpucu 2

PostgreSQL: DISTINCT ON (customer_id) + ORDER BY customer_id, created_at DESC.

İpucu 3

Taşınabilir yol: row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC, id DESC) = 1.

Çözüm ve açıklama

sql

SELECT DISTINCT ON (customer_id)
       customer_id, id AS order_id, status, created_at
  FROM orders
 ORDER BY customer_id, created_at DESC, id DESC;

Greatest-n-per-group, live coding'de çok sorulur. Çözümler:

DISTINCT ON: PostgreSQL'e özgü, kısa. ORDER BY'ın ilk kolonları DISTINCT ON ifadesiyle aynı olmalı.
row_number() ... = 1: her veritabanında çalışır; "en son 3 sipariş" gibi N'e de genellenir.
max(created_at) ile join: eşit zamanlarda tekrarlı satır üretebilir. Dikkat.
LATERAL + LIMIT 1: müşteri listesi küçük ve orders(customer_id, created_at DESC) index'i varsa en hızlısı.

Tie-breaker (id DESC) yazmazsan sonuç deterministik olmaz. Mülakatçılar bunu fark eder.

07OrtaKoşullu aggregation · FILTER · NULLIF

Satıcı bazında iptal oranı

Her satıcı için, satıcının ürünlerini içeren farklı sipariş sayısını ve bunların ne kadarının iptal edildiğini raporla.

Kolonlar: seller_name, total_orders, delivered_orders, cancelled_orders, cancel_rate (yüzde, 1 ondalık). Sıra: seller_name.

İpucu 1

Bir siparişte satıcının iki ürünü olabilir: siparişi bir kez saymak için count(DISTINCT o.id).

İpucu 2

Statüye göre sayım: count(...) FILTER (WHERE status = 'CANCELLED') ya da sum(CASE WHEN ... THEN 1 ELSE 0 END).

İpucu 3

Yüzde: 100.0 * a / NULLIF(b, 0); tam sayı bölmesine ve sıfıra bölmeye dikkat.

Çözüm ve açıklama

sql

SELECT s.name AS seller_name,
       count(DISTINCT o.id)                                          AS total_orders,
       count(DISTINCT o.id) FILTER (WHERE o.status = 'DELIVERED')    AS delivered_orders,
       count(DISTINCT o.id) FILTER (WHERE o.status = 'CANCELLED')    AS cancelled_orders,
       round(100.0 * count(DISTINCT o.id) FILTER (WHERE o.status = 'CANCELLED')
             / NULLIF(count(DISTINCT o.id), 0), 1)                   AS cancel_rate
  FROM sellers s
  JOIN products p     ON p.seller_id = s.id
  JOIN order_items oi ON oi.product_id = p.id
  JOIN orders o       ON o.id = oi.order_id
 GROUP BY s.id, s.name
 ORDER BY s.name;

Koşullu aggregation, tek geçişte birden fazla metrik hesaplar (pivot benzeri rapor). FILTER PostgreSQL ve SQL standardındadır; diğer veritabanlarında sum(CASE WHEN ... THEN 1 ELSE 0 END) kullanılır.

Tam sayı bölmesi tuzağı: 3 / 10 = 0. 100.0 * ile numeric'e çevir.
Sıfıra bölme: NULLIF(x, 0) payda sıfırsa NULL döndürür, sorgu patlamaz.
Join fan-out: Join'ler satırları çoğaltır. Bu yüzden sayımlarda DISTINCT gerekir; toplamlar (sum) ise yanlışlıkla çoğalabilir. Bu yüzden ayrı CTE'lerde toplayıp sonra birleştirmek gerekir.

08OrtaWindow · DENSE_RANK · PARTITION BY

Her kategoride en çok satan 2 ürün

Kategori sayfalarında "çok satanlar" rozeti gösterilecek. Teslim edilmiş siparişlerdeki toplam adede göre her kategoride (ürünün kendi kategorisi) ilk 2 sıradaki ürünleri bul. Aynı adette satanlar aynı sırayı paylaşmalı (dense_rank).

Kolonlar: category_name, product_name, total_qty. Sıra: category_name, total_qty azalan, product_name.

İpucu 1

Önce ürün bazında toplam adedi hesapla (CTE).

İpucu 2

Sonra dense_rank() OVER (PARTITION BY category_id ORDER BY total_qty DESC).

İpucu 3

Window function'ın sonucu WHERE'de doğrudan kullanılamaz; bir dış sorguda ya da CTE'de filtrele.

Çözüm ve açıklama

sql

WITH product_sales AS (
    SELECT p.category_id, p.name AS product_name, sum(oi.quantity) AS total_qty
      FROM order_items oi
      JOIN orders o   ON o.id = oi.order_id AND o.status = 'DELIVERED'
      JOIN products p ON p.id = oi.product_id
     GROUP BY p.category_id, p.id, p.name
), ranked AS (
    SELECT ps.*, dense_rank() OVER (PARTITION BY category_id ORDER BY total_qty DESC) AS rnk
      FROM product_sales ps
)
SELECT c.name AS category_name, r.product_name, r.total_qty
  FROM ranked r
  JOIN categories c ON c.id = r.category_id
 WHERE r.rnk <= 2
 ORDER BY c.name, r.total_qty DESC, r.product_name;

Değerler	row_number	rank	dense_rank
10, 8, 8, 5	1, 2, 3, 4	1, 2, 2, 4	1, 2, 2, 3

"İlk 2" isteğinde eşitlik politikasını sor: tam 2 satır mı (row_number), eşitler dahil mi (rank/dense_rank)?

Window function'lar SELECT aşamasında hesaplandığı için WHERE'de kullanılamaz. Bu yüzden CTE gerekir. Performans için order_items(product_id) ve orders(status) index'leri, büyük ölçekte ise önceden toplanmış satış tablosu düşünülür.

09ZorWindow · LAG · tarih aritmetiği

Müşterilerin sipariş verme sıklığı

Pazarlama ekibi, hatırlatma e-postalarını müşterinin alışkanlığına göre zamanlamak istiyor. İptal edilmemiş siparişlerine göre, en az 2 siparişi olan her müşteri için ardışık siparişleri arasındaki ortalama gün sayısını hesapla.

Kolonlar: customer_id, avg_gap_days (1 ondalık). Sıra: customer_id.

İpucu 1

Bir önceki siparişin tarihi: lag(created_at) OVER (PARTITION BY customer_id ORDER BY created_at).

İpucu 2

İki timestamptz'nin farkı bir interval'dir; extract(epoch FROM gap) / 86400 ile güne çevir.

İpucu 3

İlk siparişin lag'i NULL'dur; onu ele. Tek siparişli müşteriler böylece kendiliğinden düşer.

Çözüm ve açıklama

sql

WITH gaps AS (
    SELECT customer_id,
           created_at - lag(created_at) OVER (PARTITION BY customer_id ORDER BY created_at) AS gap
      FROM orders
     WHERE status <> 'CANCELLED'
)
SELECT customer_id,
       round(avg(extract(epoch FROM gap) / 86400)::numeric, 1) AS avg_gap_days
  FROM gaps
 WHERE gap IS NOT NULL
 GROUP BY customer_id
 ORDER BY customer_id;

lag ve lead, bir önceki ve sonraki satıra bakmayı sağlar. Self-join'e gerek kalmaz. Kullanım alanları: oturum analizi, fiyat değişimi, churn tahmini, sipariş aralığı.

Interval'i güne çevirmek için extract(epoch FROM interval) / 86400. Sadece takvim günü gerekiyorsa created_at::date - lag(...)::date tam sayı gün verir.
Saat dilimi: ::date dönüşümü oturumun TimeZone ayarına göre yapılır. Türkiye'deki günü istiyorsan (created_at AT TIME ZONE 'Europe/Istanbul')::date.
Alternatif ortalama: (son sipariş − ilk sipariş) / (sipariş sayısı − 1). Matematiksel olarak aynı sonucu verir, window gerektirmez. Mülakatta bunu fark etmek artı puandır.

10OrtaWindow · kümülatif toplam

Son 30 günün günlük ve kümülatif cirosu

Kampanya panosu için son 30 günde (bugün dahil, created_at >= now() - interval '30 days') teslim edilmiş siparişlerin günlük cirosunu ve dönem başından itibaren kümülatif cirosunu göster. Sadece satış olan günler listelensin.

Kolonlar: day (date), daily_revenue, running_total. Sıra: day.

İpucu 1

Önce gün bazında topla (GROUP BY created_at::date).

İpucu 2

Kümülatif toplam: sum(daily_revenue) OVER (ORDER BY day).

İpucu 3

Aggregate ile window'u aynı SELECT'te karıştırabilirsin ama önce CTE ile ayırmak daha okunur.

Çözüm ve açıklama

sql

WITH daily AS (
    SELECT o.created_at::date AS day,
           sum(oi.quantity * oi.unit_price) AS daily_revenue
      FROM orders o
      JOIN order_items oi ON oi.order_id = o.id
     WHERE o.status = 'DELIVERED'
       AND o.created_at >= now() - interval '30 days'
     GROUP BY o.created_at::date
)
SELECT day,
       daily_revenue,
       sum(daily_revenue) OVER (ORDER BY day) AS running_total
  FROM daily
 ORDER BY day;

sum(x) OVER (ORDER BY day) varsayılan olarak RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW çerçevesini kullanır, yani kümülatif toplam verir. Hareketli 7 günlük ortalama: avg(x) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).

Boş günleri de göstermek istenirse generate_series(current_date - 30, current_date, interval '1 day') ile bir takvim üretilip LEFT JOIN yapılır ve coalesce(daily_revenue, 0) kullanılır. Bu, sık sorulan bir devam sorusudur.

11ZorRecursive CTE · hiyerarşi

Kategori ağacı: Elektronik altındaki her şey

categories tablosu bir ağaç (parent_id). "Elektronik" kategorisini (id = 1) ve altındaki tüm alt kategorileri (her derinlikte) listele. Her biri için derinliği (Elektronik = 0) ve doğrudan o kategoriye bağlı ürün sayısını göster (ürünü olmayanlar 0).

Kolonlar: category_name, depth, product_count. Sıra: depth, category_name.

İpucu 1

WITH RECURSIVE: başlangıç (anchor) sorgusu + UNION ALL + kendine join eden recursive kısım.

İpucu 2

Anchor: WHERE id = 1, depth 0. Recursive: categories c JOIN tree t ON c.parent_id = t.id, depth + 1.

İpucu 3

Ürünü olmayan kategoriler de görünsün diye LEFT JOIN ve count(p.id) (count(*) değil!).

Çözüm ve açıklama

sql

WITH RECURSIVE tree AS (
    SELECT id, name, 0 AS depth
      FROM categories
     WHERE id = 1
    UNION ALL
    SELECT c.id, c.name, t.depth + 1
      FROM categories c
      JOIN tree t ON c.parent_id = t.id
)
SELECT t.name AS category_name,
       t.depth,
       count(p.id) AS product_count
  FROM tree t
  LEFT JOIN products p ON p.category_id = t.id
 GROUP BY t.id, t.name, t.depth
 ORDER BY t.depth, t.name;

Recursive CTE, hiyerarşik verinin (kategori ağacı, organizasyon şeması, yorum zinciri) standart çözümüdür. Mülakatta sorulursa şu üç noktaya değin:

Döngü koruması: Hatalı veride (A → B → A) sonsuz döngü olur. Ziyaret edilenleri bir dizide tutup WHERE NOT c.id = ANY(path) ile engelle. PostgreSQL 14+'te CYCLE cümlesi de var.
count(p.id) ve count(*): LEFT JOIN'de eşleşme yoksa satır yine gelir. count(*) onu 1 sayar, count(p.id) NULL'u saymaz ve 0 verir.
Ölçek: Çok okunan ağaçlarda her seferinde recursive sorgu yerine materialized path ('1/4/5'), ltree eklentisi ya da closure table kullanılır.

12ZorCTE · fan-out tuzağı · oran

Kategori bazında iade oranı

Kalite ekibi, teslim edilmiş siparişlerde kategori bazında satılan adedi, iade edilen adedi ve iade oranını istiyor. İadesi olmayan kategoriler de 0 iade ile listelensin; hiç satışı olmayan kategoriler listelenmesin.

Kolonlar: category_name, sold_qty, returned_qty, return_rate (yüzde, 1 ondalık). Sıra: return_rate azalan, category_name.

İpucu 1

Satışları ve iadeleri aynı join'de toplarsan satırlar çoğalır (fan-out) ve toplamlar şişer.

İpucu 2

İki ayrı CTE'de kategori bazında topla, sonra kategori üzerinden birleştir.

İpucu 3

İadesi olmayan kategoriler için LEFT JOIN + coalesce(..., 0); yüzde için 100.0 * ... / NULLIF(..., 0).

Çözüm ve açıklama

sql

WITH sold AS (
    SELECT p.category_id, sum(oi.quantity) AS sold_qty
      FROM order_items oi
      JOIN orders o   ON o.id = oi.order_id AND o.status = 'DELIVERED'
      JOIN products p ON p.id = oi.product_id
     GROUP BY p.category_id
), returned AS (
    SELECT p.category_id, sum(r.quantity) AS returned_qty
      FROM returns r
      JOIN products p ON p.id = r.product_id
     GROUP BY p.category_id
)
SELECT c.name AS category_name,
       s.sold_qty,
       coalesce(r.returned_qty, 0) AS returned_qty,
       round(100.0 * coalesce(r.returned_qty, 0) / NULLIF(s.sold_qty, 0), 1) AS return_rate
  FROM sold s
  JOIN categories c    ON c.id = s.category_id
  LEFT JOIN returned r ON r.category_id = s.category_id
 ORDER BY return_rate DESC, c.name;

Fan-out tuzağı, live coding'de en sık yapılan SQL hatasıdır. order_items JOIN returns yapıp ikisini birden sum'larsan, bir sipariş satırının iki iadesi varsa satış iki kez sayılır. Doğrusu: önce topla, sonra birleştir (aggregate-then-join).

LEFT JOIN'de filtre yeri: Sağ tablonun koşulunu WHERE'e yazarsan (WHERE r.reason = 'Hasarlı ürün') LEFT JOIN fiilen INNER JOIN'e döner, çünkü eşleşmeyen satırlarda r.reason NULL'dur ve elenir. Koşul ON'a ya da CTE'nin içine yazılır.

13OrtaDML · UPSERT · ON CONFLICT

Depoya sevkiyat geldi: stok ekle

Depoya sevkiyat geldi: ürün 5'e 10 adet, ürün 17'ye 25 adet eklenecek. Ürün 17'nin henüz stok kaydı yok. Bunu tek bir INSERT ... ON CONFLICT ifadesiyle yap: kayıt varsa mevcut stoğa ekle, yoksa yeni kayıt oluştur.

Kontrol: Lab ifadeni bir transaction içinde çalıştırır, sonucu SELECT product_id, quantity FROM stock WHERE product_id IN (5, 17) ile karşılaştırır ve ROLLBACK eder. Veritabanın bozulmaz.

İpucu 1

ON CONFLICT (product_id) DO UPDATE SET ...

İpucu 2

Gelen değere EXCLUDED.quantity ile, mevcut değere stock.quantity ile erişilir.

İpucu 3

quantity = EXCLUDED.quantity yazarsan mevcut stoğu ezersin; eklemek gerekiyor.

Çözüm ve açıklama

sql

INSERT INTO stock (product_id, quantity)
VALUES (5, 10), (17, 25)
ON CONFLICT (product_id)
DO UPDATE SET quantity = stock.quantity + EXCLUDED.quantity;

Upsert atomiktir: "SELECT ile var mı bak, yoksa INSERT, varsa UPDATE" yazmak check-then-act yarışı yaratır. İki eşzamanlı istek ikisi de "yok" görür ve biri unique violation alır. ON CONFLICT, unique/PK constraint'e dayanarak bunu tek ifadede güvenle çözer.

EXCLUDED: eklenmek istenen satır.
DO NOTHING: idempotent kayıt için (ör. processed_event tablosu).
RETURNING *: sonucu ek sorgu atmadan döndürür.
JPA'da save() bunu yapmaz; native query ya da @SQLInsert gerekir.

14OrtaDML · koşullu atomik UPDATE

Stok düş, ama asla eksiye düşürme

İki sipariş geldi: ürün 7'den 3 adet ve ürün 1'den 1000 adet. Stok yeterliyse düş, yetersizse o ürünün stoğuna hiç dokunma ve hata da verme. Her ürün için ayrı bir UPDATE ifadesi yazabilirsin (iki ifade, noktalı virgülle).

Not: stock tablosunda CHECK (quantity >= 0) var. Koşulsuz bir UPDATE, stok yetersizse constraint hatası verir.

İpucu 1

Kontrolü WHERE koşuluna koy: ... AND quantity >= :istenen.

İpucu 2

Etkilenen satır sayısı 0 ise stok yetersiz demektir; uygulama bunu kontrol eder.

İpucu 3

RETURNING quantity ile yeni stoğu tek seferde alabilirsin.

Çözüm ve açıklama

sql

UPDATE stock SET quantity = quantity - 3
 WHERE product_id = 7 AND quantity >= 3;

UPDATE stock SET quantity = quantity - 1000
 WHERE product_id = 1 AND quantity >= 1000;

Bu, ilk quiz'deki flash sale sorusunun SQL hali. Tek ifade atomiktir: PostgreSQL satırı kilitler, koşulu güncel değerle değerlendirir ve yazar. Uygulama tarafında etkilenen satır sayısına bakılır: int updated = repo.decrease(id, qty); if (updated == 0) throw new InsufficientStockException(...).

CHECK constraint, uygulama hatasına karşı son savunma hattıdır. Bunu tek kontrol olarak kullanırsan iş kuralı hatası exception olarak gelir (23514). Koşullu WHERE ile ise temiz bir "0 satır" sonucu alırsın. İkisini birlikte kullan.

15OrtaIndex · EXPLAIN

"Son siparişlerim" sorgusu için index

Mobil uygulama her açılışta şu sorguyu çalıştırıyor:

SELECT id, status, created_at FROM orders
 WHERE customer_id = $1
 ORDER BY created_at DESC
 LIMIT 5;

Bu sorgu için en uygun composite index'i oluştur. (Lab ifadeni transaction içinde çalıştırdığı için CONCURRENTLY kullanma; production'da neden kullanacağın açıklamada.)

Kontrol, orders üzerinde (customer_id, created_at ...) ile başlayan bir index olup olmadığına bakar. İndex'i oluşturduktan sonra Serbest Sorgu sekmesinde EXPLAIN ile planı görebilirsin.

İpucu 1

Eşitlik kolonu önce, sıralama kolonu sonra (ESR kuralı).

İpucu 2

ON orders (customer_id, created_at DESC).

İpucu 3

INCLUDE (status, id) eklersen Index Only Scan de mümkün olur (sorgu id'yi de seçiyor; ikincil index PK'yi kendiliğinden taşımaz).

Çözüm ve açıklama

sql

CREATE INDEX idx_orders_customer_created
    ON orders (customer_id, created_at DESC);

(customer_id, created_at DESC) index'i ile PostgreSQL müşterinin siparişlerine doğrudan atlar, zaten sıralı okur ve 5 satırda durur. Sort node'u yoktur. Sorgu id, status ve created_at seçtiği için INCLUDE (status, id) eklenirse tabloya hiç gidilmez (Index Only Scan; visibility map güncelse Heap Fetches: 0). Yalnızca INCLUDE (status) yetmez: PostgreSQL'de ikincil index'ler primary key'i taşımaz, id için yine heap'e gidilir.

Production'da: CREATE INDEX CONCURRENTLY. Normal CREATE INDEX tabloya yazma kilidi koyar; büyük tabloda dakikalarca sipariş alınamaz. CONCURRENTLY transaction içinde çalışmaz, daha yavaştır ve fail ederse INVALID index bırakır.
Küçük tabloda planner index'i kullanmayabilir (Seq Scan daha ucuz). Bu lab'da da öyle olabilir; mülakatta bunu bilmek artı puandır.
Tek kolonlu orders(customer_id) index'i de filtreler, ama sıralama için Sort node'u gerekir.