【 Interview Questions 】

There is a “user login record form”, which contains two fields: user ID and date.

Query the maximum number of consecutive login days per user per month in 2021.

【 答 案 】

  1. A universal template for continuous problems

In “Pinduoduo Interview Questions: How to Find content that Appears N times in a row?” We’ve seen how to solve the “continuum problem” and we’ve sent out a universal template that uses window functions to solve the continuum problem.

2. Window functions

The window function lead can be used by:

The default value is the value returned by the function when N rows up or N rows down are out of the range of table rows and columns, or Null if no default value is specified.

The window function lead can get the NTH value after each field and generate a new column.

The user’s current login date is one day different from the next login date in this month.

We can first use the window function lead to get the “user’s next login date in that month” :

When the user logs in on the last day of the month, date is recorded as the last login date of the month. If this parameter is not set, Null is returned, which is not understandable.

From the results, we can get the following information:

1) When the “date” is only one day different from the “user’s next login date in the current month”, the user’s login is continuous;

2) When the difference between the “Date” and the “user’s next login date in the current month” is more than one day, the user’s login is the last day of continuous login (or only one day login);

3) When “user’s next login date of the month” equals “last login date of the month”, that is, the user logs in on the last day of the month.

In this way, you can judge the situation of continuous login of users.

The next step is to solve the user each consecutive login days.

  1. The subquery

There is a certain relationship between the number of consecutive login days of users and the login order of users. At this time, we can first query the reading order of users in this month with a subquery and use the window function row_number:

It can be seen that when the continuity terminates, that is:

1) The difference between “Date” and “user’s next login date in the current month” is more than one day;

2) “User’s next login date of the current month” equals “Last login date of the current month”;

Two things.

After filtering out these two cases, the number of consecutive login days is: the current login order minus the previous login order.

If last login order is Null, replace it with 0 (using the coalesce function). In this case, login order in each month minus Last login order is the number of consecutive login days.

  1. meta-analysis

Finally, select “maximum number of consecutive days per user per month” and use the group by function.

【 答 案 】 B

1. Examine the understanding of window function, to “monkey from zero to learn SQL” in the window function to solve the 4 types of interview questions to remember;

2. What do you know about the subquery?

3. To examine the understanding of the continuous problem, you can use the universal template.

The picture

Recommended: Learn SQL from zero?