זיהוי רצפים בהתחברות

תרגול זיהוי פעולות רצופות

מתקדם
Published

September 5, 2023

ברוכים הבאים לתרגיל הראשון בניוזלטר!

הדאטה

התרגיל יורכב משתי שאלות. בטבלה למטה ניתן לראות ניסיונות התחברות של יוזרים (success / fail) בנקודות זמן שונות.

SELECT TOP 10 * FROM LOGINS;
Displaying records 1 - 10
user_id login_date login
3 2022-08-12 success
4 2022-08-26 success
5 2022-07-17 success
5 2022-11-02 success
2 2023-06-07 success
1 2023-05-07 success
5 2023-04-28 fail
3 2023-04-26 success
2 2023-02-05 success
5 2022-12-22 fail

שאלות

שאלה 1

עבור כל יוזר החזירו את רצף הצלחות התחברות הגדול ביותר.

לדוג’, עבור יוזר 2 עם ההתחברויות הבאות:

נחזיר את הרשומה הבאה (שכן ניתן לראות למעלה שיש לו רצף של 6 הצלחות התחברות כמספר הגבוה ביותר):

1 records
user_id most_consecutive_logins
2 6

שאלה 2

ברגע שיוזרים נכשלים בהתחברות שלוש פעמים באופן רצוף החשבון שלהם ננעל. עבור כל יוזר עם שלוש כשלונות התחברות או יותר, החזירו את התאריך (login_date) בו הוא קיבל לראשונה את הכשלון השלישי באופן רצוף.

לדוג’, עבור יוזר 1:

SELECT TOP 7 * FROM LOGINS
where user_id = 1
order by login_date;
7 records
user_id login_date login
1 2022-07-09 fail
1 2022-07-15 fail
1 2022-07-20 success
1 2022-08-12 fail
1 2022-08-15 fail
1 2022-08-22 fail
1 2022-09-13 success

ניתן לראות שבניסיון התחברות ה-4, 5, ו-6 הוא נכשל לראשונה לפחות שלוש פעמים. לכן נחזיר עבורו את הערך 2022-08-22.

1 records
user_id FIRST_3rd_failed_login
1 2022-08-22

נסו בעצמכם

אונליין

נסו בחלון טקסט למטה (SQLite):

-- EDIT THIS QUERY:
SELECT *
from logins
LIMIT 10;

נסו על המחשב שלכם

הצג קוד ליצירת טבלה

מוזמנים להשתמש בקוד הבא ליצירת טבלה במחשב האישי שלכם (יש סימן להעתקה בצד ימין למעלה של המלבן כשמעבירים את העכבר מעליו):


--drop table if exists logins; /* Drop with caution */

