Written test question 66: latest login date of each person of Niuke (I)

• Detailed description: please write an sql statement to query the last login date of each user, and press user_id ascending sort

Problem solving ideas

• Group by userid and take the maximum login time

source code

select user_id, max(date)
from login
group by user_id
order by user_id

Written test question 67: latest login date of each person of Niuke (2)

• Detailed description: please write an sql statement to query each user's last login date, user's name and the name of the device used by the user, and the query results are sorted in ascending order according to the user's name

Problem solving ideas

• The user's name is in the user table, the device's name is in the client table, and the login date is in the login table
• Login and client depend on client_id connection, login and user depend on user_id

source code

select u.name, c.name, date
from login l
         join user u on l.user_id = u.id
         join client c on l.client_id = c.id
where (l.user_id, l.date) in (
    select user_id, max(date)
    from login
    group by user_id
)
order by u.name;

Written test question 68: latest login date of each Niuke (III) [retention rate]**

• Please write an sql statement to query the successful retention rate of the new login user the next day, that is, the probability of logging in again on the first day and the second day, and save 3 digits after the decimal point (rounding after 3 digits)

Problem solving ideas

• Retention rate of the next day: (new users logged in on the first day and users logged in on the second day) / (total users)

• Get total users first

  select count(distinct user_id) from login

• Then find the user who logged in the first day

  select user_id,min(date) from login group by user_id

• At the same time, find the user who also logs in the next day. We can get it through the following steps

  select user_id,date_add(min(date),interval 1 day) from login group by user_id

source code

select round(count(user_id) / (select count (distinct user_id) from login), 3)
from login
where (user_id, date) in (
    select user_id, date_add(min(date), interval 1 day)
    from login
    group by user_id
);

Supplementary knowledge

• The usage of searching the day after a certain day in mysql is: DATE_ADD(yyyy-mm-dd,INTERVAL 1 DAY)

Written test question 69: latest login date of each person of Niuke (4)

• Detailed description: please write an sql statement to query the number of new login users on each date, and the query results are sorted in ascending order by date

Problem solving ideas

• sum+case
• Group according to the user login date to judge whether the current user login is the first login. If so, add 1
  • First login: select user_id, min(date) from login group by user_id

source code

select
       date,
        sum(
        case when (user_id,date) in (select user_id, min(date)from login group by user_id)
        then 1 else 0 end)
from login
group by date
order by date;

Written test question 70: latest login date of each person of Niuke (5)

• Detailed description: please write an sql statement to query the retention rate of new users on the next day on each date. The results retain 3 digits after the decimal point (rounding after 3 digits), and the query results are sorted in ascending order of dates

Problem solving ideas

• After reading the topic, we can convert the topic into, seeking new user table, and retaining user table
• Remind again, the requirement of splitting the topic is very important!!!

source code

select temp1.date, round(if(first_login = 0, 0, ifnull(retain, 0) / first_login), 3) res
from (
         -- Number of new users per day
         select date,
                sum(case
                        when
                                (user_id, date) in (
                                select user_id, min(date)
                                from login
                                group by user_id) then 1
                        else 0 end) first_login
         from login
         group by date
     ) temp1
    left join (
        -- Number of retained users per day
        select date, date_sub(date, interval 1 day) new_date, count(*) retain
        from login
        where (user_id, date) in (
            select user_id, date_add(min(date), interval 1 day)
            from login
            group by user_id
        )
        group by date
    ) temp2 on temp1.date = temp2.new_date
order by date

Written test question 71: latest login date of each person of Niuke (6)

• Detailed description: please write out an sql statement to query the question brushing information, including the user's name and how many questions have been passed in total by a certain day, and the query results are sorted in ascending order of date first, and then in ascending order of name. The data of the day on which there is login but no question brushing does not need to be output

Problem solving ideas

• This topic only needs to pay attention to two points:
  • How to delete data that is logged in but has not been scratched ----- left join
  • How to accumulate the number of questions - window function

source code

with temp as (
    select l.user_id,l.date,number
    from login l
    left join passing_number pn on l.user_id = pn.user_id and l.date = pn.date
)
select name,date,sum(number) over(partition by name order by date) ps_num
from temp
join user on temp.user_id=user.id
where number is not null
order by date,name

Written test question 72: test score (I)

• Detailed description: please write an sql statement to query the average number of scores of each position and sort them in descending order. The results will be kept in 3 digits after the decimal point (rounded after 3 digits)

Problem solving ideas

