SQL Interview Prep · Ders

Kayıp ve Geri Dönüş Sorguları

Ayrılan kullanıcıların ve bir aradan sonra geri dönenlerin belirlenmesi.

Kayıp ve Geri Dönüş Sorguları, CoddyKit'te ücretsiz bir SQL Interview Prep dersidir. Bu, 4 dersinin 4. 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 Tutmanın Ters Yüzü

Elde tutma kimlerin kaldığını ölçüyorsa, kayıp kimlerin ayrıldığını, yeniden etkinleşme ise kimlerin geri döndüğünü ölçer. Mülakatçılar bunları elde tutma ile birlikte ele alır; çünkü bu kavramlar, etkinliğin yokluğu hakkında akıl yürütebildiğinizi gösterir ve bu, varlığı saymaktan daha zordur.

Tekrarlanan püf noktası şudur: var olmayan satırlara filtre uygulayamazsınız. Kayıp sorguları temelde bir kullanıcının son etkinliği ile şimdi arasındaki (veya bir sonraki etkinliği arasındaki) boşluğu bulmakla ilgilidir.

Kaybı Kesin Olarak Tanımlama

Bir pencere belirtilmeden "kayıp" ifadesinin anlamı yoktur. Yaygın bir tanım şöyledir: son 30 gün içinde hiç etkinliği olmayan kullanıcı kayıp sayılır. 30 günlük etkin olmama eşiği, kesinleştirmeniz gereken işletme tercihidir.

Abonelik ürünlerinde kayıp, bunun yerine iptal edilmiş veya süresi dolmuş abonelik, yani etkinlik boşluğundan farklı olarak bir durum değişikliği anlamına gelebilir. SQL yazmadan önce hangi modelin geçerli olduğunu netleştirin.

Kullanıcı Başına Son Etkinlik

Etkinlik boşluğuna dayalı kaybın temeli, her kullanıcının en son olayıdır. Kullanıcıya göre GROUP yapın ve olay tarihinin MAX değerini alın.

Bugünle karşılaştırılan bu tek değer, kullanıcının ne kadar süredir sessiz olduğunu gösterir. Sonraki her işlem, bu son görülme tarihiyle yapılan bir karşılaştırmadır.

SELECT
  user_id,
  MAX(event_at::date) AS last_active
FROM events
GROUP BY user_id;

Kayıp Kullanıcılar Sorgusu

Son etkinliği 30 günden daha eskiyse kullanıcı kayıp sayılır. last_active değerini CURRENT_DATE - 30 ile karşılaştırın. En son olayı bu kesim tarihinden önce olan herkes sessizleşmiştir.

İşlemin toplulaştırmadan sonra gerçekleştiğine dikkat edin: önce kullanıcı başına bir satıra indirger, ardından boşluğu sınarsınız. Ham olayları tarihe göre filtrelemek yalnızca bir pencere içinde kimlerin etkin olmadığını gösterir; genel olarak kimlerin kayıp olduğunu göstermez.

WITH last_seen AS (
  SELECT user_id, MAX(event_at::date) AS last_active
  FROM events
  GROUP BY user_id
)
SELECT user_id, last_active
FROM last_seen
WHERE last_active < CURRENT_DATE - INTERVAL '30 days';

Kayıp Oranını Sayma

Kayıp oranı, ilgili tabandaki kayıp kullanıcıların oranıdır; bu taban çoğu zaman dönemin başlangıcında etkin olan kullanıcılardır. Kayıp kullanıcıları ve toplam kullanıcıları tek geçişte saymak için koşullu toplulaştırma kullanın; ardından 100.0 ve NULLIF ile dikkatli biçimde bölme yapın.

Mülakatta paydayı açıkça belirtin: tüm zamanlardaki kullanıcılar üzerinden hesaplanan kayıp ile daha önce etkin olmuş kullanıcılar üzerinden hesaplanan kayıp farklı ölçümlerdir.

