【牛客刷題-SQL大廠面試真題】NO5.某寶店鋪分析(電商模式)
theme: awesome-green
攜手創作,共同成長!這是我參與「掘金日新計劃 · 8 月更文挑戰」的第8天,點選檢視活動詳情
📢📢📢📣📣📣\ 哈嘍!大家好,我是【IT邦德】,江湖人稱jeames007,10年DBA工作經驗\ 中國DBA聯盟(ACDU)成員,目前從事DBA及程式程式設計😜😜😜\ 擅長Oracle、MySQL、PG 運維開發,備份恢復,安裝遷移,效能優化、故障應急處理。\ ❤️❤️❤️感謝各位大可愛小可愛!❤️❤️❤️
前言
SQL每個人都要用,但是用來衡量產出的並不是SQL本身,你需要用這個工具,去創造其它的價值。
## 🐴 SQL180 某寶店鋪的SPU數量
🚀 建表語句
sql
drop table if exists product_tb;
CREATE TABLE product_tb(
item_id char(10) NOT NULL,
style_id char(10) NOT NULL,
tag_price int(10) NOT NULL,
inventory int(10) NOT NULL
);
INSERT INTO product_tb VALUES('A001', 'A', 100, 20);
INSERT INTO product_tb VALUES('A002', 'A', 120, 30);
INSERT INTO product_tb VALUES('A003', 'A', 200, 15);
INSERT INTO product_tb VALUES('B001', 'B', 130, 18);
INSERT INTO product_tb VALUES('B002', 'B', 150, 22);
INSERT INTO product_tb VALUES('B003', 'B', 125, 10);
INSERT INTO product_tb VALUES('B004', 'B', 155, 12);
INSERT INTO product_tb VALUES('C001', 'C', 260, 25);
INSERT INTO product_tb VALUES('C002', 'C', 280, 18);
📖 需求
bash
請你統計每款的SPU(貨號)數量,並按SPU數量降序排序
🍌🍌 答案
sql
select style_id,count(item_id) as SPU_num
from product_tb
group by style_id
order by SPU_num desc
🐴 SQL181 某寶店鋪的實際銷售額與客單價
🚀 建表語句
```sql drop table if exists sales_tb; CREATE TABLE sales_tb( sales_date date NOT NULL, user_id int(10) NOT NULL, item_id char(10) NOT NULL, sales_num int(10) NOT NULL, sales_price int(10) NOT NULL );
INSERT INTO sales_tb VALUES('2021-11-1', 1, 'A001', 1, 90); INSERT INTO sales_tb VALUES('2021-11-1', 2, 'A002', 2, 220); INSERT INTO sales_tb VALUES('2021-11-1', 2, 'B001', 1, 120); INSERT INTO sales_tb VALUES('2021-11-2', 3, 'C001', 2, 500); INSERT INTO sales_tb VALUES('2021-11-2', 4, 'B001', 1, 120); INSERT INTO sales_tb VALUES('2021-11-3', 5, 'C001', 1, 240); INSERT INTO sales_tb VALUES('2021-11-3', 6, 'C002', 1, 270); INSERT INTO sales_tb VALUES('2021-11-4', 7, 'A003', 1, 180); INSERT INTO sales_tb VALUES('2021-11-4', 8, 'B002', 1, 140); INSERT INTO sales_tb VALUES('2021-11-4', 9, 'B001', 1, 125); INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B003', 1, 120); INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B004', 1, 150); INSERT INTO sales_tb VALUES('2021-11-5', 10, 'A003', 1, 180); INSERT INTO sales_tb VALUES('2021-11-6', 11, 'B003', 1, 120); INSERT INTO sales_tb VALUES('2021-11-6', 10, 'B004', 1, 150); ```
📖 需求
bash
請你統計實際總銷售額與客單價(人均付費,總收入/總使用者數,結果保留兩位小數)
🍌🍌 答案
sql
select sum(sales_price) as sales_total,
round(sum(sales_price)/count(distinct user_id),2) as per_trans
from sales_tb
🐴 SQL182 某寶店鋪折扣率
🚀 建表語句
```sql drop table if exists product_tb; CREATE TABLE product_tb( item_id char(10) NOT NULL, style_id char(10) NOT NULL, tag_price int(10) NOT NULL, inventory int(10) NOT NULL ); INSERT INTO product_tb VALUES('A001', 'A', 100, 20); INSERT INTO product_tb VALUES('A002', 'A', 120, 30); INSERT INTO product_tb VALUES('A003', 'A', 200, 15); INSERT INTO product_tb VALUES('B001', 'B', 130, 18); INSERT INTO product_tb VALUES('B002', 'B', 150, 22); INSERT INTO product_tb VALUES('B003', 'B', 125, 10); INSERT INTO product_tb VALUES('B004', 'B', 155, 12); INSERT INTO product_tb VALUES('C001', 'C', 260, 25); INSERT INTO product_tb VALUES('C002', 'C', 280, 18);
drop table if exists sales_tb; CREATE TABLE sales_tb( sales_date date NOT NULL, user_id int(10) NOT NULL, item_id char(10) NOT NULL, sales_num int(10) NOT NULL, sales_price int(10) NOT NULL );
INSERT INTO sales_tb VALUES('2021-11-1', 1, 'A001', 1, 90); INSERT INTO sales_tb VALUES('2021-11-1', 2, 'A002', 2, 220); INSERT INTO sales_tb VALUES('2021-11-1', 2, 'B001', 1, 120); INSERT INTO sales_tb VALUES('2021-11-2', 3, 'C001', 2, 500); INSERT INTO sales_tb VALUES('2021-11-2', 4, 'B001', 1, 120); INSERT INTO sales_tb VALUES('2021-11-3', 5, 'C001', 1, 240); INSERT INTO sales_tb VALUES('2021-11-3', 6, 'C002', 1, 270); INSERT INTO sales_tb VALUES('2021-11-4', 7, 'A003', 1, 180); INSERT INTO sales_tb VALUES('2021-11-4', 8, 'B002', 1, 140); INSERT INTO sales_tb VALUES('2021-11-4', 9, 'B001', 1, 125); INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B003', 1, 120); INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B004', 1, 150); INSERT INTO sales_tb VALUES('2021-11-5', 10, 'A003', 1, 180); INSERT INTO sales_tb VALUES('2021-11-6', 11, 'B003', 1, 120); INSERT INTO sales_tb VALUES('2021-11-6', 10, 'B004', 1, 150); ```
📖 需求
bash
請你統計折扣率(GMV/吊牌金額,GMV指的是成交金額),折扣率保留兩位小數:
'🍌🍌 答案
sql
select
round(sum(sales_price)*100/sum(sales_num*tag_price),2) discount_rate
from sales_tb left join product_tb using(item_id)
🐴 SQL183 某寶店鋪動銷率與售罄率
🚀 建表語句
```sql drop table if exists product_tb; CREATE TABLE product_tb( item_id char(10) NOT NULL, style_id char(10) NOT NULL, tag_price int(10) NOT NULL, inventory int(10) NOT NULL ); INSERT INTO product_tb VALUES('A001', 'A', 100, 20); INSERT INTO product_tb VALUES('A002', 'A', 120, 30); INSERT INTO product_tb VALUES('A003', 'A', 200, 15); INSERT INTO product_tb VALUES('B001', 'B', 130, 18); INSERT INTO product_tb VALUES('B002', 'B', 150, 22); INSERT INTO product_tb VALUES('B003', 'B', 125, 10); INSERT INTO product_tb VALUES('B004', 'B', 155, 12); INSERT INTO product_tb VALUES('C001', 'C', 260, 25); INSERT INTO product_tb VALUES('C002', 'C', 280, 18);
drop table if exists sales_tb; CREATE TABLE sales_tb( sales_date date NOT NULL, user_id int(10) NOT NULL, item_id char(10) NOT NULL, sales_num int(10) NOT NULL, sales_price int(10) NOT NULL );
INSERT INTO sales_tb VALUES('2021-11-1', 1, 'A001', 1, 90); INSERT INTO sales_tb VALUES('2021-11-1', 2, 'A002', 2, 220); INSERT INTO sales_tb VALUES('2021-11-1', 2, 'B001', 1, 120); INSERT INTO sales_tb VALUES('2021-11-2', 3, 'C001', 2, 500); INSERT INTO sales_tb VALUES('2021-11-2', 4, 'B001', 1, 120); INSERT INTO sales_tb VALUES('2021-11-3', 5, 'C001', 1, 240); INSERT INTO sales_tb VALUES('2021-11-3', 6, 'C002', 1, 270); INSERT INTO sales_tb VALUES('2021-11-4', 7, 'A003', 1, 180); INSERT INTO sales_tb VALUES('2021-11-4', 8, 'B002', 1, 140); INSERT INTO sales_tb VALUES('2021-11-4', 9, 'B001', 1, 125); INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B003', 1, 120); INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B004', 1, 150); INSERT INTO sales_tb VALUES('2021-11-5', 10, 'A003', 1, 180); INSERT INTO sales_tb VALUES('2021-11-6', 11, 'B003', 1, 120); INSERT INTO sales_tb VALUES('2021-11-6', 10, 'B004', 1, 150); ```
📖 需求
bash
請你統計每款的動銷率(pin_rate,有銷售的SKU數量/在售SKU數量)與售罄率(sell-through_rate,GMV/備貨值,備貨值=吊牌價*庫存數),按style_id升序排序
🍌🍌 答案
sql
select
style_id
,round(sum(t2.sales_num)/(sum(inventory)-sum(t2.sales_num))*100,2) pin_rate
,round(sum(sales_price)/sum(tag_price*inventory)*100,2) sell_rate
from product_tb t1 join
(select
item_id
,sum(sales_num) sales_num
,sum(sales_price) sales_price
from sales_tb
group by item_id) t2
using(item_id)
group by style_id
order by style_id
🐴 SQL184 某寶店鋪連續2天及以上購物的使用者及其對應的天數
🚀 建表語句
```sql drop table if exists sales_tb; CREATE TABLE sales_tb( sales_date date NOT NULL, user_id int(10) NOT NULL, item_id char(10) NOT NULL, sales_num int(10) NOT NULL, sales_price int(10) NOT NULL );
INSERT INTO sales_tb VALUES('2021-11-1', 1, 'A001', 1, 90); INSERT INTO sales_tb VALUES('2021-11-1', 2, 'A002', 2, 220); INSERT INTO sales_tb VALUES('2021-11-1', 2, 'B001', 1, 120); INSERT INTO sales_tb VALUES('2021-11-2', 3, 'C001', 2, 500); INSERT INTO sales_tb VALUES('2021-11-2', 4, 'B001', 1, 120); INSERT INTO sales_tb VALUES('2021-11-3', 5, 'C001', 1, 240); INSERT INTO sales_tb VALUES('2021-11-3', 6, 'C002', 1, 270); INSERT INTO sales_tb VALUES('2021-11-4', 7, 'A003', 1, 180); INSERT INTO sales_tb VALUES('2021-11-4', 8, 'B002', 1, 140); INSERT INTO sales_tb VALUES('2021-11-4', 9, 'B001', 1, 125); INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B003', 1, 120); INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B004', 1, 150); INSERT INTO sales_tb VALUES('2021-11-5', 10, 'A003', 1, 180); INSERT INTO sales_tb VALUES('2021-11-6', 11, 'B003', 1, 120); INSERT INTO sales_tb VALUES('2021-11-6', 10, 'B004', 1, 150); ```
📖 需求
bash
請你統計連續2天及以上在該店鋪購物的使用者及其對應的次數(若有多個使用者,按user_id升序排序)
🍌🍌 答案
sql
select user_id,
count(diff) date_cnt
from
(select user_id,
sales_date - rk diff
from
(select user_id,
sales_date,
rank() over(partition by sales_date) rk
from (select distinct user_id,sales_date from sales_tb)aa )a) b
group by user_id
having date_cnt>=2
- 還用寬表?體驗一下DQL成就新一代的BI吧
- 大資料計算,如何優化SQL?
- MySQL高可用之MHA架構企業實戰
- 【牛客刷題-SQL大廠面試真題】NO5.某寶店鋪分析(電商模式)
- 內卷時代,35歲程式猿轉行DBA|2022 年中總結
- MySQL高可用之多源複製
- MySQL8.0新特性搶先看,效能又雙叒提升了
- Oracle表碎片對效能有多大影響?
- 趣味端午節,程式猿特殊的獻禮
- 【微信小程式開發】第1篇:開發工具安裝及程式配置
- CentOS7部署Mysql8.0.27
- MySQL中COLLATE是用來做什麼的?
- PostgreSQL大總結,小白最適合了
- Mysql快速找回root密碼妙招
- Python字典介紹
- Mysql日期型別剖析
- Python元組詳解
- Python列表詳解
- Python字串詳解
- Windows安裝Mysql(msi 圖形安裝)