SQL Interview Prep · Ders

İlk Eyleme Göre Kohort Tanımlama

Her kullanıcıya ilk etkinlik tarihine göre bir kohort atanması.

İlk Eyleme Göre Kohort Tanımlama, CoddyKit'te ücretsiz bir SQL Interview Prep dersidir. Bu, 4 dersinin 1. dersidir. Aşağıdan dersin tamamını ücretsiz okuyabilir, sonra tarayıcıda yerleşik kod editörü ve 7/24 yapay zeka koçu ile uygulamalı olarak pratik yapabilirsin. Bu, SQL Interview Prep öğrenme yolunun bir parçasıdır ve ilerlemeniz web ve CoddyKit uygulaması arasında senkronize olur. SQL Interview Prep kursu toplamda 4 dersten oluşur.

Kohortlar Mülakatlarda Neden Karşınıza Çıkar

Ürün analitiği mülakatçısı "bir kohort oluşturun" dediğinde, sizden her kullanıcıyı bir şeyi ilk kez ne zaman yaptığına göre bir gruba atamanızı ve bu grubu zaman içinde izlemenizi bekler.

Kohort, genellikle ilk satın alma, kayıt olma veya giriş gibi aynı dönemde gerçekleşen bir başlangıç etkinliğini paylaşan kullanıcı kümesidir. Kohortların gücü, kullanıcıları aynı ölçütlerle karşılaştırmanıza olanak tanımasıdır: Ocak kohortundaki herkes, kendi Ocak başlangıcından itibaren ölçülür.

İlk alt beceri ve bu derste üzerinde çalışacağınız beceri, her kullanıcının ilk eylem tarihini güvenilir biçimde hesaplamaktır.

Kaynak Tablo

Neredeyse her kohort sorusu bir etkinlik tablosundan başlar: her kullanıcı eylemi için zaman damgası içeren bir satır bulunur. Bir events tablosunu düşünün:

user_id — işlemi yapan kişi
event_type — yaptığı işlem
event_at — zaman damgası olarak ne zaman gerçekleştiği

Mülakatta ayrıntı düzeyini açıkça belirtin: "Bu tabloda her etkinlik için bir satır mı var ve bir kullanıcı birden çok kez görünebilir mi?" Yanıt neredeyse her zaman evettir; tam da bu nedenle kullanıcı başına ilk eylemi elde etmek için toplulaştırma yapmanız gerekir.

CREATE TABLE events (
  user_id    INT,
  event_type VARCHAR(50),
  event_at   TIMESTAMP
);

İlk Eylem = Zaman Damgasının MIN Değeri

Temel işlem basittir: user_id değerine göre gruplayın ve MIN(event_at) değerini alın. Bu minimum değer, kullanıcıyı bir kohorta yerleştiren ilk eylemi, yani anı gösterir.

Herhangi bir pencere işlevi gösterisine geçmeden önce mülakatçıların duymak istediği ilk yanıt budur. Sade bir GROUP BY doğru, okunabilir ve hızlıdır.

SELECT
  user_id,
  MIN(event_at) AS first_action_at
FROM events
GROUP BY user_id;

Belirleyici Etkinliğe Filtreleme

Kohort çoğu zaman herhangi bir etkinlikle değil, belirli bir eylemle tanımlanır. "Kullanıcıları ilk satın almalarına göre kohortlara ayırın" ifadesi, minimumu almadan önce satın alma satırlarını filtrelemeniz gerektiği anlamına gelir.

Filtreyi WHERE içine koyun; böylece MIN yalnızca uygun satırları görür. Sık rastlanan bir mülakat hatası, tüm etkinlikler üzerinden MIN alıp ardından filtrelemektir. Bu hata, satın almadan önce göz atan kullanıcıların başlangıç tarihinin yanlış belirlenmesine yol açar.

SELECT
  user_id,
  MIN(event_at) AS first_purchase_at
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

Kohort Dönemine Ayırma

Kohort genellikle kesin bir zaman damgası değil, bir dönemdir: "2024-03 kohortu" veya "2024-03-04 haftası" gibi. İlk eylem tarihini, dönem ayrıntı düzeyine indirgeyecek şekilde kesin.