create table logins (
    user_id INT,
    login_date DATE,
    login VARCHAR(50)
);
insert into logins (user_id, login_date, login) values (3, '8/12/2022', 'success');
insert into logins (user_id, login_date, login) values (4, '8/26/2022', 'success');
insert into logins (user_id, login_date, login) values (5, '7/17/2022', 'success');
insert into logins (user_id, login_date, login) values (5, '11/2/2022', 'success');
insert into logins (user_id, login_date, login) values (2, '6/7/2023', 'success');
insert into logins (user_id, login_date, login) values (1, '5/7/2023', 'success');
insert into logins (user_id, login_date, login) values (5, '4/28/2023', 'fail');
insert into logins (user_id, login_date, login) values (3, '4/26/2023', 'success');
insert into logins (user_id, login_date, login) values (2, '2/5/2023', 'success');
insert into logins (user_id, login_date, login) values (5, '12/22/2022', 'fail');
insert into logins (user_id, login_date, login) values (1, '4/22/2023', 'fail');
insert into logins (user_id, login_date, login) values (4, '6/29/2023', 'success');
insert into logins (user_id, login_date, login) values (3, '4/19/2023', 'fail');
insert into logins (user_id, login_date, login) values (3, '4/6/2023', 'fail');
insert into logins (user_id, login_date, login) values (5, '4/16/2023', 'fail');
insert into logins (user_id, login_date, login) values (2, '5/22/2023', 'success');
insert into logins (user_id, login_date, login) values (5, '3/10/2023', 'fail');
insert into logins (user_id, login_date, login) values (4, '6/23/2023', 'fail');
insert into logins (user_id, login_date, login) values (4, '1/18/2023', 'fail');
insert into logins (user_id, login_date, login) values (2, '4/27/2023', 'success');
insert into logins (user_id, login_date, login) values (5, '11/6/2022', 'fail');
insert into logins (user_id, login_date, login) values (3, '11/18/2022', 'fail');
insert into logins (user_id, login_date, login) values (2, '9/17/2022', 'fail');
insert into logins (user_id, login_date, login) values (2, '11/14/2022', 'success');
insert into logins (user_id, login_date, login) values (3, '7/23/2022', 'fail');
insert into logins (user_id, login_date, login) values (1, '9/13/2022', 'success');
insert into logins (user_id, login_date, login) values (2, '7/3/2022', 'success');
insert into logins (user_id, login_date, login) values (1, '12/9/2022', 'success');
insert into logins (user_id, login_date, login) values (4, '4/3/2023', 'fail');
insert into logins (user_id, login_date, login) values (5, '10/11/2022', 'success');
insert into logins (user_id, login_date, login) values (4, '3/16/2023', 'fail');
insert into logins (user_id, login_date, login) values (5, '1/10/2023', 'success');
insert into logins (user_id, login_date, login) values (3, '12/3/2022', 'fail');
insert into logins (user_id, login_date, login) values (3, '7/2/2022', 'success');
insert into logins (user_id, login_date, login) values (1, '10/11/2022', 'fail');
insert into logins (user_id, login_date, login) values (1, '2/24/2023', 'fail');
insert into logins (user_id, login_date, login) values (5, '9/11/2022', 'fail');
insert into logins (user_id, login_date, login) values (3, '4/11/2023', 'fail');
insert into logins (user_id, login_date, login) values (4, '6/3/2023', 'fail');
insert into logins (user_id, login_date, login) values (2, '1/30/2023', 'fail');
insert into logins (user_id, login_date, login) values (4, '10/21/2022', 'fail');
insert into logins (user_id, login_date, login) values (1, '7/9/2022', 'fail');
insert into logins (user_id, login_date, login) values (1, '8/12/2022', 'fail');
insert into logins (user_id, login_date, login) values (1, '5/30/2023', 'fail');
insert into logins (user_id, login_date, login) values (3, '8/11/2022', 'fail');
insert into logins (user_id, login_date, login) values (2, '10/22/2022', 'success');
insert into logins (user_id, login_date, login) values (3, '1/6/2023', 'fail');
insert into logins (user_id, login_date, login) values (1, '11/10/2022', 'fail');
insert into logins (user_id, login_date, login) values (2, '4/2/2023', 'success');
insert into logins (user_id, login_date, login) values (2, '7/17/2022', 'fail');
insert into logins (user_id, login_date, login) values (2, '8/6/2022', 'fail');
insert into logins (user_id, login_date, login) values (4, '1/11/2023', 'success');
insert into logins (user_id, login_date, login) values (2, '2/20/2023', 'fail');
insert into logins (user_id, login_date, login) values (2, '1/9/2023', 'success');
insert into logins (user_id, login_date, login) values (3, '1/3/2023', 'success');
insert into logins (user_id, login_date, login) values (2, '4/17/2023', 'success');
insert into logins (user_id, login_date, login) values (5, '3/7/2023', 'fail');
insert into logins (user_id, login_date, login) values (4, '6/26/2023', 'fail');
insert into logins (user_id, login_date, login) values (5, '6/30/2023', 'fail');
insert into logins (user_id, login_date, login) values (5, '2/1/2023', 'fail');
insert into logins (user_id, login_date, login) values (3, '3/26/2023', 'success');
insert into logins (user_id, login_date, login) values (5, '11/13/2022', 'success');
insert into logins (user_id, login_date, login) values (5, '8/11/2022', 'fail');
insert into logins (user_id, login_date, login) values (5, '1/30/2023', 'success');
insert into logins (user_id, login_date, login) values (3, '12/2/2022', 'fail');
insert into logins (user_id, login_date, login) values (3, '9/27/2022', 'fail');
insert into logins (user_id, login_date, login) values (1, '10/26/2022', 'success');
insert into logins (user_id, login_date, login) values (2, '2/23/2023', 'fail');
insert into logins (user_id, login_date, login) values (4, '12/25/2022', 'fail');
insert into logins (user_id, login_date, login) values (1, '8/22/2022', 'fail');
insert into logins (user_id, login_date, login) values (5, '5/27/2023', 'fail');
insert into logins (user_id, login_date, login) values (5, '8/17/2022', 'success');
insert into logins (user_id, login_date, login) values (2, '10/2/2022', 'success');
insert into logins (user_id, login_date, login) values (1, '7/15/2022', 'fail');
insert into logins (user_id, login_date, login) values (3, '2/6/2023', 'fail');
insert into logins (user_id, login_date, login) values (4, '6/5/2023', 'fail');
insert into logins (user_id, login_date, login) values (1, '8/15/2022', 'fail');
insert into logins (user_id, login_date, login) values (3, '8/3/2022', 'success');
insert into logins (user_id, login_date, login) values (4, '12/17/2022', 'fail');
insert into logins (user_id, login_date, login) values (3, '9/11/2022', 'success');
insert into logins (user_id, login_date, login) values (5, '7/6/2022', 'success');
insert into logins (user_id, login_date, login) values (5, '1/14/2023', 'success');
insert into logins (user_id, login_date, login) values (1, '11/4/2022', 'fail');
insert into logins (user_id, login_date, login) values (3, '11/18/2022', 'success');
insert into logins (user_id, login_date, login) values (2, '12/17/2022', 'success');
insert into logins (user_id, login_date, login) values (4, '5/2/2023', 'success');
insert into logins (user_id, login_date, login) values (5, '8/14/2022', 'success');
insert into logins (user_id, login_date, login) values (1, '7/20/2022', 'success');
insert into logins (user_id, login_date, login) values (2, '10/27/2022', 'success');
insert into logins (user_id, login_date, login) values (2, '2/24/2023', 'success');
insert into logins (user_id, login_date, login) values (5, '9/17/2022', 'fail');
insert into logins (user_id, login_date, login) values (5, '10/5/2022', 'fail');
insert into logins (user_id, login_date, login) values (5, '1/12/2023', 'fail');
insert into logins (user_id, login_date, login) values (4, '4/30/2023', 'fail');
insert into logins (user_id, login_date, login) values (4, '6/14/2023', 'success');
insert into logins (user_id, login_date, login) values (2, '10/5/2022', 'fail');
insert into logins (user_id, login_date, login) values (3, '7/24/2022', 'fail');
insert into logins (user_id, login_date, login) values (4, '6/3/2023', 'fail');
insert into logins (user_id, login_date, login) values (2, '11/27/2022', 'success');
insert into logins (user_id, login_date, login) values (1, '3/15/2023', 'success');

