Veri Analistleri için SQL: Pencere Fonksiyonları, CTE ve İleri Düzey Sorgular

Veri analistleri için SQL bilgisi, özellikle pencere fonksiyonları ve CTE konuları, teknik mülakatlarda en sık karşılaşılan alanlardan birini oluşturmaktadır. Temel SELECT sorgularının ötesine geçen bu ileri SQL kalıpları, büyük veri kümelerinden anlamlı içerikler çıkarmayı ve karmaşık analitik soruları tek bir sorgu ile yanıtlamayı mümkün kılmaktadır. Bu rehber, pencere fonksiyonlarından özyinelemeli CTE'lere, boşluk-ada analizinden koşullu analitik kalıplara kadar her konuyu pratik kod örnekleriyle ele almaktadır.

SQL Pencere Fonksiyonlarını ve OVER Yan Tümcesini Anlamak

Pencere fonksiyonları, standart toplama fonksiyonlarından farklı olarak satırları daraltmaz. OVER yan tümcesi içerisindeki PARTITION BY ifadesi verileri mantıksal gruplara ayırırken, ORDER BY her grup içerisindeki sıralama düzenini belirler. ROWS BETWEEN ifadesi ise pencere çerçevesini tanımlar ve hangi satırların hesaplamaya dahil edileceğini kontrol eder.

Aşağıdaki örnek, her departman içerisindeki çalışanları maaş sırasına göre sıralar ve kümülatif maaş toplamını hesaplar:

-- window_function_syntax.sql
SELECT
  employee_id,
  department,
  salary,
  -- Ranks employees within each department by salary
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_salary_rank,
  -- Running total of salaries within department
  SUM(salary) OVER (
    PARTITION BY department
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_salary_total
FROM employees;

PARTITION BY department her departmanı bağımsız bir pencere olarak tanımlarken, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ifadesi pencere çerçevesinin başından mevcut satıra kadar olan tüm satırları hesaplamaya dahil eder. Bu yaklaşım, her satırın hem kendi bilgisini hem de departman bazında kümülatif değerlerini aynı anda gösterir.

ROW_NUMBER, RANK ve DENSE_RANK Karşılaştırması

Sıralama fonksiyonları teknik mülakatlarda en çok sorulan konular arasında yer alır. Üç fonksiyonun davranışı, özellikle eşit değerler (ties) söz konusu olduğunda birbirinden ayrılır:

-- ranking_comparison.sql
SELECT
  product_name,
  category,
  revenue,
  ROW_NUMBER() OVER (ORDER BY revenue DESC) AS row_num,
  RANK()       OVER (ORDER BY revenue DESC) AS rank_val,
  DENSE_RANK() OVER (ORDER BY revenue DESC) AS dense_rank_val
FROM products;

| product_name | revenue | row_num | rank_val | dense_rank_val | |-------------|---------|---------|----------|----------------| | Widget A | 50000 | 1 | 1 | 1 | | Widget B | 50000 | 2 | 1 | 1 | | Widget C | 40000 | 3 | 3 | 2 | | Widget D | 30000 | 4 | 4 | 3 |

ROW_NUMBER eşit değerler dahil her satıra benzersiz bir numara atar. RANK eşit değerlere aynı sırayı verir ancak sonraki sırayı atlar (1, 1, 3). DENSE_RANK ise eşit değerlere aynı sırayı verir ancak boşluk bırakmaz (1, 1, 2). Dönem bazlı sıralama, sayfalama ve en üst N analizi gibi senaryolarda hangi fonksiyonun kullanılacağını bilmek, doğru sonuçlara ulaşmak için kritik öneme sahiptir.

LAG ve LEAD ile Dönemler Arası Analiz

Zaman serisi verileri üzerinde dönemsel karşılaştırmalar yapmak, veri analizinin temel gereksinimlerinden biridir. LAG fonksiyonu önceki satırın değerine erişirken, LEAD fonksiyonu sonraki satırın değerini getirir. Bu iki fonksiyon, büyüme oranları, trend analizi ve anomali tespiti için sıklıkla kullanılır.

-- period_over_period.sql
SELECT
  month,
  revenue,
  -- Previous month revenue
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
  -- Month-over-month growth rate
  ROUND(
    (revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0
    / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0),
    2
  ) AS mom_growth_pct,
  -- Next month revenue (forward-looking)
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue
FROM monthly_sales
ORDER BY month;

LAG(revenue, 1) ifadesi bir önceki ayın gelirini döndürür. NULLIF fonksiyonu sıfıra bölme hatasını önlemek için kullanılır. Hesaplanan mom_growth_pct sütunu, aylık büyüme yüzdesini doğrudan sorgu çıktısında sunar. Bu yaklaşım, ayrı JOIN işlemleri veya alt sorgular yerine tek bir pencere fonksiyonu ile dönemsel karşılaştırmaları mümkün kılar.

NTILE ve Yüzdelik Dilimlendirme

Müşterileri harcama segmentlerine ayırmak veya performans yüzdeliklerini hesaplamak, veri analisti rolünün temel gereksinimlerinden biridir. NTILE(n) fonksiyonu sıralanan satırları n eşit gruba böler ve her satıra 1 ile n arasında bir dilim numarası atar.

-- customer_segmentation.sql
SELECT
  customer_id,
  total_spend,
  -- Split customers into 4 quartiles by spend
  NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_quartile,
  -- Decile scoring for finer granularity
  NTILE(10) OVER (ORDER BY total_spend DESC) AS spend_decile
FROM (
  SELECT
    customer_id,
    SUM(order_total) AS total_spend
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY customer_id
) customer_totals;

NTILE(4) en yüksek harcama yapan müşterileri 1. çeyrekliğe, en düşük harcama yapanları 4. çeyrekliğe atar. NTILE(10) ise daha ince taneli segmentasyon için onluk dilimler oluşturur. Bu yaklaşım pazarlama kampanyalarında hedefleme, müşteri yaşam döngüsü analizi ve RFM skorlaması gibi senaryolarda kullanılır. Satır sayısı eşit bölünmediğinde, NTILE ilk gruplara bir fazla satır atar.

Common Table Expressions: İç İçe Alt Sorguların Yerini Alan Yaklaşım

CTE'ler (WITH ifadesi), karmaşık sorgulamayı adımlar halinde yapılandırarak okunabilirliği önemli ölçüde artırır. Her CTE bloğu geçici bir sonuç kümesi tanımlar ve sonraki bloklar bu sonuçlara isim üzerinden erişir. Bu yaklaşım, iç içe geçmiş alt sorguların bakımını zorlaştırdığı durumlarda tercih edilen yöntemdir.

-- cte_sales_analysis.sql
WITH monthly_revenue AS (
  -- Step 1: Aggregate raw orders into monthly totals
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    product_category,
    SUM(amount) AS revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY DATE_TRUNC('month', order_date), product_category
),
ranked_categories AS (
  -- Step 2: Rank categories within each month
  SELECT
    month,
    product_category,
    revenue,
    unique_customers,
    RANK() OVER (
      PARTITION BY month
      ORDER BY revenue DESC
    ) AS category_rank
  FROM monthly_revenue
)
-- Step 3: Final output — top 3 categories per month
SELECT
  month,
  product_category,
  revenue,
  unique_customers,
  category_rank
FROM ranked_categories
WHERE category_rank <= 3
ORDER BY month, category_rank;

İlk CTE (monthly_revenue) ham sipariş verilerini aylık toplamlara dönüştürür. İkinci CTE (ranked_categories) pencere fonksiyonu kullanarak kategorileri sıralar. Son SELECT ifadesi yalnızca ilk üç kategoriyi filtreler. Bu üç katmanlı yaklaşım, her adımın bağımsız olarak test edilebilmesini ve hatanın kolayca izlenebilmesini sağlar.

Özyinelemeli CTE'ler ile Hiyerarşik Veri Sorgulama

Organizasyon şemaları, ürün kategorileri veya dosya sistemi yapıları gibi ağaç şeklindeki veriler, özyinelemeli CTE'ler ile sorgulanır. WITH RECURSIVE ifadesi bir temel durum (base case) ve tekrar eden durum (recursive case) olmak üzere iki bölümden oluşur.

-- recursive_org_chart.sql
WITH RECURSIVE org_hierarchy AS (
  -- Base case: top-level managers (no manager above them)
  SELECT
    employee_id,
    employee_name,
    manager_id,
    1 AS depth,
    employee_name AS management_chain
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case: join employees to their managers
  SELECT
    e.employee_id,
    e.employee_name,
    e.manager_id,
    oh.depth + 1,
    oh.management_chain || ' > ' || e.employee_name
  FROM employees e
  INNER JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT
  employee_id,
  employee_name,
  depth,
  management_chain
FROM org_hierarchy
ORDER BY management_chain;

Temel durum, manager_id IS NULL koşuluyla üst düzey yöneticileri seçer. Tekrar eden durum, her çalışanı yöneticisinin satırına birleştirerek hiyerarşiyi seviye seviye oluşturur. management_chain sütunu, kökten yaprak düğüme kadar tüm yönetim zincirini tek bir metin olarak gösterir.

İleri Analitik Kalıplar: Boşluklar, Adalar ve Kümülatif Toplamlar

Boşluk-ada (gaps and islands) problemi, ardışık kayıtlardaki sürekliliği tespit etmek için kullanılan klasik bir SQL kalıbıdır. Bu kalıp, ardışık giriş yapan kullanıcılar, kesintisiz çalışma serileri veya art arda satış yapılan günler gibi senaryolarda uygulanır.

-- consecutive_login_streaks.sql
WITH login_days AS (
  SELECT DISTINCT
    user_id,
    DATE(login_timestamp) AS login_date
  FROM user_sessions
),
streaks AS (
  SELECT
    user_id,
    login_date,
    -- Subtracting row_number from date creates a constant for consecutive days
    login_date - (ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY login_date
    ))::int AS streak_group
  FROM login_days
)
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*) AS streak_length
FROM streaks
GROUP BY user_id, streak_group
HAVING COUNT(*) >= 3
ORDER BY streak_length DESC;

