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

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

הדאטה

התרגיל יורכב משתי שאלות. בטבלה למטה ניתן לראות ניסיונות התחברות של יוזרים (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 עם ההתחברויות הבאות:

Show 102550100 entries

Search:

	login	login_date	user_id
1	success	2022-07-03	2
2	fail	2022-07-17	2
3	fail	2022-08-06	2
4	fail	2022-09-17	2
5	success	2022-10-02	2
6	fail	2022-10-05	2
7	success	2022-10-22	2
8	success	2022-10-27	2
9	success	2022-11-14	2
10	success	2022-11-27	2

Showing 1 to 10 of 22 entries

Previous123Next

נחזיר את הרשומה הבאה (שכן ניתן לראות למעלה שיש לו רצף של 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;

Edit

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

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

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

--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