-- QUESTION3: You're interviewing at a large social media company and are given two tables: registrations and logins. Each table consists of a user id and a date timestamp of the event. Each user has only one registration entry but could have multiple login entries (or none). The question is to write a query that will give the number of times each user login within their first week of registration (including none).

CREATE TABLE registration ([user_id] char(1), [registration_date] date)

INSERT INTO registration ([user_id], [registration_date])

VALUES ('1','2020-01-15')

,('2','2020-02-01')

,('3','2020-03-10')

,('4','2020-04-07')

,('5','2020-05-25')

;

CREATE TABLE logins ([user_id] char(1), [login_date] date)

INSERT INTO logins ([user_id], [login_date])

VALUES ('1','2020-01-17')

,('1','2020-01-19')

,('1','2020-01-20')

,('1','2020-01-25')

,('2','2020-02-01')

,('2','2020-02-05')

,('2','2020-02-08')

,('4','2020-04-08')

,('4','2020-04-09')

,('4','2020-04-10')

,('4','2020-04-15')

,('4','2020-04-20')

,('5','2020-05-31')

,('5','2020-06-05')

;

答案:

SELECT a.user_id,COUNT(a.user_id)

FROM logins a,registration b

WHERE a.user_id=b.user_id

AND a.login_date <DATE_ADD(b.registration_date, INTERVAL 7 DAY)

GROUP BY a.user_id

報(bào)錯(cuò): “7”附近的語法錯(cuò)誤。

想知道哪里錯(cuò)了, 具體的正確語句該如何寫?