Leecode SQL

SQL会员题2

618.学生地理信息报告

一所学校有来自亚洲、欧洲和美洲的学生。写一个查询语句实现对大洲(continent) 列的透视表操作，使得每个学生按照姓名的字母顺序依次排列在对应的大洲下面。输出的标题应依次为美洲(America)、亚洲(Asia) 和欧洲(Europe) 。
测试用例的生成使得来自美国的学生，人数不少于亚洲或欧洲的学生人数。

##数据：
CREATE TABLE student(
	NAME VARCHAR(20),
	continent VARCHAR(20)
);

INSERT INTO student(NAME,continent)
VALUES('Jane','America'),('Pascal','Europe'),('Xi','Asia'),('Jack','America');

SELECT * FROM student;

##题解：
SELECT MAX(CASE WHEN continent = 'America' THEN NAME END) AS 'America',
MAX(CASE WHEN continent = 'Asia' THEN NAME END) AS 'Asia',
MAX(CASE WHEN continent = 'Europe' THEN NAME END) AS 'Europe'
FROM (SELECT *,row_number() over(PARTITION BY continent ORDER BY NAME) AS rk
	FROM student) temp
GROUP BY rk;

1069.产品销售分析Ⅱ

编写一个SQL查询，按产品id product id来统计每个产品的销售总量。

##数据：
CREATE TABLE Sales(
	sale_id INT,
	product_id INT,
	YEAR INT,
	quantity INT,
	price INT
);

CREATE TABLE Product(
	product_id INT,
	product_name VARCHAR(20)
);
INSERT INTO Sales(sale_id,product_id,YEAR,quantity,price)
VALUES(1,100,2008,10,5000),(2,100,2009,12,5000),(7,200,2011,15,9000);

INSERT INTO Product(product_id,product_name)
VALUES(100,'Nokia'),(200,'Apple'),(300,'Samsung');

SELECT * FROM Sales;
SELECT * FROM Product;

##题解：
SELECT product_id,SUM(quantity) AS total_quantity
FROM Sales JOIN Product USING(product_id)
GROUP BY product_id;

1070.产品销售分析Ⅲ

编写一个SQL查询,选出每个销售产品第一年销售的产品id、年份、数量和价格。
结果表中的条可以按任意顺序例。

##题解：
SELECT product_id,YEAR AS first_year,quantity,price
FROM(
	SELECT *,dense_rank() over(PARTITION BY product_id ORDER BY YEAR) rk
	FROM Sales
) temp
WHERE rk = 1;

1076.项目员工Ⅱ

编写一个SQL查询，报告所有雇员最多的项目。

##数据：
CREATE TABLE Project(
	project_id INT,
	employee_id INT
);

CREATE TABLE Employee2(
	employee_id INT,
	NAME VARCHAR(20),
	experience_years INT
);

INSERT INTO Project(project_id,employee_id)
VALUES(1,1),(1,2),(1,3),(2,1),(2,4);
INSERT INTO Employee2(employee_id,NAME,experience_years)
VALUES(1,'Khaled',3),(2,'Ali',2),(3,'John',3),(4,'Doe',2);
SELECT * FROM Project;
SELECT * FROM Employee2;

##题解：
SELECT project_id
FROM Project
GROUP BY project_id
ORDER BY COUNT(DISTINCT employee_id) DESC
LIMIT 1;

1077.项目员工Ⅲ

每个项目中最有经验的员工

##题解：
SELECT project_id,employee_id
FROM(
SELECT project_id,employee_id,dense_rank() over(PARTITION BY project_id ORDER BY experience_years DESC) AS rk
FROM Project JOIN Employee2 USING(employee_id)
) a
WHERE rk = 1;

##1082.销售分析Ⅰ
/*
编写一个SQL查询，查询总销售额最高的销售者，如果有并列的，就都展示出来。

*/
##数据：
CREATE TABLE Product1084(
	product_id INT PRIMARY KEY,  
	product_name VARCHAR(20),
	unit_price INT   
);

CREATE TABLE Sales1084(
	seller_id INT,   
	product_id INT,     
	buyer_id INT,     
	sale_date DATE,    
	quantity INT,     
	price INT,
	FOREIGN KEY(product_id) REFERENCES Product1084(product_id)
  
);
INSERT INTO Product1084(product_id,product_name,unit_price)
VALUES(1,'S8',1000),(2,'G4',800),(3,'iPhone',1400);
SELECT	* FROM Product1084;
INSERT INTO Sales1084(seller_id,product_id,buyer_id,sale_date,quantity,price)
VALUES(1,1,1,'2019-01-21',2,2000),(1,2,2,'2019-02-17',1,800),(2,2,3,'2019-06-02',1,800),(3,3,4,'2019-05-13',2,2800);
SELECT * FROM Sales1084;

