牛客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
更多相關文章
  • 第二回 巧習得元素分類 子不知懷璧其罪 雲溪父親見狀看了看雲溪,臉上滲出意思冷汗,但遲疑一下就立即退了出去,匆匆忙忙的往右邊廚房趕,只留下了雲溪和這位神秘的老爺子。 雲溪瞠目結舌的看著悠然自得的喝著老爹泡的茶的老爺子,下意識說了一句:“老先生你怎麼這麼快,還知道我要來這裡”。 “方向,你一直在繞巷子 ...
  • 一、Iproute2簡介 Iproute2是一個在Linux下的高級網路管理工具軟體。實際上,它是通過rtnetlink sockets方式動態配置內核的一些小工具組成的,從Linux2.2內核開始,Alexey Kuznetsov 實現了通過rtnetlink sockets用來配置網路協議棧,它 ...
  • 目錄 一、前景回顧 二、用C語言編寫內核 三、載入內核 四、運行測試 一、前景回顧 本回開始,我們要開始編寫內核代碼了,在此之前,先梳理一下已經完成的工作。 藍色部分是目前已經完成的部分,黃色部分是本節將要實現的。 二、用C語言編寫內核 為什麼要用C語言來編寫內核呢,其實用彙編語言也可以實現,只是對 ...
  • 鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 VS中有一鍵編譯+自動運行,Linux也給我們提供了對應的編譯方式,雖然不及VS那麼便捷,但是相比於手動輸入 gcc -o add add.c ,我們一句make就可以搞定 Makefile是一個文件,能夠存放上述 gcc -o add add. ...
  • 鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 1. 伺服器要求: 建議最小硬體配置:2核CPU、2G記憶體、20G硬碟 伺服器最好可以訪問外網,會有從網上拉取鏡像需求,如果伺服器不能上網,需要提前下載對應鏡像並導入節點 1.1 軟體環境: 1.2 伺服器規劃: 1.3 架構圖: 2. 操作系統 ...
  • ClickHouse核心架構設計是怎麼樣的?ClickHouse核心架構模塊分為兩個部分:ClickHouse執行過程架構和ClickHouse數據存儲架構,下麵分別詳細介紹。 ClickHouse執行過程架構 總的來說,結合目前搜集到的一些資料,可以看到目前ClickHouse核心架構由下圖構成, ...
  • 原文鏈接:基於開源大數據調度系統Taier的Web前端架構選型及技術實踐 課件獲取:關註公眾號**“數棧研習社”,後臺私信“Taier”**獲得直播課件 視頻回放:點擊這裡 Taier開源項目地址:github丨gitee 上兩期,我們為大家分享了Taier入門及控制台的介紹,本期我們為大家分享Ta ...
  • **導讀:**在公司內部,業務線經常面臨數據有哪些、質量如何、是否可用、能產生多大價值的困惑,並且,隨著數據量的增加,計算和存儲資源面臨瓶頸。本次將圍繞數據治理重點關註的計算、存儲等方面,分享數據治理的產品實踐。通過分享,一方面可以瞭解當前業務線主要面臨的待治理的數據問題;另一方面,從計算、存儲等主 ...
一周排行
    -Advertisement-
    Play Games
  • Timer是什麼 Timer 是一種用於創建定期粒度行為的機制。 與標準的 .NET System.Threading.Timer 類相似,Orleans 的 Timer 允許在一段時間後執行特定的操作,或者在特定的時間間隔內重覆執行操作。 它在分散式系統中具有重要作用,特別是在處理需要周期性執行的 ...
  • 前言 相信很多做WPF開發的小伙伴都遇到過表格類的需求,雖然現有的Grid控制項也能實現,但是使用起來的體驗感並不好,比如要實現一個Excel中的表格效果,估計你能想到的第一個方法就是套Border控制項,用這種方法你需要控制每個Border的邊框,並且在一堆Bordr中找到Grid.Row,Grid. ...
  • .NET C#程式啟動閃退,目錄導致的問題 這是第2次踩這個坑了,很小的編程細節,容易忽略,所以寫個博客,分享給大家。 1.第一次坑:是windows 系統把程式運行成服務,找不到配置文件,原因是以服務運行它的工作目錄是在C:\Windows\System32 2.本次坑:WPF桌面程式通過註冊表設 ...
  • 在分散式系統中,數據的持久化是至關重要的一環。 Orleans 7 引入了強大的持久化功能,使得在分散式環境下管理數據變得更加輕鬆和可靠。 本文將介紹什麼是 Orleans 7 的持久化,如何設置它以及相應的代碼示例。 什麼是 Orleans 7 的持久化? Orleans 7 的持久化是指將 Or ...
  • 前言 .NET Feature Management 是一個用於管理應用程式功能的庫,它可以幫助開發人員在應用程式中輕鬆地添加、移除和管理功能。使用 Feature Management,開發人員可以根據不同用戶、環境或其他條件來動態地控制應用程式中的功能。這使得開發人員可以更靈活地管理應用程式的功 ...
  • 在 WPF 應用程式中,拖放操作是實現用戶交互的重要組成部分。通過拖放操作,用戶可以輕鬆地將數據從一個位置移動到另一個位置,或者將控制項從一個容器移動到另一個容器。然而,WPF 中預設的拖放操作可能並不是那麼好用。為瞭解決這個問題,我們可以自定義一個 Panel 來實現更簡單的拖拽操作。 自定義 Pa ...
  • 在實際使用中,由於涉及到不同編程語言之間互相調用,導致C++ 中的OpenCV與C#中的OpenCvSharp 圖像數據在不同編程語言之間難以有效傳遞。在本文中我們將結合OpenCvSharp源碼實現原理,探究兩種數據之間的通信方式。 ...
  • 一、前言 這是一篇搭建許可權管理系統的系列文章。 隨著網路的發展,信息安全對應任何企業來說都越發的重要,而本系列文章將和大家一起一步一步搭建一個全新的許可權管理系統。 說明:由於搭建一個全新的項目過於繁瑣,所有作者將挑選核心代碼和核心思路進行分享。 二、技術選擇 三、開始設計 1、自主搭建vue前端和. ...
  • Csharper中的表達式樹 這節課來瞭解一下表示式樹是什麼? 在C#中,表達式樹是一種數據結構,它可以表示一些代碼塊,如Lambda表達式或查詢表達式。表達式樹使你能夠查看和操作數據,就像你可以查看和操作代碼一樣。它們通常用於創建動態查詢和解析表達式。 一、認識表達式樹 為什麼要這樣說?它和委托有 ...
  • 在使用Django等框架來操作MySQL時,實際上底層還是通過Python來操作的,首先需要安裝一個驅動程式,在Python3中,驅動程式有多種選擇,比如有pymysql以及mysqlclient等。使用pip命令安裝mysqlclient失敗應如何解決? 安裝的python版本說明 機器同時安裝了 ...