53. 文章浏览
需求一:找出所有浏览过自己文章的作者,结果按照 id 升序排列。
展示效果:
id |
---|
4 |
7 |
Create table If Not Exists 53_Views (article_id int, author_id int, viewer_id int, view_date date);
Truncate table 53_Views;
insert into 53_Views (article_id, author_id, viewer_id, view_date) values (1, 3, 5, '2019-08-01');
insert into 53_Views (article_id, author_id, viewer_id, view_date) values (3, 4, 5, '2019-08-01');
insert into 53_Views (article_id, author_id, viewer_id, view_date) values (1, 3, 6, '2019-08-02');
insert into 53_Views (article_id, author_id, viewer_id, view_date) values (2, 7, 7, '2019-08-01');
insert into 53_Views (article_id, author_id, viewer_id, view_date) values (2, 7, 6, '2019-08-02');
insert into 53_Views (article_id, author_id, viewer_id, view_date) values (4, 7, 1, '2019-07-22');
insert into 53_Views (article_id, author_id, viewer_id, view_date) values (3, 4, 4, '2019-07-21');
insert into 53_Views (article_id, author_id, viewer_id, view_date) values (3, 4, 4, '2019-07-21');
最终SQL:
select
distinct viewer_id as id
from
53_Views
where
viewer_id = author_id
order by
viewer_id;
需求二:找出在同一天阅读至少两篇文章的人,结果按照 id 升序排序。
展示效果:
project_id |
---|
5 |
6 |
最终SQL:
SELECT
DISTINCT viewer_id as id
FROM
53_views
GROUP BY
viewer_id,view_date
HAVING
COUNT(DISTINCT article_id)>=2
ORDER BY
viewer_id;
54. Market Analysis
需求一:Write an SQL query to find for each user, the join date and the number of orders they made as a buyer in 2019.
展示效果:
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 |
Create table If Not Exists 54_Users (user_id int, join_date date, favorite_brand varchar(10));
create table if not exists 54_Orders (order_id int, order_date date, item_id int, buyer_id int, seller_id int);
create table if not exists 54_Items (item_id int, item_brand varchar(10));
Truncate table 54_Users;
insert into 54_Users (user_id, join_date, favorite_brand) values (1, '2018-01-01', 'Lenovo');
insert into 54_Users (user_id, join_date, favorite_brand) values (2, '2018-02-09', 'Samsung');
insert into 54_Users (user_id, join_date, favorite_brand) values (3, '2018-01-19', 'LG');
insert into 54_Users (user_id, join_date, favorite_brand) values (4, '2018-05-21', 'HP');
Truncate table 54_Orders;
insert into 54_Orders (order_id, order_date, item_id, buyer_id, seller_id) values (1, '2019-08-01', 4, 1, 2);
insert into 54_Orders (order_id, order_date, item_id, buyer_id, seller_id) values (2, '2018-08-02', 2, 1, 3);
insert into 54_Orders (order_id, order_date, item_id, buyer_id, seller_id) values (3, '2019-08-03', 3, 2, 3);
insert into 54_Orders (order_id, order_date, item_id, buyer_id, seller_id) values (4, '2018-08-04', 1, 4, 2);
insert into 54_Orders (order_id, order_date, item_id, buyer_id, seller_id) values (5, '2018-08-04', 1, 3, 4);
insert into 54_Orders (order_id, order_date, item_id, buyer_id, seller_id) values (6, '2019-08-05', 2, 2, 4);
Truncate table 54_Items;
insert into 54_Items (item_id, item_brand) values (1, 'Samsung');
insert into 54_Items (item_id, item_brand) values (2, 'Lenovo');
insert into 54_Items (item_id, item_brand) values (3, 'LG');
insert into 54_Items (item_id, item_brand) values (4, 'HP');
最终SQL:
SELECT
user_id AS buyer_id,
join_date,
IFNULL(COUNT(buyer_Id), 0) AS orders_in_2019
FROM
54_Users u
LEFT JOIN
54_Orders o
ON
U.user_id = o.buyer_id AND
order_date >= '2019-01-01'
GROUP BY
user_id
ORDER BY
user_id;
需求二:Write an SQL query to find for each user, whether the brand of the second item (by date) they sold is their favorite brand. If a user sold less than two items, report the answer for that user as no.
展示效果:
product_id | 2nd_item_fav_brand |
---|---|
1 | no |
2 | no |
3 | yes |
4 | no |
最终SQL:
select
user_id as seller_id,
case
when t3.item_brand is null then 'no'
when t3.item_brand = u.favorite_brand then 'yes'
else 'no'
end as 2nd_item_fav_brand
from
54_Users u
left join
(select
order_id,
order_date,
t2.item_id,
buyer_id,
seller_id,
i.item_brand as item_brand
from
(select
order_id,
order_date,
item_id,
buyer_id,
seller_id,
cast(if(@prev = seller_id,@rank := @rank + 1,@rank := 1) as unsigned) as rank,
@prev := seller_id as prev
from
(select
order_id,
order_date,
item_id,
buyer_id,
seller_id
from
54_Orders
group by
seller_id,
order_date
) as t1,
(select
@rank := 0,
@prev := null
) as init) t2,
54_Items i
where
rank = 2 and
t2.item_id = i.item_id
) as t3
on
u.user_id = t3.seller_id;
55. Product Price at a Given Date
需求一:Write an SQL query to find the prices of all products on 2019-08-16. Assume the price of all products before any change is 10.
展示效果:
project_id | price |
---|---|
2 | 50 |
1 | 35 |
3 | 10 |
Create table If Not Exists 55_Products (product_id int, new_price int, change_date date);
Truncate table 55_Products;
insert into 55_Products (product_id, new_price, change_date) values (1, 20, '2019-08-14');
insert into 55_Products (product_id, new_price, change_date) values (2, 50, '2019-08-14');
insert into 55_Products (product_id, new_price, change_date) values (1, 30, '2019-08-15');
insert into 55_Products (product_id, new_price, change_date) values (1, 35, '2019-08-16');
insert into 55_Products (product_id, new_price, change_date) values (2, 65, '2019-08-17');
insert into 55_Products (product_id, new_price, change_date) values (3, 20, '2019-08-18');
最终SQL:
SELECT
*
FROM
(SELECT
product_id,
new_price AS price
FROM
55_Products
WHERE (product_id, change_date) IN (
SELECT
product_id,
MAX(change_date)
FROM
55_Products
WHERE
change_date <= '2019-08-16'
GROUP BY
product_id
)
UNION
SELECT
DISTINCT product_id, 10 AS price
FROM
55_Products
WHERE
product_id NOT IN (SELECT
product_id
FROM
55_Products
WHERE change_date <= '2019-08-16'
)
) tmp
ORDER BY
price DESC;
56. Immediate Food Delivery
需求一:查询语句获取即时订单所占的百分比, 保留两位小数。
展示效果:
immediate_percentage |
---|
42.86 |
Create table If Not Exists 56_Delivery (delivery_id int, customer_id int, order_date date, customer_pref_delivery_date date);
Truncate table 56_Delivery;
insert into 56_Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values (1, 1, '2019-08-01', '2019-08-02');
insert into 56_Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values (2, 5, '2019-08-02', '2019-08-02');
insert into 56_Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values (3, 1, '2019-08-11', '2019-08-11');
insert into 56_Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values (4, 3, '2019-08-24', '2019-08-26');
insert into 56_Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values (5, 4, '2019-08-21', '2019-08-22');
insert into 56_Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values (6, 2, '2019-08-11', '2019-08-13');
insert into 56_Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values (7, 4, '2019-08-09', '2019-08-09');
最终SQL:
SELECT ROUND(
(SELECT
COUNT(delivery_id)
FROM
56_Delivery
WHERE
order_date = customer_pref_delivery_date
) * 100 / COUNT(delivery_id) , 2) AS immediate_percentage
FROM
56_Delivery;
需求二:查询语句获取即时订单在所有用户的首次订单中的比例。保留两位小数。
展示效果:
immediate_percentage |
---|
40.00 |
最终SQL:
select
round(
count(case when d.order_date = d.customer_pref_delivery_date then 1 end)
*
100/count(*), 2) as immediate_percentage
from
56_Delivery d,
(select
delivery_id,
customer_id,
min(order_date) as order_date
from
56_Delivery
group by
customer_id
) as t
where
d.customer_id = t.customer_id
and d.order_date = t.order_date;
57. 重新格式化部门表
需求一:编写一个 SQL 查询来重新格式化表,使得新的表中有一个部门 id 列和一些对应 每个月 的收入(revenue)列。
展示效果:
id | Jan_Revenue | Feb_Revenue | Mar_Revenue | … | Dec_Revenue |
---|---|---|---|---|---|
1 | 8000 | 7000 | 6000 | … | null |
2 | 9000 | null | null | … | null |
3 | null | 10000 | null | … | null |
Create table If Not Exists 57_Department (id int, revenue int, month varchar(5));
Truncate table 57_Department;
insert into 57_Department (id, revenue, month) values (1, 8000, 'Jan');
insert into 57_Department (id, revenue, month) values (2, 9000, 'Jan');
insert into 57_Department (id, revenue, month) values (3, 10000, 'Feb');
insert into 57_Department (id, revenue, month) values (1, 7000, 'Feb');
insert into 57_Department (id, revenue, month) values (1, 6000, 'Mar');
最终SQL:
SELECT
DISTINCT id AS "id",
SUM(IF (month = "Jan", revenue, null)) AS "Jan_Revenue",
SUM(IF (month = "Feb", revenue, null)) AS "Feb_Revenue",
SUM(IF (month = "Mar", revenue, null)) AS "Mar_Revenue",
SUM(IF (month = "Apr", revenue, null)) AS "Apr_Revenue",
SUM(IF (month = "May", revenue, null)) AS "May_Revenue",
SUM(IF (month = "Jun", revenue, null)) AS "Jun_Revenue",
SUM(IF (month = "Jul", revenue, null)) AS "Jul_Revenue",
SUM(IF (month = "Aug", revenue, null)) AS "Aug_Revenue",
SUM(IF (month = "Sep", revenue, null)) AS "Sep_Revenue",
SUM(IF (month = "Oct", revenue, null)) AS "Oct_Revenue",
SUM(IF (month = "Nov", revenue, null)) AS "Nov_Revenue",
SUM(IF (month = "Dec", revenue, null)) AS "Dec_Revenue"
FROM
57_Department
GROUP BY id;
58. 每月交易
需求一:查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。
展示效果:
month | country | trans_count | approved_count | trans_total_amount | approved_total_amount |
---|---|---|---|---|---|
2018-12 | US | 2 | 1 | 3000 | 1000 |
2019-01 | US | 1 | 1 | 2000 | 2000 |
2019-01 | DE | 1 | 1 | 2000 | 2000 |
2019-05 | US | 2 | 1 | 3000 | 1000 |
2019-06 | US | 3 | 2 | 12000 | 8000 |
create table if not exists 58_Transactions (id int, country varchar(4), state enum('approved', 'declined'), amount int, trans_date date);
create table if not exists Chargebacks (trans_id int, trans_date date);
truncate table 58_Transactions;
insert into 58_Transactions (id, country, state, amount, trans_date) values (101, 'US', 'approved', 1000, '2018-12-18');
insert into 58_Transactions (id, country, state, amount, trans_date) values (102, 'US', 'declined', 2000, '2018-12-19');
insert into 58_Transactions (id, country, state, amount, trans_date) values (103, 'US', 'approved', 2000, '2019-01-01');
insert into 58_Transactions (id, country, state, amount, trans_date) values (104, 'DE', 'approved', 2000, '2019-01-07');
insert into 58_Transactions (id, country, state, amount, trans_date) values (105, 'US', 'approved', 1000, '2019-05-18');
insert into 58_Transactions (id, country, state, amount, trans_date) values (106, 'US', 'declined', 2000, '2019-05-19');
insert into 58_Transactions (id, country, state, amount, trans_date) values (107, 'US', 'approved', 3000, '2019-06-10');
insert into 58_Transactions (id, country, state, amount, trans_date) values (108, 'US', 'declined', 4000, '2019-06-13');
insert into 58_Transactions (id, country, state, amount, trans_date) values (109, 'US', 'approved', 5000, '2019-06-15');
truncate table 58_Chargebacks;
insert into 58_Chargebacks (trans_id, trans_date) values (102, '2019-05-29');
insert into 58_Chargebacks (trans_id, trans_date) values (101, '2019-06-30');
insert into 58_Chargebacks (trans_id, trans_date) values (105, '2019-09-18');
最终SQL:
select
date_format(trans_date,'%Y-%m') as month,
country,
count(*) as trans_count,
sum(if(state='approved',1,0)) as approved_count,
sum(amount) as trans_total_amount,
sum(if(state='approved',amount,0)) as approved_total_amount
from
58_Transactions t
group by
date_format(trans_date,'%Y-%m'),
country;
需求二:编写一个 SQL 查询,以查找每个月和每个国家/地区的已批准交易的数量及其总金额、退单的数量及其总金额。
展示效果:
month | country | approved_count | approved_amount | chargeback_count | chargeback_amount |
---|---|---|---|---|---|
2018-12 | US | 1 | 1000 | 0 | 0 |
2019-01 | DE | 1 | 2000 | 0 | 0 |
2019-01 | US | 1 | 2000 | 0 | 0 |
2019-05 | US | 1 | 1000 | 1 | 2000 |
2019-06 | US | 2 | 8000 | 1 | 1000 |
2019-09 | US | 0 | 0 | 1 | 1000 |
最终SQL:
SELECT
month as MONTH,
country as COUNTRY,
SUM(IF(type = 'approved', 1, 0)) AS APPROVED_COUNT,
SUM(IF(type = 'approved', amount, 0)) AS APPROVED_AMOUNT,
SUM(IF(type = 'chargeback', 1, 0)) AS CHARGEBACK_COUNT,
SUM(IF(type = 'chargeback', amount, 0)) AS CHARGEBACK_AMOUNT
FROM
(SELECT
date_format(t.trans_date,'%Y-%m') AS month,
t.country,
amount,
'approved' AS type
FROM
58_Transactions AS t
WHERE
state = 'approved'
UNION ALL
SELECT
date_format(c.trans_date,'%Y-%m') AS month,
t.country,
amount,
'chargeback' AS type
FROM
58_Transactions AS t
INNER JOIN
58_Chargebacks AS c
ON t.id = c.trans_id
) AS tt
GROUP BY
tt.month,
tt.country;
59. 锦标赛优胜者
需求一:编写一个 SQL 查询来查找每组中的获胜者。每组的获胜者是在组内得分最高的选手。如果平局,得分最低的选手获胜。
展示效果:
group_id | player_id |
---|---|
1 | 15 |
2 | 35 |
3 | 40 |
Create table If Not Exists 59_Players (player_id int, group_id int);
Create table If Not Exists 59_Matches (match_id int, first_player int, second_player int, first_score int, second_score int);
Truncate table 59_Players;
insert into 59_Players (player_id, group_id) values (10, 2);
insert into 59_Players (player_id, group_id) values (15, 1);
insert into 59_Players (player_id, group_id) values (20, 3);
insert into 59_Players (player_id, group_id) values (25, 1);
insert into 59_Players (player_id, group_id) values (30, 1);
insert into 59_Players (player_id, group_id) values (35, 2);
insert into 59_Players (player_id, group_id) values (40, 3);
insert into 59_Players (player_id, group_id) values (45, 1);
insert into 59_Players (player_id, group_id) values (50, 2);
Truncate table 59_Matches;
insert into 59_Matches (match_id, first_player, second_player, first_score, second_score) values (1, 15, 45, 3, 0);
insert into 59_Matches (match_id, first_player, second_player, first_score, second_score) values (2, 30, 25, 1, 2);
insert into 59_Matches (match_id, first_player, second_player, first_score, second_score) values (3, 30, 15, 2, 0);
insert into 59_Matches (match_id, first_player, second_player, first_score, second_score) values (4, 40, 20, 5, 2);
insert into 59_Matches (match_id, first_player, second_player, first_score, second_score) values (5, 35, 50, 1, 1);
最终SQL:
select
group_id,
player_id
from
(select
group_id,
player_id,
sum(
case
when player_id = first_player then first_score
when player_id = second_player then second_score
end
) as totalScores
from
59_Players p,
59_Matches m
where
p.player_id = m.first_player or
p.player_id = m.second_player
group by
group_id,
player_id
order by
group_id,
totalScores desc,
player_id
) as temp
group by
group_id
order by
group_id,
totalScores desc,
player_id;
60. Last Person to Fit in the Elevator
需求:查找最后一个能进入电梯且不超过重量限制的 person_name
。题目确保队列中第一位的人可以进入电梯 。
展示效果:
person_name |
---|
Thomas Jefferson |
Create table If Not Exists 60_Queue (person_id int, person_name varchar(30), weight int, turn int);
Truncate table 60_Queue;
insert into 60_Queue (person_id, person_name, weight, turn) values (5, 'George Washington', 250, 1);
insert into 60_Queue (person_id, person_name, weight, turn) values (4, 'Thomas Jefferson', 175, 5);
insert into 60_Queue (person_id, person_name, weight, turn) values (3, 'John Adams', 350, 2);
insert into 60_Queue (person_id, person_name, weight, turn) values (6, 'Thomas Jefferson', 400, 3);
insert into 60_Queue (person_id, person_name, weight, turn) values (1, 'James Elephant', 500, 6);
insert into 60_Queue (person_id, person_name, weight, turn) values (2, 'Will Johnliams', 200, 4);
最终SQL:
select
person_name
from
60_Queue q1
where
(select
sum(weight)
from
60_Queue q
where turn <= q1.turn) <= 1000
order by
turn desc
limit 1;
61. Queries Quality and Percentage
需求:编写一组 SQL 来查找每次查询的名称
(query_name
)、质量
(quality
) 和 劣质查询百分比
(poor_query_percentage
)。
质量
(quality
) 和劣质查询百分比
(poor_query_percentage
) 都应四舍五入到小数点后两位。
展示效果:
query_name | quality | poor_query_percentage |
---|---|---|
Dog | 2.50 | 33.33 |
Cat | 0.66 | 33.33 |
Create table If Not Exists 61_Queries (query_name varchar(30), result varchar(50), position int, rating int);
Truncate table 61_Queries;
insert into 61_Queries (query_name, result, position, rating) values ('Dog', 'Golden Retriever', 1, 5);
insert into 61_Queries (query_name, result, position, rating) values ('Dog', 'German Shepherd', 2, 5);
insert into 61_Queries (query_name, result, position, rating) values ('Dog', 'Mule', '200', 1);
insert into 61_Queries (query_name, result, position, rating) values ('Cat', 'Shirazi', 5, 2);
insert into 61_Queries (query_name, result, position, rating) values ('Cat', 'Siamese', 3, 3);
insert into 61_Queries (query_name, result, position, rating) values ('Cat', 'Sphynx', 7, 4);
最终SQL:
select
query_name,
round(avg(rating/position), 2) as quality ,
round((count(if(rating<3, True, null)) / count(query_name)) *100 , 2) as poor_query_percentage
from
61_Queries
group by
query_name
order by query_name desc;
62. Team Scores in Football Tournament
需求一:写出一条SQL语句以查询每个队的 team_id,team_name 和 num_points。结果根据 num_points 降序排序,如果有两队积分相同,那么这两队按 team_id 升序排序。
展示效果:
team_id | team_name | num_points |
---|---|---|
10 | Leetcode FC | 7 |
20 | NewYork FC | 3 |
50 | Toronto FC | 3 |
30 | Atlanta FC | 1 |
40 | Chicago FC | 0 |
Create table If Not Exists 62_Teams (team_id int, team_name varchar(30));
Create table If Not Exists 62_Matches (match_id int, host_team int, guest_team int, host_goals int, guest_goals int);
Truncate table 62_Teams;
insert into 62_Teams (team_id, team_name) values (10, 'Leetcode FC');
insert into 62_Teams (team_id, team_name) values (20, 'NewYork FC');
insert into 62_Teams (team_id, team_name) values (30, 'Atlanta FC');
insert into 62_Teams (team_id, team_name) values (40, 'Chicago FC');
insert into 62_Teams (team_id, team_name) values (50, 'Toronto FC');
Truncate table 62_Matches;
insert into 62_Matches (match_id, host_team, guest_team, host_goals, guest_goals) values (1, 10, 20, 30, 0);
insert into 62_Matches (match_id, host_team, guest_team, host_goals, guest_goals) values (2, 30, 10, 2, 2);
insert into 62_Matches (match_id, host_team, guest_team, host_goals, guest_goals) values (3, 10, 50, 5, 1);
insert into 62_Matches (match_id, host_team, guest_team, host_goals, guest_goals) values (4, 20, 30, 1, 0);
insert into 62_Matches (match_id, host_team, guest_team, host_goals, guest_goals) values (5, 50, 30, 1, 0);
最终SQL:
SELECT
*
FROM
(SELECT
a.team_id,
MAX(team_name) AS team_name,
SUM(
CASE
WHEN a.team_id = b.host_team THEN
CASE
WHEN b.host_goals > b.guest_goals THEN 3
WHEN b.host_goals = b.guest_goals THEN 1
ELSE 0
END
WHEN a.team_id = b.guest_team THEN
CASE
WHEN b.host_goals < b.guest_goals THEN 3
WHEN b.host_goals = b.guest_goals THEN 1
ELSE 0
END
ELSE 0
END
) AS num_points
FROM
62_Teams a
LEFT JOIN
62_Matches b
ON
a.team_id = b.host_team OR
a.team_id = b.guest_team
GROUP BY a.team_id
) a
ORDER BY
a.num_points DESC,
a.team_id;
63. 报告系统状态的连续日期
需求:系统 每天 运行一个任务。每个任务都独立于先前的任务。任务的状态可以是失败或是成功。编写一个 SQL 查询 2019-01-01 到 2019-12-31 期间任务连续同状态 period_state 的起止日期(start_date 和 end_date)。即如果任务失败了,就是失败状态的起止日期,如果任务成功了,就是成功状态的起止日期。最后结果按照起始日期 start_date 排序。
展示效果:
period_state | start date | end date |
---|---|---|
present | 2019-01-01 | 2019-01-03 |
missing | 2019-01-04 | 2019-01-05 |
present | 2019-01-06 | 2019-01-06 |
Create table If Not Exists 63_Failed (fail_date date);
Create table If Not Exists 63_Succeeded (success_date date);
Truncate table 63_Failed;
insert into 63_Failed (fail_date) values ('2018-12-28');
insert into 63_Failed (fail_date) values ('2018-12-29');
insert into 63_Failed (fail_date) values ('2019-01-04');
insert into 63_Failed (fail_date) values ('2019-01-05');
Truncate table 63_Succeeded;
insert into 63_Succeeded (success_date) values ('2018-12-30');
insert into 63_Succeeded (success_date) values ('2018-12-31');
insert into 63_Succeeded (success_date) values ('2019-01-01');
insert into 63_Succeeded (success_date) values ('2019-01-02');
insert into 63_Succeeded (success_date) values ('2019-01-03');
insert into 63_Succeeded (success_date) values ('2019-01-06');
最终SQL:
select
if(str=1,'succeeded','failed') as period_state ,
min(date) as start_date,
max(date) as end_date
from
(select
@diff := @diff+ if(num = 1 , 1,0) as diff,
date,
str
from
(select
case
when @str = str and date_add(@pre,interval 1 day) = date then @num := @num +1
when @str:=str then @num := 1
else @num := 1
end as num,
@pre := date,
date,
str
from
(select
fail_date as date ,
0 as 'str'
from
63_Failed
union
select
success_date,
1
from
63_Succeeded
) s,
(select @pre:=null,@num:=0,@str := null) s1
where
date between '2019-01-01' and '2019-12-31'
order by
date
) s,
(select @diff:=0) s1
) ys
group by diff, str;
64. 每个帖子的评论数
需求一:编写 SQL 语句以查找每个帖子的评论数。结果表应包含帖子的 post_id 和对应的评论数 number_of_comments 并且按 post_id 升序排列。Submissions 可能包含重复的评论。您应该计算每个帖子的唯一评论数。Submissions 可能包含重复的帖子。您应该将它们视为一个帖子。
展示效果:
post_id | number_of_comments |
---|---|
1 | 3 |
2 | 2 |
12 | 0 |
Create table If Not Exists 64_Submissions (sub_id int, parent_id int);
Truncate table 64_Submissions;
insert into 64_Submissions (sub_id, parent_id) values (1, null);
insert into 64_Submissions (sub_id, parent_id) values (2, null);
insert into 64_Submissions (sub_id, parent_id) values (1, null);
insert into 64_Submissions (sub_id, parent_id) values (12, null);
insert into 64_Submissions (sub_id, parent_id) values (3, 1);
insert into 64_Submissions (sub_id, parent_id) values (5, 2);
insert into 64_Submissions (sub_id, parent_id) values (3, 1);
insert into 64_Submissions (sub_id, parent_id) values (4, 1);
insert into 64_Submissions (sub_id, parent_id) values (9, 1);
insert into 64_Submissions (sub_id, parent_id) values (10, 2);
insert into 64_Submissions (sub_id, parent_id) values (6, 7);
最终SQL:
SELECT
post_id,
COUNT( DISTINCT S2.sub_id ) AS number_of_comments
FROM
(SELECT
DISTINCT sub_id AS post_id
FROM
64_Submissions
WHERE
parent_id IS NULL
) S1
LEFT JOIN
64_Submissions S2
ON
S1.post_id = S2.parent_id
GROUP BY
S1.post_id;
65. Average Selling Price
需求一:Write an SQL query to find the average selling price for each product. average_price should be rounded to 2 decimal places.
The query result format is in the following example:
展示效果:
product_id | average_price |
---|---|
1 | 6.96 |
2 | 16.96 |
Create table If Not Exists 65_Prices (product_id int, start_date date, end_date date, price int);
Create table If Not Exists 65_UnitsSold (product_id int, purchase_date date, units int);
Truncate table 65_Prices;
insert into 65_Prices (product_id, start_date, end_date, price) values (1, '2019-02-17', '2019-02-28', 5);
insert into 65_Prices (product_id, start_date, end_date, price) values (1, '2019-03-01', '2019-03-22', 20);
insert into 65_Prices (product_id, start_date, end_date, price) values (2, '2019-02-01', '2019-02-20', 15);
insert into 65_Prices (product_id, start_date, end_date, price) values (2, '2019-02-21', '2019-03-31', 30);
Truncate table 65_UnitsSold;
insert into 65_UnitsSold (product_id, purchase_date, units) values (1, '2019-02-25', 100);
insert into 65_UnitsSold (product_id, purchase_date, units) values (1, '2019-03-01', 15);
insert into 65_UnitsSold (product_id, purchase_date, units) values (2, '2019-02-10', 200);
insert into 65_UnitsSold (product_id, purchase_date, units) values (2, '2019-03-22', 30);
最终SQL:
select
product_id,
round(sum(a)/sum(units),2) as average_price
from
(select
p.product_id as product_id,
price,units,
price * units as a
from
65_Prices p
left join
65_UnitsSold u
on
p.product_id=u.product_id and
purchase_date<=end_date and
purchase_date>=start_date
)t
group by
product_id;