1084. 销售分析III
题目
表: Product
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| product_id | int |
| product_name | varchar |
| unit_price | int |
+--------------+---------+
product_id 是该表的主键(具有唯一值的列)。
该表的每一行显示每个产品的名称和价格。
表:Sales
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| seller_id | int |
| product_id | int |
| buyer_id | int |
| sale_date | date |
| quantity | int |
| price | int |
+------ ------+---------+
这个表可能有重复的行。
product_id 是 Product 表的外键(reference 列)。
该表的每一行包含关于一个销售的一些信息。
编写解决方案,报告2019年春季
才售出的产品。即仅在**2019-01-01**
至**2019-03-31**
(含)之间出售的商品。
答案
#先进行表内连接,根据product_id进行分组,记录数不变就说明都在指定日期
select t1.product_id,t1.product_name
from Product t1
inner join Sales t2 using(product_id)
group by t1.product_id
having count(t2.sale_date between '2019-01-01' and '2019-03-31' or null) = count(*)
1141. 查询近30天活跃用户数
题目
表:Activity
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| session_id | int |
| activity_date | date |
| activity_type | enum |
+---------------+---------+
该表没有包含重复数据。
activity_type 列是 ENUM(category) 类型, 从 ('open_session', 'end_session', 'scroll_down', 'send_message') 取值。
该表记录社交媒体网站的用户活动。
注意,每个会话只属于一个用户。
编写解决方案,统计截至 2019-07-27
(包含2019-07-27),近 30
天的每日活跃用户数(当天只要有一条活动记录,即为活跃用户)。
答案
#where 过滤 ,groyp by 分组
select activity_date as day,count(distinct user_id) as active_users
from Activity
where activity_date between '2019-06-28' and '2019-07-27'
group by activity_date
1148. 文章浏览 I
题目
Views
表:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| article_id | int |
| author_id | int |
| viewer_id | int |
| view_date | date |
+---------------+---------+
此表可能会存在重复行。(换句话说,在 SQL 中这个表没有主键)
此表的每一行都表示某人在某天浏览了某位作者的某篇文章。
请注意,同一人的 author_id 和 viewer_id 是相同的。
请查询出所有浏览过自己文章的作者结果按照 id
升序排列。
答案
select distinct author_id as id
from Views
where author_id = viewer_id
order by id asc;
1158. 市场分析 I
题目
表: Users
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| user_id | int |
| join_date | date |
| favorite_brand | varchar |
+----------------+---------+
user_id 是此表主键(具有唯一值的列)。
表中描述了购物网站的用户信息,用户可以在此网站上进行商品买卖。
表: Orders
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| order_date | date |
| item_id | int |
| buyer_id | int |
| seller_id | int |
+---------------+---------+
order_id 是此表主键(具有唯一值的列)。
item_id 是 Items 表的外键(reference 列)。
(buyer_id,seller_id)是 User 表的外键。
表:Items
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| item_id | int |
| item_brand | varchar |
+---------------+---------+
item_id 是此表的主键(具有唯一值的列)。
编写解决方案找出每个用户的注册日期和在 2019
年作为买家的订单总数。
答案
select t1.user_id as buyer_id,t1.join_date as join_date, count(order_id) as orders_in_2019
from Users t1
left join Orders t2 on t1.user_id = t2.buyer_id and year(t2.order_date) = 2019
group by t1.user_id
1164. 指定日期的产品价格
题目
产品数据表: Products
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| new_price | int |
| change_date | date |
+---------------+---------+
(product_id, change_date) 是此表的主键(具有唯一值的列组合)。
这张表的每一行分别记录了 某产品 在某个日期 更改后 的新价格。
编写一个解决方案,找出在 2019-08-16
时全部产品的价格,假设所有产品在修改前的价格都是 10
。
答案
#先查出所有的产品id
#再查出 产品id和其最新价格 根据 产品id和最新日期max()
select t1.product_id,ifnull(t2.new_price,10) as price
from (select distinct product_id from Products) as t1
left join (select product_id,new_price
from Products
where (product_id,change_date) in (select product_id,max(change_date)
from Products
where change_date <= '2019-08-16'
group by product_id)
) as t2
on t1.product_id = t2.product_id;
1174. 即时食物配送 II
题目
配送表: Delivery
+-----------------------------+---------+
| Column Name | Type |
+-----------------------------+---------+
| delivery_id | int |
| customer_id | int |
| order_date | date |
| customer_pref_delivery_date | date |
+-----------------------------+---------+
delivery_id 是该表中具有唯一值的列。
该表保存着顾客的食物配送信息,顾客在某个日期下了订单,并指定了一个期望的配送日期(和下单日期相同或者在那之后)。
如果顾客期望的配送日期和下单日期相同,则该订单称为 「即时订单」,否则称为「计划订单」。
「首次订单」是顾客最早创建的订单。我们保证一个顾客只会有一个「首次订单」。
编写解决方案以获取即时订单在所有用户的首次订单中的比例。保留两位小数。
答案
select round (
sum(order_date = customer_pref_delivery_date) * 100 /
count(*),
2
) as immediate_percentage
from Delivery
where (customer_id, order_date) in (
select customer_id, min(order_date)
from delivery
group by customer_id
)
1179. 重新格式化部门表
题目
表 Department
:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| revenue | int |
| month | varchar |
+---------------+---------+
在 SQL 中,(id, month) 是表的联合主键。
这个表格有关于每个部门每月收入的信息。
月份(month)可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]。
重新格式化表格,使得 每个月 都有一个部门 id 列和一个收入列。
答案
select id,
sum(case month when 'Jan' then revenue end) as Jan_Revenue,
sum(case month when 'Feb' then revenue end) as Feb_Revenue,
sum(case month when 'Mar' then revenue end) as Mar_Revenue,
sum(case month when 'Apr' then revenue end) as Apr_Revenue,
sum(case month when 'May' then revenue end) as May_Revenue,
sum(case month when 'Jun' then revenue end) as Jun_Revenue,
sum(case month when 'Jul' then revenue end) as Jul_Revenue,
sum(case month when 'Aug' then revenue end) as Aug_Revenue,
sum(case month when 'Sep' then revenue end) as Sep_Revenue,
sum(case month when 'Oct' then revenue end) as Oct_Revenue,
sum(case month when 'Nov' then revenue end) as Nov_Revenue,
sum(case month when 'Dec' then revenue end) as Dec_Revenue
from Department
group by id;
1193. 每月交易 I
题目
表:Transactions
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| country | varchar |
| state | enum |
| amount | int |
| trans_date | date |
+---------------+---------+
id 是这个表的主键。
该表包含有关传入事务的信息。
state 列类型为 ["approved", "declined"] 之一。
编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额
答案
select Date_format(trans_date,'%Y-%m') as month,
country,
count(*) as trans_count,
count(if(state = 'approved',1,null)) as approved_count,
sum(amount) as trans_total_amount,
sum(if(state='approved',amount,0)) as approved_total_amount
from Transactions
group by month,country;