• Through the average of the scores of each position in the topic, you can know that the group is divided according to the position, then calculate the average value of the scores, and keep three significant figures: round(num, 3)

source code

select job, round(avg(score),3) avg_score
from grade
group by job
order by avg_score desc

Written test question 73: test score (2)

• Detailed description: please write an sql statement to query the attributes of all grade s whose user score is greater than the average score of their job score, and sort them in ascending order of id

Problem solving ideas

• Seeing this question, we should know that we first need to know the average score of the job, so we need to use the table in question 1.
• join through the job field and the grade table, and then the query condition is that the average score is smaller than the score

source code

with temp as (
    select job, round(avg(score), 3) avg_score
    from grade
    group by job
    order by avg_score desc
)
select id, grade.job, score
from temp
         join grade on temp.job = grade.job
where temp.avg_score < grade.score
order by id;

Written test question 74: test score (III)

• Detailed description: please find out the top 2 users in each position score. The results are sorted in ascending order according to the name of language, then in descending order according to the score, and finally in ascending order according to the id of grade

Problem solving ideas

• If you are in the top 2 scores of each position, you will first think of opening a window to get the ranking of each score, and then query those whose ranking is less than or equal to 2
• Then join the two tables

source code

with temp as (    select id,           language_id,           score,           dense_rank() over (partition by language_id order by score desc) rk    from grade)select temp.id, name, scorefrom temp         left join language on temp.language_id = language.idwhere rk <= 2order by name, score desc, id;

Written test question 75: test score (4)

• Detailed description: please write an sql statement to query the range of median positions after each position score is arranged in ascending order, and sort by job in ascending order

Problem solving ideas

• floor function: round down
• Just remember the following solution. It is a fixed writing method for finding the median position

source code

select job,    floor(( count(*) + 1 )/ 2 ) start,    floor(( count(*) + 2 )/ 2 ) endfrom gradegroup by joborder by job;

Written test question 76: test score (5)

• Solution idea: please write an sql statement to query all grade information at the median position of each position score, and sort it in ascending order by id

Problem solving ideas

• Seeing this topic, we first split the topic requirements, first find the position of the median, and then join with the middle table with corresponding score ranking. The join condition is that the job s of the two tables are the same, and the query condition is that the starting point of the median is equal to the ranking or the ending point of the median is equal to the ranking

source code

with temp as (    select job,           floor((count(*) + 1) / 2) start,           floor((count(*) + 2) / 2) end    from grade    group by job    order by job)select id, temp1.job, score, rkfrom (select id, job, score, row_number() over (partition by job order by score desc) rk      from grade) temp1         join temp on temp1.job = temp.jobwhere temp1.rk = temp.start   or temp1.rk = temp.endorder by temp1.id;

Written test question 77: analysis of Niuke's course orders (I)

• Detailed description: please write an sql statement to query the orders of C + + courses, Java courses or Python whose status is successful after 2025-10-15, and follow the order_ id ascending sort of info

Problem solving ideas

• Clarify the query conditions: the purchase time is 2025-10-15, and the successful purchase of C + + or Java or Python
• I didn't write it out at the beginning. The mistake is mainly in the last query condition. We can use in to solve the problem of options

source code

select * from order_infowhere date > '2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')order by id;

Written test question 78: analysis of Niuke's course orders (2)

• Detailed description: please write an sql statement to query two or more users whose status is successfully purchased C + + courses, Java courses or Python courses after 2025-10-15_ ID, and press user_id ascending sort

Problem solving ideas

• Through this topic, we only need to master the grammatical order. Where > group by > having > order by

source code

select user_idfrom order_infowhere date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')group by user_idhaving count(user_id) >= 2order by user_id

Written test question 79: analysis of Niuke's course orders (III)

• Detailed description: please write an sql statement to query the order information of two or more C + + courses, Java courses or Python courses that are successfully purchased by the same user after 2025-10-15, and follow the order_ id ascending sort of info

Problem solving ideas

• The column of order by must be the column in the group by clause. Therefore, you can only use subqueries based on the above
• datediff(date1, date2) returns the number of days between the start time date1 and the end time date2

source code

select *from order_infowhere user_id in (        select user_id        from order_info        where date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')        group by user_id        having count(*)>=2    ) and date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')order by id;

Written test question 80: analysis of Niuke's course orders (4)

• Detailed description: please write an sql statement to query. After 2025-10-15, if a user orders 2 or more C + + courses or Java courses or Python courses with the status of successful purchase, the user's name will be output_ ID, and the date of the first successful C + + course or java course or Python course that meets the above conditions_ buy_ Date, and the number of successful C + + courses, Java courses or Python courses cnt, and the output results are according to user_id ascending sort