Postgres'te DATE_TRUNC('month', ...) kullanın. MySQL'de DATE_FORMAT(d, '%Y-%m-01'); SQL Server'da ise DATETRUNC(month, d) veya hesaplanmış ayın ilk günü kullanılabilir. Mülakatta kullandığınız SQL lehçesini belirtin; böylece sözdizimi seçiminiz bilinçli görünür.

SELECT
  user_id,
  DATE_TRUNC('month', MIN(event_at)) AS cohort_month
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

CTE İçine Alma

Kullanıcı başına kohort ataması, elde tutma sorgularında tekrar kullanacağınız bir yapı taşıdır; bu nedenle bunu açıkça adlandırılmış bir CTE içinde paketleyin. Böylece sonraki adımlar daha okunabilir olur ve birleştirilebilir parçalarla düşündüğünüzü gösterirsiniz.

Buradan sonra tüm sonraki sorgular, bir kullanıcının hangi gruba ait olduğunu öğrenmek için user_cohort ile birleştirme yapabilir.

WITH user_cohort AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events
  WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT * FROM user_cohort;

Kohort Boyutu: Üyeleri Sayma

Mülakatçının beklediği ilk tutarlılık kontrolü kohort boyutudur: her kohorta kaç kullanıcı ait? Atama CTE'sini cohort_month alanına göre gruplayın ve farklı kullanıcıları sayın.

CTE zaten kullanıcı başına tek satır içerse de önlem olarak COUNT(DISTINCT user_id) kullanın; bu, ayrıntı düzeyini düşündüğünüzü gösterir. Bu sayı, sonraki tüm elde tutma yüzdeleri için payda olur.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT
  cohort_month,
  COUNT(DISTINCT user_id) AS cohort_size
FROM user_cohort
GROUP BY cohort_month
ORDER BY cohort_month;

Pencere İşlevi Seçeneği

Mülakatçılar bazen toplulaştırılmış bir tablo yerine kohort etiketinin her etkinlik satırına eklenmesini ister. Bu durumda pencere işlevi öne çıkar: MIN(event_at) OVER (PARTITION BY user_id), satırları kaldırmadan ilk eylemi hesaplar.

Hem ayrıntılı etkinliklere hem de kohort etiketine tek geçişte ihtiyaç duyduğunuzda bu yaklaşım kullanışlıdır; elde tutma sayımının temeli de budur.

SELECT
  user_id,
  event_at,
  DATE_TRUNC('month',
    MIN(event_at) OVER (PARTITION BY user_id)
  ) AS cohort_month
FROM events
WHERE event_type = 'purchase';

Eşit Değerler ve Yinelenen Kayıt Hatası

Bir kullanıcının tam olarak aynı en erken zaman damgasına sahip iki etkinliği varsa ne olur? MIN bu durumu sorunsuz ele alır: kaç satır aynı değere sahip olursa olsun tek bir minimum değer döndürür; böylece kohort ataması kullanıcı başına tek olarak kalır.

Bunu ROW_NUMBER() ... ORDER BY event_at yaklaşımıyla karşılaştırın. Bu yaklaşımda eşit değerler rastgele sıralanır ve kararlı bir sonuç elde etmek için event_id gibi belirli bir eşitlik bozucu eklemeniz gerekir. Bu farkı sorulmadan belirtmek, kıdemli düzeyde düşündüğünüzü gösterir.

SELECT user_id, event_at,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY event_at, event_id
  ) AS rn
FROM events
WHERE event_type = 'purchase';

Saat Dilimleri ve Gün Sınırı

İnce bir mülakat sorusu: New York'ta saat 11:30 PM'de yapılan bir satın alma UTC'de sonraki gündedir. Kohortlar takvim gününe göre dönemlere ayrılıyorsa, kullanıcının hangi kohorta gireceğini saat dilimi belirler.

Güvenli yanıt şudur: zaman damgalarını UTC olarak depolayın, ardından güne kesmeden önce işletmenin saat dilimine dönüştürün. Ölçüm için "gün"ü hangi saat diliminin tanımladığını açıkça belirtin; çünkü bu tek karar, binlerce kullanıcıyı kohortlar arasında değiştirebilir.

SELECT
  user_id,
  DATE_TRUNC('day',
    MIN(event_at AT TIME ZONE 'America/New_York')
  ) AS cohort_day
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