##题解：
SELECT seller_id
FROM(
SELECT seller_id,dense_rank() over(ORDER BY total_price DESC) rk
FROM(
SELECT DISTINCT seller_id,SUM(price) over(PARTITION BY seller_id) total_price
FROM Sales1084) a) aa
WHERE rk = 1;

1083.销售分析Ⅱ

查询购买S8但没有购买iphone的用户id

##题解：
SELECT buyer_id
FROM Sales1084 LEFT JOIN Product1084 USING(product_id)
GROUP BY buyer_id
HAVING SUM(product_name = 'S8')>0 AND SUM(product_name = 'iPhone') = 0;

SQL会员题3

1097.游戏玩法分析Ⅴ

玩家的安装日期定义为该玩家的第一个登录日。玩家的第一天留存率定义为:假定安装日期为X的玩家的数量为N,其中在X之后的一天重新登录的玩家数量为M，M/N 就是第一天留存率，四舍五入到小数点后两位。
编写一个SQL查询,报告所有安装日期、当天安装游戏的玩家数量和玩家的第一天留存率。

##数据：
CREATE TABLE Activity(
	player_id INT ,
	device_id INT,
	event_date DATE,
	gamesplayed INT,
	PRIMARY KEY(player_id,event_date)
	
);
INSERT INTO Activity
VALUES(1,2,'2016-03-01',5),(1,2,'2016-03-02',6),(2,3,'2017-06-25',1),(3,1,'2016-03-01',0),(3,4,'2016-07-03',5);

SELECT * FROM Activity;

##题解：
SELECT  install_dt,COUNT(DISTINCT player_id) AS installs,
ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(event_date,install_dt) =1 THEN player_id END)/
COUNT(DISTINCT player_id),2) AS Day1_retention
FROM
(SELECT player_id,event_date,MIN(event_date) over(PARTITION BY player_id) AS install_dt
FROM Activity) temp
GROUP BY install_dt;

1098.小众书籍

你需要写一-段SQL命令,筛选出过去一年中订单总量少于10本的书籍。
注意:不考虑上架(available from)距今不满-个月的书籍。并且假设今天是2019-06-23。

##数据：
CREATE TABLE Books(
	book_id INT PRIMARY KEY,
	NAME VARCHAR(20),
	available_from DATE
);

CREATE TABLE Orders(
	order_id INT PRIMARY KEY,
	book_id INT,
	quantity INT,
	dispatch_date DATE,
	FOREIGN KEY(book_id) REFERENCES Books(book_id)
);

INSERT INTO Books
VALUES(1,'Kalila And Demna','2010-01-01'),(2,'28 Letters','2012-05-12'),
(3,'The Hobbit','2019-06-10'),(4,'13 Reasons Why','2019-06-01'),
(5,'The Hunger Games','2008-09-21');

INSERT INTO Orders
VALUES(1,1,2,'2018-07-26'),(2,1,1,'2018-11-05'),
(3,3,8,'2019-06-11'),(4,4,6,'2019-06-05'),(5,4,5,'2019-06-20'),
(6,5,9,'2009-02-02'),(7,5,8,'2010-04-13');

##题解：
SELECT B.book_id,NAME
FROM (SELECT book_id,NAME 
      FROM Books 
      WHERE available_from < DATE_SUB('2019-06-23',INTERVAL 1 MONTH)) B
LEFT JOIN(SELECT order_id,book_id,quantity 
	FROM Orders
	WHERE dispatch_date BETWEEN DATE_SUB('2019-06-23',INTERVAL 1 YEAR) AND '2019-06-23'
	) O USING(book_id)
GROUP BY B.book_id,NAME;
HAVING IFNULL(SUM(quantity),0) < 10;

1107.每日新用户统计

编写一个SQL查询，以查询从今天起最多90天内，每个日期该日期首次登录的用户数。假设今天是2019-06-30.

