MyscherzoTR Window function ya da CTE kullanılarak yapılabilir gibi ama bunlar için MySQL 8+ gerekiyor. Ben MySQL 5.7 kullandığınızı varsayarak klasik SQL ile yapmaya çalıştım. Sanırım istediğinizi yapabildim. Şöyle bir tablonuz olduğunu düşünün:
logins
+---------+---------------------+
| user_id | created_at |
+---------+---------------------+
| 1 | 2023-04-17 11:00:00 |
| 1 | 2023-04-18 11:00:00 |
| 1 | 2023-04-20 11:00:00 |
| 1 | 2023-04-22 11:00:00 |
| 2 | 2023-04-17 11:00:00 |
| 2 | 2023-04-18 11:00:00 |
| 2 | 2023-04-19 11:00:00 |
| 3 | 2023-04-14 11:00:00 |
| 3 | 2023-04-16 11:00:00 |
+---------+---------------------+
Kullanıcıların giriş tarihlerini tutuyorsunuz. Kullanıcının aynı gün girdiklerini dahil etmedim. Saatlerin aynı olmasına da takılmayın örnek veri.
alihankoc kullanıcı her giriş yaptığında son aktivite gününe bakarız, eğer bugün ise hiç bişe yapmayız, eğer dün ise streak countu bir arttırırız ve son aktivite tarihini bugüne çekeriz,
Bundan yola çıkarak bunu SQL ile yapmaya çalıştım:
SET @streak := 0;
SET @last_login := NULL;
SELECT user_id,
/*
Burada son giriş yaptığı günden bir sonraki gün kayıt içinde varsa streak değerini
1 arttırıyoruz, yoksa başlangıç olarak 1 vereceğiz. İlk kayıtta henüz @last_login
bir değer almadığı için hep 1'den başlayacak:
IF(şart, true ise, false ise)
*/
@streak := IF(DATE_ADD(@last_login, INTERVAL 1 DAY) = DATE(created_at), @streak + 1, 1) AS streak,
/*
Sonra son giriş tarihini kayıt döngüsünde kullanmak için kaydediyoruz. Böylece cursor
sonraki satıra geçtiğinde üstteki IF devreye girerek ertesi gün varsa streak değerini
arttıracak:
*/
@last_login := DATE(created_at) AS last_login
FROM logins
/*
Kayıt döngüsünü doğru kontrol yapabilmesi için kullanıcı ve güne göre
sıralayarak kullanıcı bazında ertesi günü doğru bulmasını sağlıyoruz:
*/
ORDER BY user_id, created_at
Bu sorguyu yazdığımda bana şu sonucu verdi:
+---------+--------+------------+
| user_id | streak | last_login |
+---------+--------+------------+
| 1 | 1 | 2023-04-17 |
| 1 | 2 | 2023-04-18 |
| 1 | 1 | 2023-04-20 |
| 1 | 1 | 2023-04-22 |
| 2 | 1 | 2023-04-17 |
| 2 | 2 | 2023-04-18 |
| 2 | 3 | 2023-04-19 |
| 3 | 1 | 2023-04-14 |
| 3 | 1 | 2023-04-16 |
+---------+--------+------------+
Burada aslında her geçen gün için tüm streakları sırayla bulmuş olduk. Sizin istediğiniz en büyük yani en son girdikleri tarihten itibaren olduğu için mecburen bunu başka bir SELECT ile alacağız. Bu durumda son sorgu şu şekilde olmuş oluyor:
SET @streak := 0;
SET @last_login := NULL;
SELECT user_id, MAX(streak) AS streak
FROM (SELECT user_id,
@streak := IF(DATE_ADD(@last_login, INTERVAL 1 day) = DATE(created_at), @streak + 1, 1) AS streak,
@last_login := DATE(created_at) AS last_login
FROM logins
ORDER BY user_id, created_at) s
GROUP BY user_id
+---------+--------+
| user_id | streak |
+---------+--------+
| 1 | 2 |
| 2 | 3 |
| 3 | 1 |
+---------+--------+
Bunu da Query Builder ile yazmak isterseniz:
DB::statement('SET @streak := 0');
DB::statement('SET @last_login := NULL');
DB::table('logins')
->select([
'user_id',
DB::raw('MAX(streak) AS streak'),
])
->selectSub(function ($query) {
$query->select([
'user_id',
DB::raw('@streak := IF(DATE_ADD(@last_login, INTERVAL 1 day) = DATE(created_at), @streak + 1, 1) AS streak'),
DB::raw('@last_login := DATE(created_at) AS login_day'),
])
->from('logins')
->orderBy('user_id', 'created_at');
}, 's')
->groupBy('user_id')
->get();
