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;

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;

参考：SQL会员题