##数据：
DROP TABLE traffic;
CREATE TABLE Traffic(
	user_id INT,
	activity ENUM('login','logout','jobs','groups','homepage'),
	activity_date DATE
	
);
INSERT INTO Traffic
VALUES(1,'login','2019-05-01'),
(1,'homepage','2019-05-01'),
(1,'logout','2019-05-01'),
(2,'login','2019-06-21'),
(2,'logout','2019-06-21'),
(3,'login','2019-01-01'),
(3,'jobs','2019-01-01'),
(3,'logout','2019-01-01'),
(4,'login','2019-06-21'),
(4,'groups','2019-06-21'),
(4,'logout','2019-06-21'),
(5,'login','2019-03-01'),
(5,'logout','2019-03-01'),
(5,'login','2019-06-21'),
(5,'logout','2019-06-21');

SELECT * FROM Traffic;

##题解：
SELECT first_login_date,COUNT(DISTINCT user_id) user_count
FROM(
SELECT user_id,MIN(activity_date) over(PARTITION BY user_id) AS first_login_date
FROM Traffic
WHERE activity = 'login') temp
WHERE first_login_date BETWEEN DATE_SUB('2019-06-30',INTERVAL 89 DAY) AND '2019-06-30'
GROUP BY first_login_date;

1112.每位学生的最高成绩

编写一个SQL查询，查询每位学生获得的最高成绩和它所对应的科目，若科目成绩并列，取course_id最小的一门。查询结果需按student_id增序进行排序。

##数据：
CREATE TABLE Enrollments(
	student_id INT,
	course_id INT,
	grade INT,
	CONSTRAINT pk_EnrollmentsID PRIMARY KEY (student_id,course_id)

);

INSERT INTO Enrollments
VALUES(2,2,95),(2,3,95),(1,1,90),
(1,2,99),(3,1,80),(3,2,75),(3,3,82);

SELECT * FROM Enrollments;

##题解：
SELECT DISTINCT student_id,course_id,grade
FROM(
SELECT student_id,course_id,grade,row_number() over(PARTITION BY student_id ORDER BY grade DESC,course_id) rk
FROM Enrollments
) temp
WHERE rk = 1
ORDER BY student_id;

1113.报告的记录

