牛客网SQL大厂面试真题（二）

SQL25 某宝店铺的SPU数量

SELECT style_id, COUNT(1) AS SPU_num
FROM product_tb
GROUP BY style_id
ORDER BY SPU_num DESC

SQL26 某宝店铺的实际销售额与客单价

SELECT SUM(total_sales_price) AS sales_total, ROUND(AVG(total_sales_price), 2) AS per_trans
FROM (
    SELECT user_id, SUM(sales_price) AS total_sales_price
    FROM sales_tb
    GROUP BY user_id
) tmp

SQL27 某宝店铺折扣率

SELECT ROUND(100 * SUM(sales_price) / SUM(tag_price * sales_num), 2) AS `discount_rate(%)`
FROM product_tb AS product JOIN sales_tb AS sales ON product.item_id = sales.item_id

SQL28 某宝店铺动销率与售罄率

SELECT style_id, 
       ROUND(100 * SUM(total_sales_num) / (SUM(inventory) - SUM(total_sales_num)), 2) AS `pin_rate(%)`,
       ROUND(100 * SUM(total_sales_price) / SUM(tag_price * inventory), 2) AS `	sell-through_rate(%)`
FROM product_tb AS product JOIN (
    SELECT item_id, SUM(sales_num) AS total_sales_num, SUM(sales_price) AS total_sales_price
    FROM sales_tb
    GROUP BY item_id
) sales ON product.item_id = sales.item_id
GROUP BY style_id
ORDER BY style_id

SQL29 某宝店铺连续2天及以上购物的用户及其对应的天数

SELECT user_id, MAX(CAST(ranking AS UNSIGNED)) AS days_count
FROM (
    SELECT user_id,
           @rk := IF(user_id = @user_id AND DATEDIFF(sales_date, @sales_date) = 1, @rk + 1, 1) AS ranking,
           @user_id := user_id,
           @sales_date := sales_date
    FROM (
        SELECT user_id, sales_date
        FROM sales_tb
        ORDER BY user_id, sales_date
    ) sales, (
        SELECT @user_id := NULL, @sales_date := NULL, @rk := 0
    ) tmp1
) tmp2
GROUP BY user_id
HAVING MAX(ranking) >= 2
ORDER BY user_id

SQL30 牛客直播转换率

SELECT course_tb.course_id, course_name, ROUND(100 * SUM(if_sign) / SUM(if_vw), 2) AS `sign_rate(%)`
FROM course_tb JOIN behavior_tb ON course_tb.course_id = behavior_tb.course_id
GROUP BY course_tb.course_id, course_name
ORDER BY course_tb.course_id

SQL31 牛客直播开始时各直播间在线人数

SELECT course_tb.course_id, course_name, COUNT(1) AS online_num
FROM course_tb JOIN attend_tb ON course_tb.course_id = attend_tb.course_id
WHERE TIME(in_datetime) <= '19:00:00' AND TIME(out_datetime) >= '19:00:00'
GROUP BY course_tb.course_id, course_name
ORDER BY course_tb.course_id

SQL32 牛客直播各科目平均观看时长

SELECT course_name, ROUND(AVG(TIMESTAMPDIFF(SECOND, in_datetime, out_datetime)) / 60, 2) AS avg_Len
FROM course_tb JOIN attend_tb ON course_tb.course_id = attend_tb.course_id
GROUP BY course_name
ORDER BY avg_Len DESC

SQL33 牛客直播各科目出勤率

SELECT course_tb.course_id, course_name, ROUND(100 * SUM(IF(attend_time >= 10 * 60, 1, 0)) / SUM(if_sign), 2) AS `attend_rate(%)`
FROM course_tb JOIN behavior_tb ON course_tb.course_id = behavior_tb.course_id
     LEFT JOIN (
         SELECT course_id, user_id, MAX(TIMESTAMPDIFF(SECOND, in_datetime, out_datetime)) AS attend_time
         FROM attend_tb
         GROUP BY course_id, user_id
     ) attend ON attend.course_id = behavior_tb.course_id AND attend.user_id = behavior_tb.user_id
GROUP BY course_tb.course_id, course_name
ORDER BY course_tb.course_id

SQL34 牛客直播各科目同时在线人数

SELECT course_tb.course_id, course_name, MAX(CAST(num AS SIGNED)) AS max_num
FROM (
    SELECT course_id, SUM(flag) OVER (PARTITION BY course_id ORDER BY dt ASC, flag DESC) AS num
    FROM (
        SELECT course_id, in_datetime AS dt, '1' AS flag
        FROM attend_tb
        UNION ALL
        SELECT course_id, out_datetime AS dt, '-1' AS flag
        FROM attend_tb
    ) lst
) tmp JOIN course_tb ON tmp.course_id = course_tb.course_id
GROUP BY course_tb.course_id, course_name
ORDER BY course_tb.course_id

SQL35 某乎问答11月份日人均回答量

SELECT answer_date, ROUND(COUNT(1) / COUNT(DISTINCT author_id), 2) AS per_num
FROM answer_tb
GROUP BY answer_date
ORDER BY answer_date

SQL36 某乎问答高质量的回答中用户属于各级别的数量

SELECT CASE WHEN author_level IN ('5', '6') THEN '5-6级'
            WHEN author_level IN ('3', '4') THEN '3-4级'
            ELSE '1-2级'
       END AS level_cut,
       COUNT(1) AS num
FROM author_tb JOIN answer_tb ON author_tb.author_id = answer_tb.author_id
WHERE char_len >= 100
GROUP BY level_cut
ORDER BY num DESC

SQL37 某乎问答单日回答问题数大于等于3个的所有用户

SELECT answer_date, author_id, COUNT(1) AS answer_cnt
FROM answer_tb
WHERE MONTH(answer_date) = '11'
GROUP BY answer_date, author_id
HAVING answer_cnt >= 3
ORDER BY answer_date, author_id

SQL38 某乎问答回答过教育类问题的用户里有多少用户回答过职场类问题

SELECT COUNT(1) AS num
FROM (
    SELECT DISTINCT author_id
    FROM issue_tb JOIN answer_tb ON issue_tb.issue_id = answer_tb.issue_id
    WHERE issue_type = 'Education'
) edu JOIN (
    SELECT DISTINCT author_id
    FROM issue_tb JOIN answer_tb ON issue_tb.issue_id = answer_tb.issue_id
    WHERE issue_type = 'Career'
) career ON edu.author_id = career.author_id

SQL39 某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级

SELECT stat.author_id, author_level, days_cnt
FROM (
    SELECT author_id, MAX(CAST(rk AS UNSIGNED)) AS days_cnt
    FROM (
        SELECT author_id,
               @rk := IF(author_id = @author_id AND DATEDIFF(answer_date, @answer_date) = 1, @rk + 1, 1) AS `rk`,
               @author_id := author_id,
               @answer_date := answer_date
        FROM (
            SELECT author_id, answer_date
            FROM answer_tb
            GROUP BY author_id, answer_date
            ORDER BY author_id, answer_date
        ) answer, (
            SELECT @author_id := NULL, @answer_date := NULL, @rk := 0
        ) tmp1
    ) tmp2
    GROUP BY author_id
    HAVING days_cnt >= 3
) stat JOIN author_tb ON stat.author_id = author_tb.author_id
ORDER BY author_id