牛客SQL刷題第一趴——非技術入門基礎篇

来源:https://www.cnblogs.com/ruoli-121288/archive/2022/06/18/16378075.html
-Advertisement-
Play Games

user_profile表： id device_id gender age university province 1 2138 male 21 北京大學 Beijing 2 3214 male 復旦大學 Shanghai 3 6543 female 20 北京大學 Beijing 4 2315 ...


user_profile表：

id device_id gender age university province
1 2138 male 21 北京大學 Beijing
2 3214 male   復旦大學 Shanghai
3 6543 female 20 北京大學 Beijing
4 2315 female 23 浙江大學 ZheJiang
5 5432 male 25 山東大學 Shandong

question_pratice_detail表：

id device_id question_id result date
1 2138 111 wrong 2021-05-03
2 3214 112 wrong 2021-05-09
3 3214 113 wrong 2021-06-15
4 6543 111 right 2021-08-13
5 2315 115 right 2021-08-13
6 2315 116 right 2021-08-14
7 2315 117 wrong 2021-08-15

question_detail表

question_id difficult_level
111 hard
112 medium
113 easy
115 easy
116 medium
117 easy

一、基礎查詢

SQL1 查詢所有列

題目：現在運營想要查看用戶信息表中所有的數據，請你取出相應結果

SELECT * FROM user_profile;

SQL2 查詢多列

題目：現在運營同學想要用戶的設備id對應的性別、年齡和學校的數據，請你取出相應數據

SELECT gender,COUNT(*)
FROM user_profile
GROUP BY gender;

SQL3 查詢結果去重

題目：現在運營需要查看用戶來自於哪些學校，請從用戶信息表中取出學校的去重數據。

SELECT DISTINCT university
FROM user_profile;

SQL4 查詢結果限制返回行數

現在運營只需要查看前2個用戶明細設備ID數據，請你從用戶信息表 user_profile 中取出相應結果。

SELECT device_id FROM user_profile
LIMIT 2;

SQL5 將查詢後的列重新命名

題目：現在你需要查看前2個用戶明細設備ID數據，並將列名改為 'user_infos_example',，請你從用戶信息表取出相應結果。

SELECT device_id AS user_infors_example
FROM user_profile
LIMIT 2;

二、條件查詢

SQL6 查找學校是北大的學生信息

題目：現在運營想要篩選出所有北京大學的學生進行用戶調研，請你從用戶信息表中取出滿足條件的數據，結果返回設備id和學校。

SELECT device_id,university
FROM user_profile
HAVING university='北京大學';

SQL7 查找年齡大於24歲的用戶信息

題目：現在運營想要針對24歲以上的用戶開展分析，請你取出滿足條件的設備ID、性別、年齡、學校。

SELECT device_id,gender,age,university
FROM user_profile
WHERE age>24;

SQL8 查找某個年齡段的用戶信息

題目：現在運營想要針對20歲及以上且23歲及以下的用戶開展分析，請你取出滿足條件的設備ID、性別、年齡。

SELECT device_id,gender,age
FROM user_profile
WHERE age>=20 AND age<=23

SQL9 查找除復旦大學的用戶信息

題目：現在運營想要查看除復旦大學以外的所有用戶明細，請你取出相應數據

SELECT device_id,gender,age,university
FROM user_profile
WHERE university !='復旦大學'

SQL10 用where過濾空值練習

題目：現在運營想要對用戶的年齡分佈開展分析，在分析時想要剔除沒有獲取到年齡的用戶，請你取出所有年齡值不為空的用戶的設備ID，性別，年齡，學校的信息。

SELECT device_id,gender,age,university
FROM user_profile
WHERE age is NOT NULL;

SQL11 高級操作符練習(1)

題目：現在運營想要找到男性且GPA在3.5以上(不包括3.5)的用戶進行調研，請你取出相關數據。

SELECT device_id,gender,age,university,gpa
FROM user_profile
WHERE gender='male' AND gpa>3.5;