Tekniğin özünde, ROW_NUMBER ile tarih arasındaki fark sabit bir değer üretir. Ardışık günlerde bu fark değişmezken, boşluk olduğunda değer değişir. Bu sabit değer streak_group olarak kullanılarak ardışık günler gruplanır. HAVING COUNT(*) >= 3 filtresi yalnızca en az 3 gün süren serileri döndürür.

Pencere Fonksiyonlarını CASE ile Birleştirerek Koşullu Analitik

Pencere fonksiyonları CASE ifadesiyle birleştiğinde, hareketli ortalamalar, çeyreklik kümülatif toplamlar ve aykırı değer tespiti gibi karmaşık analitik kalıplar oluşturulabilir. Bu yaklaşım, farklı zaman dilimlerinde ve koşullarda eşzamanlı hesaplamalar yapmayı sağlar.

-- conditional_analytics.sql
SELECT
  order_date,
  product_category,
  amount,
  -- 7-day moving average
  AVG(amount) OVER (
    PARTITION BY product_category
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d,
  -- Cumulative sum resetting each quarter
  SUM(amount) OVER (
    PARTITION BY product_category, DATE_TRUNC('quarter', order_date)
    ORDER BY order_date
  ) AS qtd_cumulative,
  -- Flag if current row exceeds 2x the category average
  CASE
    WHEN amount > 2 * AVG(amount) OVER (PARTITION BY product_category)
    THEN 'OUTLIER'
    ELSE 'NORMAL'
  END AS outlier_flag
FROM daily_sales
ORDER BY product_category, order_date;

moving_avg_7d son 7 günün ortalamasını hesaplar ve kısa vadeli trend analizi için kullanılır. qtd_cumulative her çeyrekte sıfırlanarak çeyreklik kümülatif toplamı gösterir; bunun için PARTITION BY ifadesine DATE_TRUNC('quarter', order_date) eklenir. outlier_flag ise mevcut satırın değerini kategori genelindeki ortalama ile karşılaştırarak 2 kattan fazla olan değerleri OUTLIER olarak işaretler.

Analitik Sorgularda Performans Optimizasyonu

Aynı pencere tanımı birden fazla fonksiyonda tekrarlandığında, WINDOW yan tümcesi ile adlandırılmış pencereler tanımlanabilir. Bu yaklaşım hem okunabilirliği artırır hem de sorgu iyileştiricisinin (query optimizer) aynı pencereyi yeniden hesaplamamasını sağlar.

-- named_window.sql
SELECT
  department,
  employee_id,
  salary,
  RANK() OVER dept_window AS dept_rank,
  SUM(salary) OVER dept_window AS dept_total,
  AVG(salary) OVER dept_window AS dept_avg
FROM employees
WINDOW dept_window AS (
  PARTITION BY department
  ORDER BY salary DESC
);

WINDOW dept_window AS (...) ifadesi pencere tanımını bir kez yapar ve üç farklı fonksiyon aynı tanımı paylaşır. Performans için ek olarak şu noktalar dikkate alınmalıdır: PARTITION BY ve ORDER BY sütunlarında uygun indeksler oluşturmak sorgu süresini önemli ölçüde kısaltır. Gereksiz yere geniş pencere çerçeveleri kullanmaktan kaçınmak bellek tüketimini azaltır. Büyük veri kümelerinde CTE ile ön filtreleme yaparak pencere fonksiyonuna aktarılan satır sayısını sınırlamak etkili bir optimizasyon stratejisidir.

Sonuç

SQL pencere fonksiyonları ve CTE'ler, veri analistlerinin karmaşık soruları verimli ve okunabilir sorgularla yanıtlamasını sağlayan temel araçlardır. Bu rehberde ele alınan konuların özeti:

• RANK, ROW_NUMBER ve DENSE_RANK farklılıklarını bilmek, eşit değerlerin olduğu sıralama senaryolarında doğru sonuçlara ulaşmak için zorunludur
• LAG ve LEAD fonksiyonları, JOIN işlemleri olmadan dönemsel karşılaştırmalar yapmanın en verimli yoludur
• NTILE ile müşteri segmentasyonu ve yüzdelik dilimlendirme işlemleri tek bir sorgu ile gerçekleştirilir
• CTE'ler karmaşık sorgulamaları adım adım yapılandırarak hata ayıklamayı ve bakımı kolaylaştırır
• Özyinelemeli CTE'ler hiyerarşik verileri sorgulamada vazgeçilmez bir araçtır ancak derinlik kontrolü ile birlikte kullanılmalıdır
• Boşluk-ada kalıpları ROW_NUMBER ve tarih aritmetiği kombinasyonu ile ardışık kayıtları tespit eder
• Adlandırılmış pencereler (WINDOW) tekrar eden tanımları ortadan kaldırarak hem okunabilirliği hem de performansı iyileştirir

Tüm bu kalıplar, pencere fonksiyonları ve CTE bilgisini pratikte uygulayarak pekiştirmek için Data Analytics pratik alıştırmaları üzerinden çalışılabilir.