Dönem Öncesi Kullanıcıları Dışlama

Gerçek analizlerde kohort bir tarih aralığıyla sınırlandırılır; örneğin "1. çeyrekte başlayan kohortlar" gibi. Toplulaştırılmış ilk eylem tarihine göre filtre uygulayın; bu, HAVING koşulu veya CTE üzerindeki dış filtre olmalıdır, ham etkinlikler üzerinde bir WHERE olmamalıdır.

Ham etkinlikleri tarihe göre filtrelemek, ilk satın almasını Aralık ayında yapan ancak 1. çeyrekte de işlem gerçekleştiren bir kullanıcının 1. çeyrek kohortuna yanlışlıkla girmesine neden olur. Her zaman hesaplanmış ilk eylemi temel alın.

WITH user_cohort AS (
  SELECT user_id, MIN(event_at) AS first_at
  FROM events WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT user_id, DATE_TRUNC('month', first_at) AS cohort_month
FROM user_cohort
WHERE first_at >= DATE '2024-01-01'
  AND first_at <  DATE '2024-04-01';

Kısa Kontrol

Bir mülakatçı şöyle soruyor: "Her kullanıcıyı ilk satın alma ayına göre kohortlara ayırın. Kullanıcılar satın almadan önce göz atabilir." Hangi yaklaşım doğrudur?

Özet: Kohort Tanımlama

Kohort tanımlama mülakat sorusu için temel çıkarımlar:

Bir kohort, kullanıcıları uygun ilk eylemlerine göre gruplar.
Belirleyici etkinliğe WHERE içinde filtre uyguladıktan sonra MIN(event_at) ile hesaplayın.
DATE_TRUNC veya kullanılan lehçedeki eşdeğeriyle bir döneme ayırın.
Atamayı yeniden kullanmak için bir CTE içinde paketleyin; COUNT(DISTINCT user_id) kohort boyutunu verir.
Saat diliminin gün sınırına dikkat edin ve tarih aralıklarını asla ham etkinliklere göre değil, hesaplanmış ilk eyleme göre sınırlandırın.

Bunu iyi kavrarsanız sonraki dersteki elde tutma matrisi bir birleştirme işlemine dönüşür.

Sıkça Sorulan Sorular

“İlk Eyleme Göre Kohort Tanımlama” dersi ücretsiz mi?

Evet — “İlk Eyleme Göre Kohort Tanımlama” dersin tüm metni burada web'de ücretsiz olarak okunabilir. Etkileşimli olarak pratik yapmak (yerleşik kod editörü ve 7/24 yapay zeka koçu) ve SQL Interview Prep kursunun geri kalanını açmak için CoddyKit PRO'ya yükselt. SQL Interview Prep kursu toplamda 4 dersten oluşur.

“İlk Eyleme Göre Kohort Tanımlama” dersinde ne öğreneceğim?

Her kullanıcıya ilk etkinlik tarihine göre bir kohort atanması. SQL Interview Prep ile uygulamalı kodu tarayıcıda doğrudan çalıştırarak pratik yaparsın ve 7/24 yapay zeka koçu dersi çalışırken sorularını yanıtlar.

SQL Interview Prep öğrenmeye başlamak için deneyim gerekli mi?

Önceden deneyim gerekmez. CoddyKit'te SQL Interview Prep, başlangıçtan ileri seviyeye kadar yapılandırıldığı için buradan başlayabilir veya başından başlayıp kendi hızında ilerleme yapabilirsin. Bu, 4 dersinin 1. dersidir.

“İlk Eyleme Göre Kohort Tanımlama” dersi ne kadar sürer?

Çoğu CoddyKit dersi yaklaşık 5–10 dakika sürer. Her biri kısa ve etkileşimli olduğu için sabit ilerleme yaparsın ve web ile uygulama arasında tam olarak bıraktığın yerden devam edebilirsin.

Bu SQL Interview Prep dersinde kod yazıp çalıştırabilir miyim?

Evet. Her SQL Interview Prep dersi yerleşik bir kod editörü içerir, bu sayede tarayıcıda gerçek kod yazıp çalıştırabilir ve anlık yapay zeka geri bildirimi alırsın — yerel kurulum gerekli değildir.

Bu kursun tüm dersleri

← SQL Interview Prep Sayfasına Dön