בהצלחה!

תשובות

סרטון הסבר

1.

הצג קוד 
WITH GROUPS AS (
  SELECT user_id,
  login,
  login_date,
    ROW_NUMBER() OVER(PARTITION BY user_id order by login_date) - ROW_NUMBER() OVER(PARTITION BY user_id, LOGIN order by login_date asc) as grp
  FROM LOGINS
),

login_ranks as (
  SELECT user_id,
    COUNT(*) AS most_consecutive_logins,
    ROW_NUMBER() OVER(partition by user_id ORDER BY count(*) desc) as rnk
  FROM GROUPS
  where login = 'success'
  GROUP BY user_id,
    GRP
  )
  
SELECT user_id,
  most_consecutive_logins
FROM LOGIN_RANKS
WHERE RNK = 1
5 records
user_id most_consecutive_logins
1 1
2 6
3 2
4 1
5 2

2.

הצג קוד 
WITH GROUPS AS (
  SELECT user_id,
  login,
  login_date,
    ROW_NUMBER() OVER(PARTITION BY user_id order by login_date) - ROW_NUMBER() OVER(PARTITION BY user_id, LOGIN order by login_date asc) as grp
  FROM LOGINS
),

grp_stamps as (
SELECT user_id,
  login_date,
  rn_within_group = ROW_NUMBER() OVER(PARTITION BY user_id, GRP order by grp, login_date)
FROM GROUPS
where login = 'fail'
)

select user_id, 
  min(login_date) AS FIRST_3rd_failed_login
from grp_stamps
WHERE rn_within_group = 3
GROUP BY user_id
5 records
user_id FIRST_3rd_failed_login
1 2022-08-22
2 2022-09-17
3 2023-04-19
4 2022-12-25
5 2022-10-05