WITH last_seen AS (
  SELECT user_id, MAX(event_at::date) AS last_active
  FROM events GROUP BY user_id
)
SELECT
  COUNT(*) FILTER (
    WHERE last_active < CURRENT_DATE - INTERVAL '30 days'
  ) AS churned,
  COUNT(*) AS total_users,
  ROUND(100.0 * COUNT(*) FILTER (
    WHERE last_active < CURRENT_DATE - INTERVAL '30 days')
    / NULLIF(COUNT(*), 0), 1) AS churn_pct
FROM last_seen;

Küme Mantığıyla Dönemden Döneme Kayıp

Başka bir çerçeve de şudur: geçen ay etkin olup bu ay etkin olmayanlar kimlerdir? Bu bir küme farkıdır. Geçen ay etkin olan kullanıcıların kümesini ve bu ay etkin olan kullanıcıların kümesini oluşturun; ardından ilk kümede olup ikincide olmayan üyeleri bulun.

Bunu EXCEPT, bir LEFT JOIN / IS NULL karşıt birleştirmesi veya NOT EXISTS ile ifade edebilirsiniz. Karşıt birleştirme en taşınabilir yöntemdir ve mülakatçıların en sık görmek istediği yöntemdir.

WITH last_month AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_at >= DATE '2024-04-01' AND event_at < DATE '2024-05-01'
),
this_month AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_at >= DATE '2024-05-01' AND event_at < DATE '2024-06-01'
)
SELECT user_id FROM last_month
EXCEPT
SELECT user_id FROM this_month;

Anti-birleştirme Biçimi

Bu dönemdeki kullanıcı kaybı sorgusunun anti-birleştirme biçimi: bu ayın etkin kullanıcılarını geçen ayın etkin kullanıcılarına LEFT JOIN ile bağlayın, ardından eşleşmenin NULL olduğu satırları tutun. Bunlar geçen ay bulunan ancak bu ay bulunmayan, yani kaybedilen kullanıcılardır.

NOT EXISTS de aynı derecede iyi bir yanıttır ve NULL değerlerini güvenli biçimde işler. İç küme NULL değerleri içerebiliyorsa NOT IN kullanımının riskli olacağını belirtin; bu klasik bir tuzaktır.

SELECT lm.user_id
FROM last_month lm
LEFT JOIN this_month tm ON tm.user_id = lm.user_id
WHERE tm.user_id IS NULL;

Yeniden Etkinleşmeyi Tanımlama

Yeniden etkinleşme (başka bir deyişle yeniden etkinleştirme), kaybedilmiş durumdayken yeniden etkinleşen kullanıcıdır. Belirleyici özellik, zaman çizelgesindeki bir boşluktur: etkinlik, ardından kayıp eşiğinden daha uzun bir sessizlik dönemi ve sonra yeniden etkinlik.

Dolayısıyla bu ay yeniden etkinleşen kullanıcı, şu anda etkin olan, önceki dönemde etkin olmayan ancak daha önceki bir dönemde etkinliği bulunan kullanıcıdır. Kullanıcı kaybının ayna görüntüsüdür.

LAG ile Boşlukları Saptama

Yeniden etkinleşmeyi bulmanın zarif yolu LAG pencere işlevidir: her kullanıcı için her etkinlik döneminde önceki etkinlik dönemine bakın. İki dönem arasındaki boşluk eşiği aşarsa, mevcut dönem yeniden etkinleşme dönemidir.

LAG, kendi kendine birleştirme gereksinimini ortadan kaldırır ve okunaklıdır. Kullanıcıya göre bölümlendirin, etkinlik dönemine göre sıralayın ve her dönemi kendisinden önceki dönemle karşılaştırın.

WITH monthly AS (
  SELECT DISTINCT user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
),
gaps AS (
  SELECT user_id, active_month,
    LAG(active_month) OVER (
      PARTITION BY user_id ORDER BY active_month
    ) AS prev_month
  FROM monthly
)
SELECT user_id, active_month AS resurrected_month
FROM gaps
WHERE prev_month IS NOT NULL
  AND active_month > prev_month + INTERVAL '1 month';

Yeni, Yeniden Etkinleşen ve Elde Tutulan Kullanıcılar

Eksiksiz bir etkinlik sınıflandırma sorgusu, bu dönemde etkin olan her kullanıcıyı şu kategorilerden biriyle etiketler: yeni (önceki etkinlik yok), elde tutulan (önceki dönemde de etkin) veya yeniden etkinleşen (önceki etkinlik var ancak arada boşluk bulunuyor). LAG işlevinden gelen prev_month bu üç sınıflandırmanın da temelidir.

prev_month IS NULL → yeni
prev_month = active_month - 1 → elde tutulan
aksi hâlde (bir boşluk) → yeniden etkinleşen

Bu ayrıntıyı üretmek güçlü ve eksiksiz bir yanıttır.

SELECT user_id, active_month,
  CASE
    WHEN prev_month IS NULL THEN 'new'
    WHEN active_month = prev_month + INTERVAL '1 month' THEN 'retained'
    ELSE 'resurrected'
  END AS user_state
FROM gaps;

NULL Tuzaklı NOT IN

Son bir tehlikeli nokta. Kullanıcı kaybını WHERE user_id NOT IN (SELECT user_id FROM this_month) biçiminde yazarsanız ve bu alt sorgu tek bir NULL bile döndürürse, NOT IN ifadesi NULL karşısında UNKNOWN olarak değerlendirildiği için sonuç kümesinin tamamı boş olur.

NULL değerleriyle doğru çalışan NOT EXISTS veya LEFT JOIN / IS NULL anti-birleştirmesini tercih edin. Bu farkı sorulmadan belirtmek, elde tutma mülakatlarında güvenilir bir kıdemli aday göstergesidir.

-- safe anti-join instead of NOT IN
SELECT lm.user_id
FROM last_month lm
WHERE NOT EXISTS (
  SELECT 1 FROM this_month tm
  WHERE tm.user_id = lm.user_id
);

Kısa Kontrol

Geçen ay etkin olup bu ay etkin olmayan kullanıcıları bulmak istiyorsunuz. Bir ekip arkadaşınız WHERE user_id NOT IN (SELECT user_id FROM this_month) yazmış ve açıkça kullanıcı kaybına uğrayanlar olmasına rağmen sorgu sıfır satır döndürüyor. En güvenli düzeltme nedir?

Özet: Kullanıcı Kaybı ve Yeniden Etkinleşme

Kullanıcı kaybı ve yeniden etkinleşmenin temelleri:

Kullanıcı kaybını bir etkinliksizlik eşiğiyle (ör. 30 gün boyunca etkinlik olmaması) veya abonelik durumu değişikliğiyle tanımlayın; hangisini kullandığınızı netleştirin.
Her kullanıcının MAX(son etkinlik) değerini hesaplayın, ardından bunu CURRENT_DATE - threshold ile karşılaştırın.
Dönemler arası kullanıcı kaybı bir küme farkıdır: EXCEPT, NOT EXISTS veya LEFT JOIN / IS NULL anti-birleştirmesi kullanın.
Yeniden etkinleşme zaman çizelgesindeki bir boşluktur; kullanıcıları yeni / elde tutulan / yeniden etkinleşen olarak sınıflandırmak için LAG kullanın.
NULL değerleri mümkün olduğunda NOT IN kullanmaktan kaçının; bu ifade sonucu sessizce boşaltır.

Sıkça Sorulan Sorular

“Kayıp ve Geri Dönüş Sorguları” dersi ücretsiz mi?

Evet — “Kayıp ve Geri Dönüş Sorguları” 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.

“Kayıp ve Geri Dönüş Sorguları” dersinde ne öğreneceğim?

Ayrılan kullanıcıların ve bir aradan sonra geri dönenlerin belirlenmesi. 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 4. dersidir.

“Kayıp ve Geri Dönüş Sorguları” 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