一、股票的资本损益
题目描述：
Stocks 表： +---------------+---------+ | Column Name | Type | +---------------+---------+ | stock_name | varchar | | operation | enum | | operation_day | int | | price | int | +---------------+---------+ (stock_name, day) 是这张表的主键 operation 列使用的是一种枚举类型，包括：('Sell','Buy') 此表的每一行代表了名为 stock_name 的某支股票在 operation_day 这一天的操作价格。 保证股票的每次'Sell'操作前，都有相应的'Buy'操作。 编写一个SQL查询来报告每支股票的资本损益。 股票的资本损益是一次或多次买卖股票后的全部收益或损失。 以任意顺序返回结果即可。 SQL查询结果的格式如下例所示： Stocks 表: +---------------+-----------+---------------+--------+ | stock_name | operation | operation_day | price | +---------------+-----------+---------------+--------+ | Leetcode | Buy | 1 | 1000 | | Corona Masks | Buy | 2 | 10 | | Leetcode | Sell | 5 | 9000 | | Handbags | Buy | 17 | 30000 | | Corona Masks | Sell | 3 | 1010 | | Corona Masks | Buy | 4 | 1000 | | Corona Masks | Sell | 5 | 500 | | Corona Masks | Buy | 6 | 1000 | | Handbags | Sell | 29 | 7000 | | Corona Masks | Sell | 10 | 10000 | +---------------+-----------+---------------+--------+ Result 表: +---------------+-------------------+ | stock_name | capital_gain_loss | +---------------+-------------------+ | Corona Masks | 9500 | | Leetcode | 8000 | | Handbags | -23000 | +---------------+-------------------+ Leetcode 股票在第一天以1000美元的价格买入，在第五天以9000美元的价格卖出。资本收益=9000-1000=8000美元。 Handbags 股票在第17天以30000美元的价格买入，在第29天以7000美元的价格卖出。资本损失=7000-30000=-23000美元。 Corona Masks 股票在第1天以10美元的价格买入，在第3天以1010美元的价格卖出。在第4天以1000美元的价格再次购买，在第5天以500美元的价格出售。最后，它在第6天以1000美元的价格被买走，在第10天以10000美元的价格被卖掉。资本损益是每次（’Buy'->'Sell'）操作资本收益或损失的和=（1010-10）+（500-1000）+（10000-1000）=1000-500+9000=9500美元。
代码详解：
select stock_name, sum(case when operation="Buy" then -price else price end) as capital_gain_loss from Stocks group by stock_name;
二、排名靠前的旅行者
题目描述：
表：Users +---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | name | varchar | +---------------+---------+ id 是该表单主键。 name 是用户名字。 表：Rides +---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | user_id | int | | distance | int | +---------------+---------+ id 是该表单主键。 user_id 是本次行程的用户的 id, 而该用户此次行程距离为 distance 。 写一段 SQL , 报告每个用户的旅行距离。 返回的结果表单，以 travelled_distance 降序排列 ，如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列 。 查询结果格式如下例所示。 Users 表： +------+-----------+ | id | name | +------+-----------+ | 1 | Alice | | 2 | Bob | | 3 | Alex | | 4 | Donald | | 7 | Lee | | 13 | Jonathan | | 19 | Elvis | +------+-----------+ Rides 表： +------+----------+----------+ | id | user_id | distance | +------+----------+----------+ | 1 | 1 | 120 | | 2 | 2 | 317 | | 3 | 3 | 222 | | 4 | 7 | 100 | | 5 | 13 | 312 | | 6 | 19 | 50 | | 7 | 7 | 120 | | 8 | 19 | 400 | | 9 | 7 | 230 | +------+----------+----------+ Result 表： +----------+--------------------+ | name | travelled_distance | +----------+--------------------+ | Elvis | 450 | | Lee | 450 | | Bob | 317 | | Jonathan | 312 | | Alex | 222 | | Alice | 120 | | Donald | 0 | +----------+--------------------+ Elvis 和 Lee 旅行了 450 英里，Elvis 是排名靠前的旅行者，因为他的名字在字母表上的排序比 Lee 更小。 Bob, Jonathan, Alex 和 Alice 只有一次行程，我们只按此次行程的全部距离对他们排序。 Donald 没有任何行程, 他的旅行距离为 0。
代码详解：
# ifnull（x，y）：如果x不为null，则函数值为x，否则为y select name, coalesce(sum(distance), 0) travelled_distance from users u left join rides r on u.id=r.user_id group by u.id order by travelled_distance desc, name;
三、市场分析
题目描述：
Table: Users +----------------+---------+ | Column Name | Type | +----------------+---------+ | user_id | int | | join_date | date | | favorite_brand | varchar | +----------------+---------+ 此表主键是 user_id。 表中描述了购物网站的用户信息，用户可以在此网站上进行商品买卖。 Table: Orders +---------------+---------+ | Column Name | Type | +---------------+---------+ | order_id | int | | order_date | date | | item_id | int | | buyer_id | int | | seller_id | int | +---------------+---------+ 此表主键是 order_id。 外键是 item_id 和（buyer_id，seller_id）。 Table: Items +---------------+---------+ | Column Name | Type | +---------------+---------+ | item_id | int | | item_brand | varchar | +---------------+---------+ 此表主键是 item_id。 请写出一条SQL语句以查询每个用户的注册日期和在 2019 年作为买家的订单总数。 以 任意顺序 返回结果表。 查询结果格式如下。 示例 1: 输入： Users 表: +---------+------------+----------------+ | user_id | join_date | favorite_brand | +---------+------------+----------------+ | 1 | 2018-01-01 | Lenovo | | 2 | 2018-02-09 | Samsung | | 3 | 2018-01-19 | LG | | 4 | 2018-05-21 | HP | +---------+------------+----------------+ Orders 表: +----------+------------+---------+----------+-----------+ | order_id | order_date | item_id | buyer_id | seller_id | +----------+------------+---------+----------+-----------+ | 1 | 2019-08-01 | 4 | 1 | 2 | | 2 | 2018-08-02 | 2 | 1 | 3 | | 3 | 2019-08-03 | 3 | 2 | 3 | | 4 | 2018-08-04 | 1 | 4 | 2 | | 5 | 2018-08-04 | 1 | 3 | 4 | | 6 | 2019-08-05 | 2 | 2 | 4 | +----------+------------+---------+----------+-----------+ Items 表: +---------+------------+ | item_id | item_brand | +---------+------------+ | 1 | Samsung | | 2 | Lenovo | | 3 | LG | | 4 | HP | +---------+------------+ 输出： +-----------+------------+----------------+ | buyer_id | join_date | orders_in_2019 | +-----------+------------+----------------+ | 1 | 2018-01-01 | 1 | | 2 | 2018-02-09 | 2 | | 3 | 2018-01-19 | 0 | | 4 | 2018-05-21 | 0 | +-----------+------------+----------------+
代码详解：
# 1.使用 Orders 表计算每个用户的产品数。使用 group by 聚合每个用户的购买记录。使用 between 筛选出时间为 2019 年的数据。使用count(order_id) 计算出每个用户的订单数。 # 2.使用 Users 表得到所有用户及其注册时间。并使用 left join，通过 user_id 和第一步的数据连接，求每个用户的订单数。 如果一个用户没有任何订单，那么第一步的数据中不会有这个用户的数据，最后的 orders_in_2019 会显示为 null，所以我们还需要使用 ifnull，如果数据为 null，将其改为 0。 select Users.user_id as buyer_id, join_date, ifnull(UserBuy.cnt, 0) as orders_in_2019 from Users left join ( select buyer_id, count(order_id) cnt from Orders where order_date between '2019-01-01' and '2019-12-31' group by buyer_id ) UserBuy on Users.user_id = UserBuy.buyer_id;
