SQL Interview Prep · Ders

Elde Tutma Matrisi Oluşturma

Elde tutma tablosu oluşturmak için etkin kullanıcıların kohort ve dönem farkına göre sayılması.

Elde Tutma Matrisi Oluşturma, CoddyKit'te ücretsiz bir SQL Interview Prep dersidir. Bu, 4 dersinin 2. 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.

Elde Tutma Matrisi Nedir

Kohort tanımlamanın devamında ünlü elde tutma matrisi gelir: satırlar kohortları, sütunlar dönem farklarını (0., 1., 2. ay ve devamı) gösterir ve her hücre, ilgili farkta hâlâ aktif olan o kohorttaki kullanıcıların sayısını verir.

Mülakatçılar bunu sever; çünkü kohort atamasını, etkinliklerle yeniden birleştirmeyi, bir dönem farkı hesaplamasını ve sütunlara dönüştürme işlemini bir araya getirmenizi gerektirir. Bu, ürün analitiğindeki en temsilî sorgudur.

İki Girdi

İki şeye ihtiyacınız vardır: her kullanıcının kohort dönemi (önceki dersten) ve kullanıcı başına her aktif dönemin kaydı. Etkinlikler aynı etkinlik tablosundan gelir ve dönem ayrıntı düzeyinde toplulaştırılır.

Bu nedenle sorguyu şöyle planlayın: önce kohort CTE'si, ardından her kullanıcının hangi aylarda aktif olduğunu listeleyen bir etkinlik CTE'si ve son olarak bunları birleştirme.

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

Aktif Dönemleri Listeleme

Etkinlik CTE'si şu soruyu yanıtlar: "Her kullanıcı hangi aylarda aktifti?" Her etkinliği aya kesin ve DISTINCT veya GROUP BY ile yinelenenleri kaldırın; böylece Mart ayında 40 kez aktif olan bir kullanıcı için tek bir Mart satırı oluşur.

Kullanıcı ve ay başına oluşturulan bu listeyi kohortla birleştirerek kullanıcıların dönem farkları boyunca devamlılığını ölçersiniz.

WITH activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT * FROM activity;

Dönem Farkını Hesaplama

Matrisin merkezinde dönem numarası bulunur: belirli bir etkinlik, kullanıcının kohort başlangıcından kaç ay sonra gerçekleşti? Aktif olunan aydan kohort ayını çıkarın.

Postgres'te temiz bir yöntem, iki tarih arasındaki tam ayları saymaktır. Taşınabilir bir formül, yıl farkını 12 ile çarpıp ay farkını ekler; birçok veritabanı motoru da yardımcı işlevler sunar. 0 farkı, kohortun kendi başlangıç ayını ifade eder.

-- months between two month-truncated dates (Postgres)
SELECT
  (EXTRACT(YEAR  FROM active_month) - EXTRACT(YEAR  FROM cohort_month)) * 12
+ (EXTRACT(MONTH FROM active_month) - EXTRACT(MONTH FROM cohort_month))
  AS period_number;

Kohortu Etkinlikle Birleştirme

Kohort CTE'sini etkinlik CTE'siyle user_id üzerinden birleştirin. Her çıktı satırı şunu ifade eder: X kohortunda doğan bu kullanıcı, N ofsetinde etkindi. (kohort, ofset) başına farklı kullanıcıları saymak, matrisin uzun biçimidir.

Her kohort üyesi kendi başlangıç ayında etkin olduğundan, ofset 0 kohort büyüklüğüne eşit olmalıdır; bu, yerleşik bir doğruluk kontrolüdür.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT c.cohort_month, a.active_month, c.user_id
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id;

Uzun Biçimli Elde Tutma Tablosu

Ofset hesaplamasını ekleyin ve toplulaştırın. Artık düzenli bir uzun biçimli sonucunuz var: elde tutulan kullanıcı sayısını içeren her kohort ve ofset için bir satır. Görselleştirme amaçlı olduğundan, birçok mülakatçı bunu doğrudan kabul eder.

Ofset ifadesinin hem SELECT hem de GROUP BY içinde göründüğüne dikkat edin; çünkü bu ifade saklanan bir sütun değil, hesaplanan bir değerdir.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT
  c.cohort_month,
  (EXTRACT(YEAR FROM a.active_month)-EXTRACT(YEAR FROM c.cohort_month))*12
  +(EXTRACT(MONTH FROM a.active_month)-EXTRACT(MONTH FROM c.cohort_month)) AS period_number,
  COUNT(DISTINCT c.user_id) AS retained_users
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id
GROUP BY c.cohort_month, period_number
ORDER BY c.cohort_month, period_number;

Geniş Sütunlara Dönüştürme

Klasik ızgarayı elde etmek için, koşullu toplulaştırma kullanarak ofsetleri sütunlara dönüştürün: her ofset için bir CASE ifadesinin SUM değeri. Bu taşınabilir kalıp, özel PIVOT söz dizimi olmadan her lehçede çalışır.

Her CASE, satırın period_number değeri ilgili sütunla eşleştiğinde 1 üretir; böylece SUM, o ofsette elde tutulan kullanıcıları sayar.

SELECT
  cohort_month,
  COUNT(DISTINCT CASE WHEN period_number = 0 THEN user_id END) AS m0,
  COUNT(DISTINCT CASE WHEN period_number = 1 THEN user_id END) AS m1,
  COUNT(DISTINCT CASE WHEN period_number = 2 THEN user_id END) AS m2,
  COUNT(DISTINCT CASE WHEN period_number = 3 THEN user_id END) AS m3
