牛客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
  • 用例演示 - 創建實體 本節將演示一些示例用例並討論可選場景。 創建實體 從實體/聚合根類創建對象是實體生命周期的第一步。聚合/聚合根規則和最佳實踐部分 建議為Entity類創建一個主構造函數,以保證創建一個有效的實體。因此,無論何時我們需要創建實體的實例,我們都應該使用那個構造函數 參見下麵的問題 ...
  • 領域邏輯 & 應用邏輯 如前所述,領域驅動設計中的業務邏輯分為兩部分(層):領域邏輯和應用邏輯: 領域邏輯由系統的核心領域規則組成,應用邏輯實現應用特定的用例 雖然定義很明確,但實現起來可能並不容易。您可能無法決定哪些代碼應該位於應用程式層,哪些代碼應該位於領域層。本節試圖解釋其中的差異 多個應用程 ...
  • 表弟大學快畢業了,學了一個學期Python居然還不會寫學生管理系統,真的給我丟臉啊,教他又不肯學,還讓我直接給他寫,我真想兩巴掌上去,最終還是寫了給他,誰讓他是我表弟呢,關鍵時候還是得幫他一把! 寫完了放在那也是放著,所以今天分享給大家吧! 話不多說,咱們直接開始吧! 代碼解析 一、登錄頁面 1、定 ...
  • Zookeeper3.7源碼剖析 能力目標 掌握Zookeeper中Session的管理機制 能基於Client進行Debug測試Session創建/刷新操作 能搭建Zookeeper集群源碼配置 掌握集群環境下Leader選舉啟動過程 能說出Zookeeper選舉過程中的概念 能說出Zookeep ...
  • 前言 今天給大家分享一下我自己寫的筆記,純純的都是乾貨,關於字好像也能看。這是我學python整理出來的一些資料,希望對大家 有用。想要更多的資料那就的給一個關註了… python學習交流Q群:903971231### #導入Counter from collections import Count ...
  • Hi,大家好,我是Mic 一個工作5年的粉絲找到我。 他說: “Mic老師,你要是能回答出這個問題,我就佩服你” 我當場就懵了,現在打賭都這麼隨意了嗎? 我問他問題是什麼,他說“Kafka如何避免重覆消費的問題!” 下麵看看普通人和高手的回答! 普通人: Kafka怎麼避免重覆消費就是我們可以通過 ...
  • 前言 Steam是由美國電子游戲商Valve於2003年9月12日推出的數字發行平臺,被認為是電腦游戲界最大的數位發行平臺之一,Steam平臺是全球最大的綜合性數字發行平臺之一。玩家可以在該平臺購買、下載、討論、上傳和分享游戲和軟體。 而每周的steam會開啟了一輪特惠,可以讓游戲打折,而玩家就會 ...
  • 本篇內容將在上一篇已有的內容基礎上,進一步的聊一下項目中使用JPA的一些高階複雜場景的實踐指導,覆蓋了主要核心的JPA使用場景,可以讓你在需求開發的時候對JPA的使用更加的游刃有餘。 ...
  • 1.路徑處理 1.找模塊:sys.path import sys print(sys.path) - 1.理解 - 1.是python去查找包或模塊 - 2.項目開始根目錄,python內置的目錄 - 3.雖然說python的安裝目錄下也可以存放我們寫的模塊,但是不建議(太多了,不大好找) - 4. ...
  • Go 語言入門練手項目系列 01 基於命令行的圖書的增刪查改 02 文件管理 持續更新中... > 本文來自博客園,作者:Arway,轉載請註明原文鏈接:https://www.cnblogs.com/cenjw/p/gobeginner-proj-bookstore-cli.html 介紹 這是一 ...