Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the common SQL interview questions". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Continuous active problem

There are two variations in this problem: (1) An APP user active record table active has uid (user id) and dt (active date) fields to find out the number of users who have been on duty for 3 consecutive days or more. (2) An APP user active record table active has uid (user id) and dt (active date) fields. Finding the maximum number of consecutive active days for each user requires different output data. However, it is necessary to examine the ability to master the window functions lead and lag. The second is an extension on the basis of the first problem, which can also be used as a general solution to this kind of problem. Let's answer the questions in order. First, we build a table for test illustration, like the following figure:

Considering the permission problem, instead of creating a new hive table here, union a temporary table directly in the with clause:

WITH active AS (SELECT 100UID, '2021-04-01'dt UNION ALL SELECT 101UID,' 2021-04-01'dt UNION ALL SELECT 102UID, '2021-04-01'dt UNION ALL SELECT 103UID,' 2021-04-01'dt UNION ALL SELECT 100UID '2021-04-02'dt UNION ALL SELECT 101UID,' 2021-04-02'dt UNION ALL SELECT 102UID, '2021-04-02'dt UNION ALL SELECT 103UID,' 2021-04-02'dt UNION ALL SELECT 104UID '2021-04-02'dt UNION ALL SELECT 100UID,' 2021-04-03'dt UNION ALL SELECT 104UID, '2021-04-03'dt UNION ALL SELECT 101UID,' 2021-04-04'dt UNION ALL SELECT 102UID '2021-04-04'dt UNION ALL SELECT 103UID,' 2021-04-04'dt UNION ALL SELECT 104UID, '2021-04-04'dt UNION ALL SELECT 105UID,' 2021-04-04'dt UNION ALL SELECT 102UID '2021-04-03'dt)

(1) to know that a user is continuously active, it must be necessary to sort all his active records. For the active table, each active user has a record every day. We arrange the active records of each user in chronological order. If the date of the previous record is exactly one day different from that of this record, then the two records are continuous. In other words, the date difference between consecutive attendance records is 1, and by extension, if the user has been on attendance for N days in a row, then any two adjacent records of these N records are 1 day short, and the difference between the start date and the end date of this record is NMUR 1. If a user has been on duty for four consecutive days, then the sixth record from the third day of his attendance record happens to be the start date of his continuous attendance, so, if the difference between the date of Article 6 from each record and the date recorded in this section is exactly 2 days, it means that this period is uninterrupted. We use lead to obtain the date of the previous section 2 record of each record, and calculate the difference with the record date of this article. If the difference is equal to 2, it means that the user has been on duty for 3 days or more. The specific calculation of sql is shown below, it can be found that a total of 3 users have been active for 3 days or more.

Select count (DISTINCT uid) from (SELECT UID, dt, lag (dt,2) over (PARTITION BY UID ORDER BY dt) dt2 FROM active) x where datediff (dt,dt2) = 2

This is counted from back to front, and the same lag function can be used to obtain the date of the second record after each record. If there is a record with a difference equal to 2, it can also indicate that the user has been on duty for 3 days or more. (2) with the first question as the groundwork, let's take a look at the second question. I believe it should be easier for us to come up with ideas. Under this problem, it is not clearly given how many consecutive days to be active, but the maximum number of consecutive active days per user is required. In this way, we can not directly mismatch and subtract, but need to clearly find the start and end time of each continuous active period. So first of all, we have to judge whether each record is continuous with its adjacent records, and if not, it means that the record is the starting point or end point of a continuous active period.

After marking whether all records are breakpoints, we can match the nearest starting point for each record:

According to the subquery in the figure above, we can know the date from which each active record is continuously active, and then calculate the largest difference between the start date and all active records under each user's UID:

Select UID,max (datediff (dt,start_dt)) days from (select UID,dt,max (if (if_continue=0,dt,null)) over (PARTITION BY UID ORDER BY dt) start_dt from (select UID,dt,dt2,if (datediff (dt,dt2) = 1 and 1) if_continue from (SELECT UID,dt, lag (dt) 1) over (PARTITION BY UID ORDER BY dt) dt2 FROM active) x) y) z group by UID

In this way, the maximum number of consecutive active days for all users is obtained:

This is the end of the content of "what are the common SQL interview questions"? thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.