There is a user check-in table, which records each user’s daily check-in. This table contains three columns: date, user ID, and whether the user signed on that day.
【 答 案 】
1. Group and sort
You are required to output two fields: the id of the current check-in user and the number of consecutive check-in days.
In plain English, find the user who checked in that day, and the date of each user’s last check-in, and calculate the number of days between the last check-in date and the current day (during this period, the user checked in every day).
This problem is a grouping sorting problem, this kind of problem to think of “monkey from zero to LEARN SQL” in the window function.
Group users by user ID, find the days when each user ID fails to check in, and sort users by date in descending order. For example, the last missed check-in date is listed first in the chart below.
Because in the sample data given, the user with user ID 1 checks in every day, there is no missed date. So how to calculate the consecutive check-in days of such users?
We can query the start date of the user’s check-in table, set that day as the start date of the user’s check-in, calculate the interval between the date and the current day, and then add 1 to the continuous check-in days of the user.
SQL > select start date from sign-in table
The output table contains only all users who checked in that day. Therefore, two tables are required. Table B is the user ID that checked in on the current day, and table A is the last unchecked date of each user ID.
According to the above description, we know that some users check in every day, but in Table B, this user does not exist. Therefore, when two tables are associated by user ID, if the user ID cannot be associated in table B, the start date of the user check-in table is used to calculate the consecutive check-in days.
The SQL is as follows:
【 答 案 】
If you want to fetch the records with the highest/lowest/NTH value in each group (user/department/month), you can use the window function:
