【牛客刷題-SQL大廠面試真題】NO5.某寶店鋪分析(電商模式)

語言: CN / TW / HK

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

在這裡插入圖片描述 在這裡插入圖片描述