Problem solving ideas

• This topic looks very complex, but it becomes much easier when we have the foreshadowing above. But if we don't have the foreshadowing above, can we decompose this topic? This is also an experience in writing sql. Too complex topics are divided into intermediate tables

source code

with tmp as (    select *    from order_info    where user_id in (            select user_id            from order_info            where date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')            group by user_id            having count(*)>=2        ) and date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')    order by id)select user_id, min(date) first_buy_date, count(user_id) cntfrom tmpgroup by user_idorder by user_id;

Written test question 81: analysis of Niuke's course orders (5)

• Detailed description: please write an sql statement to query. After 2025-10-15, if a user orders 2 or more C + + courses or Java courses or Python courses with the status of successful purchase, the user's name will be output_ ID, and the date of the first successful C + + course or java course or Python course that meets the above conditions_ buy_ Date, and the date of the second successful purchase of C + + courses or Java courses or Python courses that meet the above conditions. second_buy_date, and the number of successful C + + courses, Java courses or Python courses cnt, and the output results are according to user_id ascending sort

Problem solving ideas

• If you want to output both the first purchase and the second purchase, you must know the corresponding ranking, so we can use the windowing function.

source code

with tmp as (    select *    from order_info    where user_id in (            select user_id            from order_info            where date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')            group by user_id            having count(*)>=2        ) and date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')    order by id)select user_id, min(date), max(date), cntfrom(    select user_id, date, row_number() over (partition by user_id order by date) rk,       count(user_id) over(partition by user_id) cntfrom tmp        )tmp2where rk<=2group by user_id, cntorder by user_id;

Written test question 82: analysis of Niuke's course orders (6)

• Detailed description: please write an sql statement to query the order id, whether to group and client name information of 2 or more C + + courses or Java courses or Python courses ordered by the same user after 2025-10-15. If the last column is a non group order, the corresponding client name will be displayed. If it is a group order, NULL will be displayed, And according to order_ id ascending sort of info

Problem solving ideas

• According to the returned fields required by the title, we can know that we need to join the two tables, and the title says that if it is a group order, it will display NULL, so we use the order table left join client table

source code

with tmp as (    select *    from order_info    where user_id in (            select user_id            from order_info            where date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')            group by user_id            having count(*)>=2        ) and date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')    order by id)select tmp.id,is_group_buy,namefrom tmpleft join client on tmp.client_id = client.idorder by id;

Written test question 83: analysis of Niuke's course orders (7)

• Detailed description: please write an sql statement to query the source information of two or more C + + courses, Java courses or Python courses ordered by the same user after 2025-10-15. The first column shows the name of the client, if it is a group order, it shows GroupBuy, and the second column shows how many orders there are for the client (or group order), The final results are sorted in ascending order according to the first column (source)

Problem solving ideas

• Replace null with an if statement
• Here is another function IFNULL(expression, alt_value): if the expression of the first parameter is NULL, the second parameter is returned

source code

