Büyük sorgu 2: olaylardan oturum ve oturum hunisi

Soru

Ürün ekibi “kullanıcı başına” değil “ziyaret başına” dönüşümü görmek istiyor. Ama web_events tablosunda oturum numarası yok; yalnızca kullanıcı ve zaman var. Sektörün yaygın tanımı: aynı kullanıcının iki olayı arasında 30 dakikadan uzun boşluk varsa yeni oturum başlar.

Sorgu

WITH olay AS (           -- 1) Her olayın bir öncekinden sonra geçen süre (saniye)
  SELECT user_id, event_id, event_time, event_type, device,
         ROUND((julianday(event_time) - julianday(LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time, event_id))) * 86400) AS ara_sn
  FROM web_events
),
isaret AS (              -- 2) 30 dakikadan uzun ara (ya da ilk olay) yeni oturum başlatır
  SELECT *, CASE WHEN ara_sn IS NULL OR ara_sn > 1800 THEN 1 ELSE 0 END AS yeni
  FROM olay
),
oturum AS (              -- 3) Kümülatif toplam = kullanıcı içinde oturum numarası
  SELECT *, SUM(yeni) OVER (PARTITION BY user_id ORDER BY event_time, event_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS oturum_no
  FROM isaret
),
ozet AS (                -- 4) Oturum düzeyi: cihaz, olay sayısı, huni adımları
  SELECT user_id, oturum_no,
         MIN(device) AS device,
         COUNT(*) AS olay,
         MAX(CASE WHEN event_type = 'product_view' THEN 1 ELSE 0 END) AS gordu,
         MAX(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) AS sepet,
         MAX(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) AS aldi
  FROM oturum
  GROUP BY user_id, oturum_no
)
SELECT device,                -- 5) Cihaz bazında oturum hunisi
       COUNT(*) AS oturum,
       ROUND(AVG(olay), 1) AS ort_olay,
       ROUND(100.0 * SUM(gordu) / COUNT(*), 1) AS urun_goren,
       ROUND(100.0 * SUM(sepet) / COUNT(*), 1) AS sepete_ekleyen,
       ROUND(100.0 * SUM(aldi) / COUNT(*), 1) AS satin_alan
FROM ozet
GROUP BY device
ORDER BY oturum DESC;

Adım adım

Kalıp: “işaretle ve kümülatif topla”

Bu kalıp yalnızca oturum için değil, her türlü “bir koşul gerçekleşince yeni grup başlar” sorusunda kullanılır: fiyat değişimleri, vardiya başlangıçları, aynı uçağın yerde kaldığı aralıklar. Boşlukları ve adaları ROW_NUMBER farkıyla bulma yöntemine göre avantajı, eşik (30 dakika) koyabilmesidir.