FROM retention_long
GROUP BY cohort_month
ORDER BY cohort_month;

Sayımlardan Elde Tutma Oranlarına

Mülakatçılar genellikle ham sayıları değil, yüzdeleri ister. Her ofsetteki elde tutulan kullanıcıları kohort büyüklüğüne (ofset 0) bölün. Tamsayı bölmesini önlemek için bir kayan noktalı türe dönüştürme yapın veya 1.0 ile çarpın; bu, burada en sık karşılaşılan sessiz hatadır.

Sonuç bir elde tutma eğrisidir: 0. ayda %100 olan eğri, bir düzeye doğru azalır. Paydaşların aslında önem verdiği ölçüm bu düzeydir.

SELECT
  cohort_month,
  period_number,
  retained_users,
  ROUND(
    100.0 * retained_users
    / MAX(retained_users) OVER (PARTITION BY cohort_month),
    1
  ) AS retention_pct
FROM retention_long
ORDER BY cohort_month, period_number;

Tamsayı Bölmesi Tuzağı

Kesin bir mülakat tuzağı: çoğu altyapıda 120 / 500 işleminin sonucu 0, 0.24 değil, 0 olur; çünkü her iki işlenen de tamsayıdır. Elde tutma yüzdeleri fark edilmeden tamamen sıfır çıkar.

Bir tarafı sayısal hâle getirerek düzeltin: 100.0 ile çarpın, işlenenlerden birini NUMERIC türüne CAST edin veya boş bir kohorta karşı da koruma sağlamak için NULLIF(size, 0) ile bölün. "NULLIF sıfıra bölmeyi de önler" demeniz ek puan kazandırır.

SELECT
  retained_users,
  cohort_size,
  100.0 * retained_users / NULLIF(cohort_size, 0) AS pct
FROM retention_long;

Eksik Ofsetleri Sıfırla Doldurma

Bir kohortta 2. ofsette hiç elde tutulan kullanıcı yoksa, JOIN satır üretmez ve matriste bir boşluk bırakır. Açık bir 0 göstermek için (kohort, ofset) birleşimlerinden oluşan tam ızgarayı üretin ve sayımları buna LEFT JOIN ile ekleyin.

Izgarayı, kohortları bir sayılar/ofsetler listesiyle CROSS JOIN ederek oluşturun; ardından eksik sayımları sıfıra dönüştürün. Mülakatçılar bu boşluğu fark etmenizi takdir eder.

WITH offsets AS (SELECT generate_series(0, 6) AS period_number),
cohorts AS (SELECT DISTINCT cohort_month FROM retention_long)
SELECT
  c.cohort_month, o.period_number,
  COALESCE(r.retained_users, 0) AS retained_users
FROM cohorts c
CROSS JOIN offsets o
LEFT JOIN retention_long r
  ON r.cohort_month = c.cohort_month
 AND r.period_number = o.period_number
ORDER BY c.cohort_month, o.period_number;

Üçgensel Biçim ve Yenilik Yanlılığı

Bir konuşma noktası daha: matris üçgenseldir. Geçen ay başlayan bir kohort henüz 3. ay değerine sahip olamaz; bu nedenle ilerleyen ofsetlere daha az kohort katkıda bulunur.

Bu yüzden kohortlar arasındaki bir sütunun ortalamasını karşılaştırmak, daha eski kohortlara doğru yanlılık oluşturur. Üçgeni olduğu gibi göstereceğinizi veya karşılaştırmaları her kohortun ulaştığı ofsetlerle sınırlayacağınızı belirtin. Bu farkındalık, analistleri yalnızca sorgu yazan kişilerden ayırır.

Hızlı Kontrol

Elde tutma sorgunuz, elde tutulan kullanıcıları kohort büyüklüğüne bölüyor; ancak 0. ay dışındaki tüm yüzdeler 0 olarak yazdırılıyor. En olası neden nedir?

Özet: Elde Tutma Matrisi

Bir mülakatta elde tutma matrisi oluşturmak için:

Her kullanıcıya bir kohort dönemi atayın, ardından her kullanıcının etkin olduğu dönemleri yinelenenlerden arındırarak listeleyin.
Bunları birleştirin ve dönem ofsetini (kohort ile etkinlik arasındaki ay sayısını) hesaplayın.
COUNT(DISTINCT user_id) ile uzun biçimde toplulaştırın; ızgara gerekiyorsa CASE aracılığıyla sütunlara dönüştürün.
Sayımları dikkatli biçimde oranlara dönüştürün; 100.0 ve NULLIF kullanarak tamsayı bölmesinden ve sıfıra bölmeden kaçının.
Sıfır hücrelerini doldurmak için üretilmiş bir ızgaraya LEFT JOIN uygulayın ve matrisin üçgensel olduğunu unutmayın.

Sıkça Sorulan Sorular

“Elde Tutma Matrisi Oluşturma” dersi ücretsiz mi?

Evet — “Elde Tutma Matrisi Oluşturma” 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.

“Elde Tutma Matrisi Oluşturma” dersinde ne öğreneceğim?

Elde tutma tablosu oluşturmak için etkin kullanıcıların kohort ve dönem farkına göre sayılması. 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 2. dersidir.

“Elde Tutma Matrisi Oluşturma” 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