with tmp as (    select *    from order_info    where user_id in (            select user_id            from order_info            where date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')            group by user_id            having count(*)>=2        ) and date>'2025-10-15' and status = 'completed' and product_name in ('C++','Java','Python')    order by id)select name_n,count(*)from (    select if(is_group_buy='Yes','GroupBuy',name) name_n    from tmp    left join client on tmp.client_id = client.id)tmp1group by name_norder by name_n;

Written examination question 84: analysis of resume delivery in internship square (I)

• Detailed description: please write out SQL statements to query the positions and quantity of resumes delivered in 2025, and sort them in descending order.

Problem solving ideas

• It mainly depends on how to judge 2025. There are many ways. You can use like statement or function date_format(date,"%Y")
• The expression can be passed in sum

source code

select    job,sum(if(year(date)=2025,num,0)) cntfrom resume_infogroup by joborder by cnt desc

Written examination question 85: analysis of resume delivery in internship square (II)

• Detailed description: please write a SQL statement to query the number of resumes received in each month for each position that submits resumes in 2025, and sort them in descending order by month first, and then by the number of resumes.

Problem solving ideas

• Find out what the number of resumes received per position per month means? We can know that this is grouped by position and month, and then aggregate the number of resumes

source code

select    job,date_format(date,"%Y-%m") month,sum(num) cntfrom resume_infowhere year(date) = 2025group by job,monthorder by month desc, cnt desc 

Written examination question 86: analysis of resume delivery in internship square (III)

• Detailed description: please write a SQL statement to query the number of resumes received in each month for each position that delivers a resume in 2025 and the number of resumes received in the same position in the same month in 2026. Press first for the final result_ year_ Mon months are displayed in descending order, and then sorted by job s in descending order.

Problem solving ideas

• According to the foreshadowing of the above two topics, we can easily get the number of resumes received by each position in each month in 2025, and it will be similar in 2026. So we can write two queries first, one is 2025 and the other is 2026. Finally, join them. What are the connection conditions? First of all, the posts should be the same, followed by the same month (the title says that the same month is the same post); Read the question carefully and it will be easy to solve

source code

select tmp1.job,       first_year_mon,       first_year_cnt,       second_year_mon,       second_year_cntfrom (         select job,                date_format(date, "%Y-%m") first_year_mon,                sum(num)                   first_year_cnt         from resume_info         where year(date) = 2025         group by job, first_year_mon     ) tmp1         join (    select job,           date_format(date, "%Y-%m") second_year_mon,           sum(num)                   second_year_cnt    from resume_info    where year(date) = 2026    group by job, second_year_mon) tmp2 on tmp1.job = tmp2.job and right(first_year_mon, 2) = right(second_year_mon, 2)order by first_year_mon desc, job desc

Written test question 87: what is the worst place (I)

• Please write out an SQL query. If a student knows his comprehensive score, what is the worst ranking? The results are sorted in ascending order of grade

Problem solving ideas

• The questions are required to be sorted in ascending order according to grade. In fact, they are sorted in ascending order according to the comprehensive scores, because the larger the comprehensive scores are in the later grade
• The score of the second line is equal to the first line plus the original second line. Just open the window

source code

select grade, sum(number) over(order by grade)from class_grade;

Written examination question 88: what is the worst place (2)

• Detailed description: the teacher wants to know what the median of students' comprehensive scores is. Please write SQL to help query. If there is only one median, output one. If there are two median, output it in ascending order of grade

Problem solving ideas

• This point is used in the problem solution: when the cumulative positive and negative order of a number is greater than half of the number of numbers in the whole sequence, it is the median
• If we know the above conclusion, we have positive order accumulation, and we can add desc to reverse order accumulation

source code

select gradefrom (         select grade,                #(select sum(number) from class_grade) as total,    			sum(number) over() total,                sum(number) over (order by grade)        a,                sum(number) over (order by grade desc)   b         from class_grade     ) tmpwhere a >= total / 2  and b >= total / 2order by grade

There is also something worth learning in the code. Select statements can be placed in select. Because aggregate functions cannot be placed with non aggregate fields, we rewrite a select to query the aggregate value

Written test question 89: who gets the most points (1)

• Detailed description: please write down the name of the user with the highest SQL search points and his total points (the data of this question ensure that there is only one user with the highest points)

Problem solving ideas

• To find the user with the most points added, you first need to accumulate all the points added by the user. How to accumulate? Grouping accumulation

source code

select name, cntfrom (         select user_id, sum(grade_num) cnt         from grade_info         group by user_id         order by cnt desc         limit 1     ) tmp         join user on tmp.user_id = user.id;

Written test question 90: who gets the most points (2)

• Detailed description: please write an SQL search score, add the id (there may be multiple) and name of the user with the highest score, and the total score. The query results are sorted in ascending order by id

Problem solving ideas

• When the intermediate table is used more than twice in the query, we can use the with intermediate table name as (select clause) to create a temporary table

source code

with tmp_table as (    select user_id, sum(grade_num) cnt    from grade_info    group by user_id    order by cnt desc)select user_id, name, cntfrom tmp_tablejoin user on user.id = tmp_table.user_idwhere cnt = (    select max(cnt) max_cnt    from tmp_table)

Written test question 91: who gets the most points (3)

• Please write down the id and name of the user with the highest score in SQL search, as well as the total score (there may be multiple), and the query results are sorted in ascending order by id

Problem solving ideas

• With the above idea, when the only problem to be solved is type = "reduce", the integral should not be added, but subtracted

source code

with tmp_table as (    select user_id, sum(grade_num) cnt    from (select user_id, if(type = "reduce", -grade_num, grade_num) grade_num from grade_info) tmp1    group by user_id)select id, name, cntfrom tmp_table         join user on tmp_table.user_id = user.idwhere cnt = (    select max(cnt)    from tmp_table)order by id