力扣sql中等篇练习(五)
1 股票的资本收益
1.1 题目内容
1.1.1 基本题目信息
1.1.2 示例输入输出
1.2 示例sql语句
SELECT stock_name,SUM(IF(operation='Buy',price*-1,price)) capital_gain_loss
FROM Stocks
GROUP BY stock_name
1.3 运行截图
2 每位顾客最经常订购的商品
2.1 题目内容
2.1.1 基本题目信息1
2.1.2 基本题目信息2
2.1.3 示例输入输出
a 示例输入
b 示例输出
2.2 示例sql语句
SELECT t1.customer_id,t1.product_id,p2.product_name
FROM
(SELECT o.customer_id,o.product_id,count(*) n1FROM Orders oINNER JOIN Products pON o.product_id=p.product_idGROUP BY o.customer_id,o.product_id
)t1
INNER JOIN
(SELECT a1.customer_id,max(a1.number) n2FROM(SELECT customer_id,count(*) numberFROM OrdersGROUP BY customer_id,product_id)a1GROUP BY a1.customer_id
)t2
ON t1.customer_id=t2.customer_id AND t1.n1=t2.n2
INNER JOIN Products p2
ON t1.product_id=p2.product_id
2.3 运行截图
3 报告的记录 ||
3.1 题目内容
3.1.1 基本题目信息1
3.1.2 基本题目信息2
3.1.3 示例输入输出
a 示例输入
b 示例输出
3.2 示例sql语句
SELECT ROUND(SUM(t1.num)/count(*),2) average_daily_percent
FROM
(SELECT a.action_date,count(r.remove_date)/count(*)*100 numFROM(SELECT distinct post_id,action_dateFROM ActionsWHERE action='report' AND extra='spam')aLEFT JOIN Removals rON a.post_id=r.post_idGROUP BY a.action_date
) t1
3.3 运行截图