编写—条SQL，查询每种报告理由(report reason）在昨天的不同报告数量(post_id)。假设今天是2019-07-05。

##数据：
CREATE TABLE Actions(
	user_id INT,
	post_id INT,
	action_date DATE,
	ACTION ENUM('view','like','reaction','comment','report','share'),
	extra VARCHAR(20)
);
DROP TABLE Actions;
INSERT INTO Actions
VALUES
(1,1,'2019-07-01','view',NULL),
(1,1,'2019-07-01','like',NULL),
(1,1,'2019-07-01','share',NULL),
(2,4,'2019-07-04','view',NULL),
(2,4,'2019-07-04','report','spam'),
(3,4,'2019-07-04','view',NULL),
(3,4,'2019-07-04','report','spam'),
(4,3,'2019-07-02','view',NULL),
(4,3,'2019-07-02','report','spam'),
(5,2,'2019-07-04','view',NULL),
(5,2,'2019-07-04','report','racism'),
(5,5,'2019-07-04','view',NULL),
(5,5,'2019-07-04','view','racism');

##题解：
SELECT extra AS report_reason,COUNT(DISTINCT post_id) report_count
FROM Actions
WHERE action_date = '2019-07-04' AND extra IS NOT NULL
GROUP BY extra
ORDER BY report_count;

1126.查询活跃业务

写一段SQL来查询所有活跃的业务。如果一个业务的某个事件类型的发生次数大于此事件类型在所有业务中的平均发生次数，并且该业务至少有两个这样的事件类型，那么该业务就可被看做是活跃业务。

##数据：
CREATE TABLE EVENTS(
	business_id INT,
	event_type VARCHAR(20),
	occurences INT,
	CONSTRAINT pk_EventsID PRIMARY KEY (business_id,event_type)

);

INSERT INTO EVENTS
VALUES(1,'reviews',7),(3,'reviews',3),(1,'ads',11),
(2,'ads',7),(3,'ads',6),(1,'page views',3),(2,'page views',12);

##题解：
WITH temp AS (
SELECT event_type,AVG(occurences) avg_occurences
FROM EVENTS
GROUP BY event_type
)
SELECT business_id  
FROM EVENTS
JOIN temp USING(event_type)
WHERE occurences > avg_occurences
GROUP BY business_id
HAVING COUNT(*) >= 2;

1127.用户购买平台

写一段SQL来查找每天仅使用手机端用户、仅使用桌面端用户和同时使用桌面端和手机端的用户人数和总支出金额。

##数据：
CREATE TABLE Spending(
	user_id INT,
	spend_date DATE,
	platform ENUM('desktop','mobile'),
	amount INT,
	CONSTRAINT pk_Spending PRIMARY KEY (user_id,spend_date,platform)
	
);

INSERT INTO Spending 
VALUES(1,'2019-07-01','mobile',100),
      (1,'2019-07-01','desktop',100),
      (2,'2019-07-01','mobile',100),
      (2,'2019-07-02','mobile',100),
      (3,'2019-07-01','desktop',100),
      (3,'2019-07-02','desktop',100);

##题解：
###法一：
WITH temp AS(
SELECT spend_date,user_id,COUNT(DISTINCT platform) platform_count
FROM Spending
GROUP BY spend_date,user_id
)
SELECT *
FROM(
SELECT S.spend_date,platform,SUM(amount) total_amount,COUNT(DISTINCT S.user_id) total_users
FROM Spending S JOIN temp t ON S.spend_date = t.spend_date AND S.user_id = t.user_id
WHERE platform_count = 1
GROUP BY S.spend_date,platform
UNION ALL
SELECT S.spend_date,'both',SUM(CASE WHEN platform_count = 2 THEN amount ELSE 0 END) total_amount,
COUNT(DISTINCT CASE WHEN platform_count = 2 THEN S.user_id ELSE NULL END) total_users
FROM Spending S LEFT JOIN temp t ON S.spend_date = t.spend_date AND S.user_id = t.user_id
GROUP BY S.spend_date) a
ORDER BY spend_date
;

###法二：
SELECT t1.spend_date, t1.platform, COALESCE(SUM(total_amount), 0) AS total_amount, COALESCE(COUNT(user_id), 0) AS total_users
FROM (
    SELECT DISTINCT spend_date, 'desktop' AS platform FROM spending
    UNION 
    SELECT DISTINCT spend_date, 'mobile' AS platform FROM spending
    UNION
    SELECT DISTINCT spend_date, 'both' AS platform FROM spending
) t1
LEFT JOIN (
    SELECT  user_id, 
            spend_date, 
            CASE WHEN COUNT(platform) = 2 THEN 'both' ELSE platform END AS platform,
            SUM(amount) AS total_amount
    FROM spending 
    GROUP BY user_id, spend_date
) t2
ON t1.spend_date = t2.spend_date AND t1.platform = t2.platform
GROUP BY spend_date, platform
ORDER BY t1.spend_date;

1132.报告的记录Ⅱ

编写一段SQL来查找:在被报告为垃圾广告的帖子中，被移除的帖子的每日平均占比，四舍五入到小数点后2位。

##数据：
CREATE TABLE Actions(
	user_id INT,
	post_id INT,
	action_date DATE,
	ACTION ENUM('view','like','reaction','comment','report','share'),
	extra VARCHAR(20)
);
DROP TABLE Actions;
INSERT INTO Actions
VALUES
(1,1,'2019-07-01','view',NULL),
(1,1,'2019-07-01','like',NULL),
(1,1,'2019-07-01','share',NULL),
(2,2,'2019-07-04','view',NULL),
(2,2,'2019-07-04','report','spam'),
(3,4,'2019-07-04','view',NULL),
(3,4,'2019-07-04','report','spam'),
(4,3,'2019-07-02','view',NULL),
(4,3,'2019-07-02','report','spam'),
(5,2,'2019-07-04','view',NULL),
(5,2,'2019-07-04','report','racism'),
(5,5,'2019-07-04','view',NULL),
(5,5,'2019-07-04','view','racism');

CREATE TABLE Removals(
	post_id INT PRIMARY KEY,
	remove_date DATE
);
INSERT INTO Removals
VALUES(2,'2019-07-20'),(3,'2019-07-18');
SELECT * FROM Actions;

##题解：
SELECT ROUND(AVG(rate) * 100, 2) AS average_daily_percent
FROM (
    SELECT action_date, COUNT(DISTINCT removals.post_id) / COUNT(DISTINCT actions.post_id) AS rate
    FROM actions 
    LEFT JOIN removals
    ON actions.post_id = removals.post_id
    WHERE extra = 'spam'
    GROUP BY action_date
) t;

1142.报告的记录Ⅱ

编写SQL查询以查找截至2019-07-27(含)的30天内每个用户的平均会话数，四舍五入到小数点后两位。只统计那些会话期间用户至少进行一项活动的有效会话。

##数据：
CREATE TABLE Activity(
	user_id INT,
	session_id INT,
	activity_date DATE,
	activity_type ENUM('open_session','end_session','scroll_down','send_message')

);

INSERT INTO Activity
VALUES
(1,1,'2019-07-20','open_session'),
(1,1,'2019-07-20','scroll_down'),
(1,1,'2019-07-20','end_session'),
(2,4,'2019-07-20','open_session'),
(2,4,'2019-07-21','send_message'),
(2,4,'2019-07-21','end_session'),
(3,2,'2019-07-21','open_session'),
(3,2,'2019-07-21','send_message'),
(3,2,'2019-07-21','end_session'),
(3,5,'2019-07-21','open_session'),
(3,5,'2019-07-21','scroll_down'),
(3,5,'2019-07-21','end_session'),
(4,3,'2019-06-25','open_session'),
(4,3,'2019-06-25','end_session');

##题解：
SELECT IFNULL(ROUND(AVG(session_count),2),0)average_sessions_per_user
FROM(
SELECT user_id,COUNT(DISTINCT session_id) session_count
FROM Activity
WHERE activity_date BETWEEN DATE_SUB('2019-07-27',INTERVAL 29 DAY) AND '2019-07-27'
GROUP BY user_id) temp;

1149.文章浏览Ⅱ

编写—条SQL查询来找出在同—天阅读至少两篇文章的人。结果按照id升序排序。

##数据：
CREATE TABLE Views(
	article_id INT,
	author_id INT,
	viewer_id INT,
	view_date DATE
);

INSERT INTO Views
VALUES(1,3,5,'2019-08-01'),
(3,4,5,'2019-08-01'),
(1,3,6,'2019-08-02'),
(2,7,7,'2019-08-01'),
(2,7,6,'2019-08-02'),
(4,7,1,'2019-07-22'),
(3,4,4,'2019-07-21'),
(3,4,4,'2019-07-21');

##题解：
SELECT viewer_id AS id
FROM Views
GROUP BY viewer_id,view_date
HAVING COUNT(DISTINCT article_id) >= 2
ORDER BY viewer_id ;

1159.市场分析Ⅱ

写一个SQL查询确定每一个用户按日期顺序卖出的第二件商品的品牌是否是他们最喜爱的品牌。如果一个用户卖出少于两件商品，查询的结果是no 。题目保证没有一个用户在一天中卖出超过—件商品

##数据：
CREATE TABLE Users(
	user_id INT PRIMARY KEY,
	join_date DATE,
	favorite_brand VARCHAR(20)
);

CREATE TABLE Orders(
	order_id INT PRIMARY KEY,
	order_date DATE,
	item_id INT,
	buyer_id INT,
	seller_id INT,
	FOREIGN KEY(item_id) REFERENCES Items(item_id),
	FOREIGN KEY(buyer_id) REFERENCES Users(user_id),
	FOREIGN KEY(seller_id) REFERENCES Users(user_id)
);

CREATE TABLE Items(
	item_id INT PRIMARY KEY,
	item_brand VARCHAR(20)
);

INSERT INTO Users
VALUES(1,'2019-01-01','Lenovo'),
(2,'2019-02-09','Samsung'),
(3,'2019-01-19','LG'),
(4,'2019-05-21','HP');

INSERT INTO Items
VALUES(1,'Samsung'),
(2,'Lenovo'),
(3,'LG'),
(4,'HP');

INSERT INTO Orders
VALUES(1,'2019-08-01',4,1,2),
(2,'2019-08-02',2,1,3),
(3,'2019-08-03',3,2,3),
(4,'2019-08-04',1,4,2),
(5,'2019-08-04',1,3,4),
(6,'2019-08-05',2,2,4);

##题解：
WITH second_item AS(
SELECT seller_id,item_brand
FROM
  (SELECT 
    seller_id,
    item_id,
    row_number () over (
      PARTITION BY seller_id 
  ORDER BY order_date
  ) rk 
  FROM
    Orders) temp 
    JOIN Items USING(item_id)
WHERE rk = 2 
	
)
SELECT user_id AS seller_id,
CASE WHEN item_brand = favorite_brand THEN 'yes'
ELSE 'no' END AS 2nd_item_fav_brand
FROM Users U 
LEFT JOIN second_item sei ON U.user_id = sei.seller_id;