Ana içeriğe geç
Kesit SQL
İleri · Büyük Sorgular

Büyük sorgu 1: aylık kohort elde tutma matrisi

İlk sipariş ayına göre kohortlar, ay numarası hesabı ve M1–M3 elde tutma oranlarını tek bir matriste toplamak.

Uygulamada aç, 6 alıştırmayı çözÜcretsiz, hesap gerekmez, kurulum yok.

Bu sayfa dersin okuma sürümüdür. Metin bir kutuyu çalıştırmanı ya da adıma geçmeni söylüyorsa dersi uygulamada aç.

Soru

Pazarlama direktörü soruyor: “2023'te ilk siparişini veren müşterilerin kaçta kaçı sonraki aylarda geri geldi? Hangi ayın müşterileri daha sadık?” Cevap bir kohort matrisi: her satır bir başlangıç ayı, her sütun “kaç ay sonra” sorusu.

Sorgu

Büyük sorgu nasıl okunur?Yukarıdan aşağı, her CTE'yi ayrı bir tablo gibi düşün. Bir adımı anlamadıysan o CTE'nin adını en sondaki SELECT'e yazıp yalnızca onu çalıştır: SELECT * FROM adim_adi LIMIT 20;
WITH ilk AS (            -- 1) Müşterinin kohortu: ilk sipariş ayı
  SELECT customer_id, MIN((CAST(strftime('%Y', order_date) AS INTEGER) * 12 + CAST(strftime('%m', order_date) AS INTEGER))) AS kohort_no
  FROM orders
  GROUP BY customer_id
),
aktif AS (              -- 2) Müşterinin sipariş verdiği her ay (tekrarsız)
  SELECT DISTINCT customer_id, (CAST(strftime('%Y', order_date) AS INTEGER) * 12 + CAST(strftime('%m', order_date) AS INTEGER)) AS ay_no
  FROM orders
),
fark AS (               -- 3) Kohorttan bu yana geçen ay sayısı
  SELECT i.kohort_no, a.ay_no - i.kohort_no AS ay_farki, a.customer_id
  FROM aktif a
  JOIN ilk i ON i.customer_id = a.customer_id
),
boyut AS (              -- 4) Her kohortun büyüklüğü
  SELECT kohort_no, COUNT(*) AS musteri
  FROM ilk
  GROUP BY kohort_no
)
SELECT                   -- 5) Matris: kohort satırda, ay farkı sütunda
  FLOOR((b.kohort_no - 1) / 12) AS yil,
  (b.kohort_no - 1) % 12 + 1 AS ay,
  b.musteri,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN f.ay_farki = 1 THEN f.customer_id END) / b.musteri, 1) AS m1,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN f.ay_farki = 2 THEN f.customer_id END) / b.musteri, 1) AS m2,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN f.ay_farki = 3 THEN f.customer_id END) / b.musteri, 1) AS m3,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN f.ay_farki BETWEEN 1 AND 6 THEN f.customer_id END) / b.musteri, 1) AS ilk6ay
FROM boyut b
JOIN fark f ON f.kohort_no = b.kohort_no
WHERE b.kohort_no >= 2023 * 12 + 1 AND b.kohort_no <= 2023 * 12 + 12
GROUP BY b.kohort_no, b.musteri
ORDER BY b.kohort_no;

Adım adım

AdımNe yapar?Çıktının bir satırı
ilkMüşterinin ilk sipariş ayını “ay numarası” (yıl × 12 + ay) olarak bulurmüşteri 17 → 24282 (2023 Haziran)
aktifMüşterinin sipariş verdiği her ayı bir kez listelermüşteri 17 → 24282, 24284, 24290
farkHer aktif ay için kohorttan bu yana geçen ay sayısı24284 − 24282 = 2
boyutKohort başına müşteri sayısı (oranların paydası)24282 → 9 müşteri
son SELECTKoşullu sayımla matrisi kurar, oranı kohort büyüklüğüne böler2023, 6, 9, m1, m2, m3, ilk6ay

Neden ay numarası?

İki tarih arasındaki “ay farkı” SQL türüne göre farklı hesaplanır (SQL Server DATEDIFF(month) ay sınırlarını sayar, PostgreSQL AGE gün hassasiyetinde çalışır). Yıl × 12 + ay dönüşümü her SQL türünde aynı sonucu verir ve aritmetiği basitleştirir. Geri dönüşüm: yıl = FLOOR((no − 1) / 12), ay = (no − 1) % 12 + 1. FLOOR gerekir; çünkü MySQL, Oracle ve BigQuery tam sayıları da ondalıklı böler.

Sık hatalar

Alıştırmalara geç (6)