SQL12 高級操作符練習(2）

題目：現在運營想要找到學校為北大或GPA在3.7以上(不包括3.7)的用戶進行調研，請你取出相關數據（使用OR實現）

SELECT device_id,gender,age,university,gpa
FROM user_profile
WHERE university='北京大學' OR gpa>3.7

 SQL13 Where in 和Not in

題目：現在運營想要找到學校為北大、復旦和山大的同學進行調研，請你取出相關數據。

SELECT device_id,gender,age,university,gpa
FROM user_profile
WHERE university IN ('北京大學','復旦大學','山東大學')

SQL14 操作符混合運用

題目：現在運營想要找到gpa在3.5以上(不包括3.5)的山東大學用戶 或 gpa在3.8以上(不包括3.8)的復旦大學同學進行用戶調研，請你取出相應數據

SELECT device_id,gender,age,university,gpa
FROM user_profile
WHERE (gpa>3.5 AND university='山東大學') OR (gpa>3.8 AND university='復旦大學')

SQL15 查看學校名稱中含北京的用戶

題目：現在運營想查看所有大學中帶有北京的用戶的信息，請你取出相應數據。

SELECT device_id,age,university
FROM user_profile
WHERE university LIKE '%北京%';

SQL36 查找後排序

題目：現在運營想要取出用戶信息表中的用戶年齡，請取出相應數據，並按照年齡升序排序。   
select device_id,age
from user_profile
order by age;

SQL37 查找後多列排序

題目：現在運營想要取出用戶信息表中的年齡和gpa數據，並先按照gpa升序排序，再按照年齡升序排序輸出，請取出相應數據。

select device_id,gpa,age
from user_profile
order by gpa,age;

SQL38 查找後降序排列

題目：現在運營想要取出用戶信息表中對應的數據，並先按照gpa、年齡降序排序輸出，請取出相應數據。

select device_id,gpa,age
from user_profile
order by gpa desc,age desc;

 

三、高級查詢

SQL16 查找GPA最高值

題目：運營想要知道復旦大學學生gpa最高值是多少，請你取出相應數據

SELECT MAX(gpa)
FROM user_profile
WHERE university='復旦大學';

SQL17 計算男生人數以及平均GPA

題目：現在運營想要看一下男性用戶有多少人以及他們的平均gpa是多少，用以輔助設計相關活動，請你取出相應數據。

SELECT COUNT(gender) AS male_num,AVG(gpa) AS avg_gpa
FROM user_profile
WHERE gender='male';

SQL18 分組計算練習題

題目：現在運營想要對每個學校不同性別的用戶活躍情況和發帖數量進行分析，請分別計算出每個學校每種性別的用戶數、30天內平均活躍天數和平均發帖數量。 用戶信息表：user_profile 30天內活躍天數欄位（active_days_within_30） 發帖數量欄位（question_cnt） 回答數量欄位（answer_cnt） 
SELECT gender,university,
COUNT(device_id) AS user_num,
AVG(active_days_within_30) AS avg_active_day,
AVG(question_cnt) AS avg_question_cnt
FROM user_profile
GROUP BY gender,university;

SQL19 分組過濾練習題

題目：現在運營想查看每個學校用戶的平均發貼和回帖情況，尋找低活躍度學校進行重點運營，請取出平均發貼數低於5的學校或平均回帖數小於20的學校。

SELECT university,AVG(question_cnt) AS avg_question_cnt,AVG(answer_cnt) AS avg_answer_cnt
FROM user_profile
GROUP BY university
HAVING avg_question_cnt<5 OR avg_answer_cnt<20;

SQL20 分組排序練習題

題目：現在運營想要查看不同大學的用戶平均發帖情況，並期望結果按照平均發帖情況進行升序排列，請你取出相應數據。

SELECT university,AVG(question_cnt) AS avg_question_cnt
FROM user_profile
GROUP BY university
ORDER BY avg_question_cnt;

四、多表查詢

SQL21 浙江大學用戶題目回答情況

select device_id,question_id,result
from question_practice_detail
where device_id=(select device_id from user_profile where university='浙江大學');

SQL22 統計每個學校的答過題的用戶的平均答題數

運營想要瞭解每個學校答過題的用戶平均答題數量情況，請你取出數據。 請你寫SQL查找每個學校用戶的平均答題數目(說明：某學校用戶平均答題數量計算方式為該學校用戶答題總次數除以答過題的不同用戶個數)根據示例，你的查詢應返回以下結果（結果保留4位小數），註意：結果按照university升序排序！！！ 用戶信息表 user_profile，其中device_id指終端編號（認為每個用戶有唯一的一個終端），gender指性別，age指年齡，university指用戶所在的學校，gpa是該用戶平均學分績點，active_days_within_30是30天內的活躍天數。 
select u.university,ROUND(count(q.question_id)/count(DISTINCT(q.device_id)),4)
from user_profile u
join question_practice_detail q
on u.device_id=q.device_id
group by 1
order by 1;

 SQL23 統計每個學校各難度的用戶平均刷題數

題目：運營想要計算一些參加了答題的不同學校、不同難度的用戶平均答題量，請你寫SQL取出相應數據

請你寫一個SQL查詢，計算不同學校、不同難度的用戶平均答題量，根據示例，你的查詢應返回以下結果(結果在小數點位數保留4位，4位之後四捨五入)：

select u.university,
q2.difficult_level,
ROUND(count(u.question_cnt)/count(distinct(u.device_id)),4) as avg_answer_cnt
from user_profile u
join question_practice_detail q1
on u.device_id=q1.device_id
join question_detail q2
on q1.question_id=q2.question_id
group by 1,2;

ps：用戶平均答題量 = 答題總數 / 用戶數，要明確答題總數需要用question_id的行數來表示，也就是需對question_id列進行計數，用戶數是device_id的行數，其中答題的題目可以重覆，用戶則不能重覆計數 

SQL24 統計每個用戶的平均刷題數

題目：運營想要查看參加了答題的山東大學的用戶在不同難度下的平均答題題目數，請取出相應數據

請你寫一個SQL查詢，計算山東、不同難度的用戶平均答題量，根據示例，你的查詢應返回以下結果(結果在小數點位數保留4位，4位之後四捨五入)：

select u.university,
q2.difficult_level,
ROUND(count(u.question_cnt)/count(DISTINCT(u.device_id)),4) as avg_answer_cnt
from user_profile u
join question_practice_detail q1
on u.device_id=q1.device_id
join question_detail q2
on q1.question_id=q2.question_id
where u.university='山東大學'
group by 1,2;

解題思路：1⃣️多表連接

2⃣️用戶平均答題量 = 答題總數 / 用戶數，要明確答題總數需要用question_id的行數來表示，也就是需對question_id列進行計數，用戶數是device_id的行數，其中答題的題目可以重覆，用戶則不能重覆計數

3⃣️where子句篩選university為山東大學。

4⃣️group by 按照大學分組。

 SQL25 查找山東大學或者性別為男生的信息

題目：現在運營想要分別查看學校為山東大學或者性別為男性的用戶的device_id、gender、age和gpa數據，請取出相應結果，結果不去重。

select device_id,gender,age,gpa
from user_profile
where university='山東大學'
union all
select device_id,gender,age,gpa
from user_profile
where gender='male';

五、必會的常用函數

SQL26 計算25歲以上和以下的用戶數量

題目：現在運營想要將用戶劃分為25歲以下和25歲及以上兩個年齡段，分別查看這兩個年齡段用戶數量 本題註意：age為null 也記為 25歲以下   
select (case when age>=25 then '25歲及以上' else '25歲以下' end) as age_cut,
count(DISTINCT(device_id)) as number
from user_profile
group by 1;

解題思路：1⃣️case when，或者if函數都可以。

2⃣️有聚合函數要用group by。

SQL27 查看不同年齡段的用戶明細

題目：現在運營想要將用戶劃分為20歲以下，20-24歲，25歲及以上三個年齡段，分別查看不同年齡段用戶的明細情況，請取出相應數據。（註：若年齡為空請返回其他。）

select device_id,
gender,
(case when age<20 then '20歲以下'
when age>=20 and age<=24 then '20-24歲'
when age>=25 then '25歲及以上'
else '其他' end) as age_cut
from user_profile;

SQL28 計算用戶8月每天的練題數量

題目：現在運營想要計算出2021年8月每天用戶練習題目的數量，請取出相應數據。

select DAY(date),
count(question_id) as question_cnt
from question_practice_detail
where date>='2021-08-01' and date<='2021-8-31'
group by 1;

 SQL29 計算用戶的平均次日留存率

題目：現在運營想要查看用戶在某天刷題後第二天還會再來刷題的平均概率。請你取出相應數據。

select avg(if(b.device_id is not null,1,0)) as avg_ret
from(select distinct device_id,date
    from question_practice_detail)a
    left join
    (select distinct device_id,date_sub(date,interval 1 day) as date
    from question_practice_detail)b
    on a.device_id=b.device_id and a.date=b.date;

 

 

SQL30 統計每種性別的人數

題目：現在運營舉辦了一場比賽，收到了一些參賽申請，表數據記錄形式如下所示，現在運營想要統計每個性別的用戶分別有多少參賽者，請取出相應結果

select substring(profile,15,6) as gender,count(device_id) as number
from user_submit
group by 1;

SQL31 提取博客URL中的用戶名

題目：對於申請參與比賽的用戶，blog_url欄位中url字元後的字元串為用戶個人博客的用戶名，現在運營想要把用戶的個人博客用戶欄位提取出單獨記錄為一個新的欄位，請取出所需數據。

select device_id,
substring(blog_url,11,11) as user_name
from user_submit;

 

SQL32 截取出年齡

題目：現在運營舉辦了一場比賽，收到了一些參賽申請，表數據記錄形式如下所示，現在運營想要統計每個年齡的用戶分別有多少參賽者，請取出相應結果

select substring(profile,12,2) as age,
count(device_id) as number
from user_submit
group by 1;

 SQL33 找出每個學校GPA最低的同學

題目：現在運營想要找到每個學校gpa最低的同學來做調研，請你取出每個學校的最低gpa。

根據示例，你的查詢結果應參考以下格式，輸出結果按university升序排序：

-- 方法1:join
select u.device_id,u.university,u.gpa
from user_profile u
join (select university,min(gpa) as gpa from user_profile group by university) u1
on u.university=u1.university and u.gpa=u1.gpa
order by university;

-- 方法2:select子查詢
select device_id,university,gpa
from user_profile
where (university,gpa) in(select university,min(gpa) from user_profile
                       group by university)
order by university;

解題思路：還可以使用視窗函數。

 六、綜合練習

SQL34 統計復旦用戶8月練題情況

題目： 現在運營想要瞭解復旦大學的每個用戶在8月份練習的總題目數和回答正確的題目數情況，請取出相應明細數據，對於在8月份沒有練習過的用戶，答題數結果返回0.

select u.device_id,u.university,
sum(if(question_id is not NUll,1,0)) as question_cnt,
sum(if(result='right',1,0)) as right_question_cnt
from user_profile u
left join question_practice_detail q
on u.device_id=q.device_id and month(date)=8
where u.university='復旦大學'
group by 1,2;

解題思路：1⃣️多表連接，使用left join

2⃣️關於日期函數寫在where中會報錯。

關於month(date)為什麼不寫在where後面：首先month函數不是聚合函數，是可以寫在where語句中的；其次是，如果寫在where中，是對連接好的表進行判斷，如果是用user表leftjoin question表，由於question表裡沒有4321用戶的記錄，那麼連接好的表中的id為4321的用戶是沒有date值的，也就是date為空，所以在執行where month(date)=8的時候會除掉4321這行記錄，所以最後的結果里就沒有這個id的記錄啦！主要是要理解：1.先執行from，再執行where，where中的操作是對連接好的表的操作；2.a左連接b，對於a有而b沒有的id，則連接好的表中的這些id的b相關的屬性值為空。

SQL35 浙大不同難度題目的正確率

題目：現在運營想要瞭解浙江大學的用戶在不同難度題目下答題的正確率情況，請取出相應數據，並按照准確率升序輸出。

select q.difficult_level,
sum(if(q1.result='right',1,0))/count(q1.question_id) as correct_rate
from question_detail q
left join question_practice_detail q1
on q.question_id=q1.question_id
left join user_profile u
on q1.device_id=u.device_id
where u.university='浙江大學'
group by 1
order by 2;

解題思路：1⃣️多表連接

2⃣️正確率的計算公式：回答正確的個數/回答的總題目

SQL39 21年8月份練題總數

題目： 現在運營想要瞭解2021年8月份所有練習過題目的總用戶數和練習過題目的總次數，請取出相應結果

select count(DISTINCT(device_id)) as did_cnt,count(question_id) as question_cnt
from question_practice_detail
where date>='2021-08-01 00:00:00' and date<='2021-08-31 23:59:59';

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 《原CSharp》第二回 巧習得元素分類 子不知懷璧其罪
    第二回 巧習得元素分類 子不知懷璧其罪 雲溪父親見狀看了看雲溪，臉上滲出意思冷汗，但遲疑一下就立即退了出去，匆匆忙忙的往右邊廚房趕，只留下了雲溪和這位神秘的老爺子。 雲溪瞠目結舌的看著悠然自得的喝著老爹泡的茶的老爺子，下意識說了一句：“老先生你怎麼這麼快，還知道我要來這裡”。 “方向，你一直在繞巷子 ...
  • iproute2路由配置（ip rule、ip route、traceroute）
    一、Iproute2簡介 Iproute2是一個在Linux下的高級網路管理工具軟體。實際上，它是通過rtnetlink sockets方式動態配置內核的一些小工具組成的，從Linux2.2內核開始，Alexey Kuznetsov 實現了通過rtnetlink sockets用來配置網路協議棧，它 ...
  • [自製操作系統] 第09回 載入內核
    目錄 一、前景回顧 二、用C語言編寫內核 三、載入內核 四、運行測試 一、前景回顧 本回開始，我們要開始編寫內核代碼了，在此之前，先梳理一下已經完成的工作。 藍色部分是目前已經完成的部分，黃色部分是本節將要實現的。 二、用C語言編寫內核 為什麼要用C語言來編寫內核呢，其實用彙編語言也可以實現，只是對 ...
  • Linux 自動化構建工具 make/Makefile
    鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 VS中有一鍵編譯+自動運行，Linux也給我們提供了對應的編譯方式，雖然不及VS那麼便捷，但是相比於手動輸入 gcc -o add add.c ，我們一句make就可以搞定 Makefile是一個文件，能夠存放上述 gcc -o add add. ...
  • k8s單master集群部署
    鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 1. 伺服器要求： 建議最小硬體配置：2核CPU、2G記憶體、20G硬碟 伺服器最好可以訪問外網，會有從網上拉取鏡像需求，如果伺服器不能上網，需要提前下載對應鏡像並導入節點 1.1 軟體環境： 1.2 伺服器規劃： 1.3 架構圖： 2. 操作系統 ...
  • ClickHouse(02)ClickHouse架構設計介紹概述與ClickHouse數據分片設計
    ClickHouse核心架構設計是怎麼樣的？ClickHouse核心架構模塊分為兩個部分：ClickHouse執行過程架構和ClickHouse數據存儲架構，下麵分別詳細介紹。 ClickHouse執行過程架構 總的來說，結合目前搜集到的一些資料，可以看到目前ClickHouse核心架構由下圖構成， ...
  • 基於開源大數據調度系統Taier的Web前端架構選型及技術實踐
    原文鏈接：基於開源大數據調度系統Taier的Web前端架構選型及技術實踐 課件獲取：關註公眾號**“數棧研習社”，後臺私信“Taier”**獲得直播課件 視頻回放：點擊這裡 Taier開源項目地址：github丨gitee 上兩期，我們為大家分享了Taier入門及控制台的介紹，本期我們為大家分享Ta ...
  • 雲娜：從計算、存儲角度，談網易數據治理工具產品實踐
    **導讀：**在公司內部，業務線經常面臨數據有哪些、質量如何、是否可用、能產生多大價值的困惑，並且，隨著數據量的增加，計算和存儲資源面臨瓶頸。本次將圍繞數據治理重點關註的計算、存儲等方面，分享數據治理的產品實踐。通過分享，一方面可以瞭解當前業務線主要面臨的待治理的數據問題；另一方面，從計算、存儲等主 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類