41-60 - 《SQL》 - 极客文档

41. 买下所有产品的用户
42. 合作过至少三次的演员和导演
43. 产品销售分析
44. 项目员工
45. 销售分析
46. 小众书籍
47. 每日新用户统计
48. 每位学生的最高成绩
49. 举报记录
50. 查询活跃业务
51. 用户购买平台
52. 查询近30天活跃用户
53. 文章浏览
54. 市场分析
55. 指定日期产品价格
56. 即时食物配送
57. 重新格式化部门表
58. 每月交易
59. 锦标赛优胜者
60. 最后一个能进入电梯的人

41. 买下所有产品的用户

需求：编写一个 SQL 查询，从 Customer 表中查询购买了 Product 表中所有产品的客户的 id。

展示效果：

customer_id
1
3

Create table If Not Exists 41_Customer (customer_id int, product_key int);
Create table 41_Product (product_key int);
Truncate table 41_Customer;
insert into 41_Customer (customer_id, product_key) values (1, 5);
insert into 41_Customer (customer_id, product_key) values (2, 6);
insert into 41_Customer (customer_id, product_key) values (3, 5);
insert into 41_Customer (customer_id, product_key) values (3, 6);
insert into 41_Customer (customer_id, product_key) values (1, 6);
Truncate table 41_Product;
insert into 41_Product (product_key) values (5);
insert into 41_Product (product_key) values (6);

最终SQL:

select
    customer_id
from 
    (select
          customer_id,
          count(distinct product_key) as num 
      from
          41_Customer
      group by
          customer_id) t
join 
    (select
          count(product_key) as num
     from 
          41_Product) m 
on t.num = m.num;

42. 合作过至少三次的演员和导演

需求：编写一个 SQL 查询，查询语句获取合作过至少三次的演员和导演的 id 对 (actor_id, director_id)

展示效果：

actor_id	director_id
1	1

Create table If Not Exists 42_ActorDirector (actor_id int, director_id int, timestamp int);
Truncate table 42_ActorDirector;
insert into 42_ActorDirector (actor_id, director_id, timestamp) values (1, 1, 0);
insert into 42_ActorDirector (actor_id, director_id, timestamp) values (1, 1, 1);
insert into 42_ActorDirector (actor_id, director_id, timestamp) values (1, 1, 2);
insert into 42_ActorDirector (actor_id, director_id, timestamp) values (1, 2, 3);
insert into 42_ActorDirector (actor_id, director_id, timestamp) values (1, 2, 4);
insert into 42_ActorDirector (actor_id, director_id, timestamp) values (2, 1, 5);
insert into 42_ActorDirector (actor_id, director_id, timestamp) values (2, 1, 6);

最终SQL:

select 
      actor_id,
      director_id
from 
      42_ActorDirector 
group by 
      actor_id,director_id 
having 
      count(*)>=3;

43. 产品销售分析

需求一：获取产品表 Product 中所有的 产品名称 product name 以及该产品在 Sales 表中相对应的 上市年份 year 和 价格 price。

展示效果：

product_name	year	price
Nokia	2008	5000
Nokia	2009	5000
Apple	2011	9000

Create table  If Not Exists 43_Sales (sale_id int, product_id int, year int, quantity int, price int);
Create table  If Not Exists 43_Product (product_id int, product_name varchar(10));
Truncate table 43_Sales;
insert into 43_Sales (sale_id, product_id, year, quantity, price) values (1, 100, 2008, 10, 5000);
insert into 43_Sales (sale_id, product_id, year, quantity, price) values (2, 100, 2009, 12, 5000);
insert into 43_Sales (sale_id, product_id, year, quantity, price) values (7, 200, 2011, 15, 9000);
Truncate table 43_Product;
insert into 43_Product (product_id, product_name) values (100, 'Nokia');
insert into 43_Product (product_id, product_name) values (200, 'Apple');
insert into 43_Product (product_id, product_name) values (300, 'Samsung');

最终SQL:

select 
      product_name,
      year,
      price
from 
      43_Sales 
inner join 
      43_Product
on
      43_Sales.product_id = 43_Product.product_id;

需求二：按产品 id（product_id ）来统计每个产品的销售总量。

展示效果：

product_id	total_quantity
100	22
200	15

最终SQL:

SELECT
    product_id, 
    SUM(quantity) as total_quantity
FROM
    43_Sales
GROUP BY
    product_id;

需求三：选出每个销售产品的第一年的产品 id、年份、数量和价格。

展示效果：

product_id	first_year	quantity	price
100	2008	10	5000
200	2011	15	9000

最终SQL:

select 
      product_id,
      year as first_year, 
      quantity,
      price
from 
      43_Sales
where 
     (product_id , year) in(
                            select
                                  product_id ,
                                  min(year)
                            from
                                  43_Sales
                            group by
                                  product_id
                            );

44. 项目员工

需求一：查询每一个项目中员工的平均工作年限，精确到小数点后两位。

展示效果：

project_id	average_years
1	2.00
2	2.50

Create table If Not Exists 44_Project (project_id int, employee_id int);
Create table If Not Exists 44_Employee (employee_id int, name varchar(10), experience_years int);
Truncate table 44_Project;
insert into 44_Project (project_id, employee_id) values (1, 1);
insert into 44_Project (project_id, employee_id) values (1, 2);
insert into 44_Project (project_id, employee_id) values (1, 3);
insert into 44_Project (project_id, employee_id) values (2, 1);
insert into 44_Project (project_id, employee_id) values (2, 4);
Truncate table 44_Employee;
insert into 44_Employee (employee_id, name, experience_years) values (1, 'Khaled', 3);
insert into 44_Employee (employee_id, name, experience_years) values (2, 'Ali', 2);
insert into 44_Employee (employee_id, name, experience_years) values (3, 'John', 1);
insert into 44_Employee (employee_id, name, experience_years) values (4, 'Doe', 2);

最终SQL:

select 
      project_id ,
      round(avg(experience_years),2) as average_years
from 
      44_Project p
left join
      44_Employee e
on 
      p.employee_id = e.employee_id
group by 
      project_id
order by
      project_id;

需求二：报告所有雇员最多的项目。

展示效果：

project_id
1

最终SQL:

SELECT 
      project_id
FROM 
      44_Project
GROUP BY 
      project_id
HAVING 
      COUNT(employee_id) = (SELECT
                                  MAX(count_employee_id)
                            FROM
                                (SELECT 
                                       project_id,
                                       COUNT(employee_id) AS count_employee_id
                                 FROM
                                       44_Project
                                 GROUP BY 
                                       project_id
                                ) As temp
                           );

需求三：报告在每一个项目中经验最丰富的雇员是谁。如果出现经验年数相同的情况，请报告所有具有最大经验年数的员工。

展示效果：

project_id	employee_id
1	1
2	1

最终SQL:

select 
      p.project_id,
      p.employee_id
from 
      44_Project p
join 
      44_Employee e
on 
      p.employee_id = e.employee_id
where 
     (p.project_id, e.experience_years) in (select
                                                  p.project_id,
                                                  max(e.experience_years)
                                            from
                                                  44_project p 
                                            join
                                                  44_employee e
                                            on 
                                                  p.employee_id = e.employee_id
                                            group by
                                                  p.project_id
                                           );

45. 销售分析

需求一：编写一个 SQL 查询，查询总销售额最高的销售者，如果有并列的，就都展示出来。

展示效果：

seller_id
1
3

Create table If Not Exists 45_Product (product_id int, product_name varchar(10), unit_price int);
Create table If Not Exists 45_Sales (seller_id int, product_id int,buyer_id int, sale_date date, quantity int, price int);
Truncate table 45_Product;
insert into 45_Product (product_id, product_name, unit_price) values (1, 'S8', 1000);
insert into 45_Product (product_id, product_name, unit_price) values (2, 'G4', 800);
insert into 45_Product (product_id, product_name, unit_price) values (3, 'iPhone', 1400);
Truncate table 45_Sales;
insert into 45_Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values (1, 1, 1,'2019-01-21', 2, 2000);
insert into 45_Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values (1, 2, 2,'2019-02-17', 1, 800);
insert into 45_Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values (2, 1, 3,'2019-06-02', 1, 800);
insert into 45_Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values (3, 3, 3,'2019-05-13', 2, 2800);

最终SQL:

select 
      seller_id 
from 
      45_Sales
group by 
      seller_id
having 
      sum(price) = (select
                          sum(price) as ye_ji
                    from  
                          45_Sales
                    group by
                          seller_id
                    order by 
                          ye_ji desc
                    limit 1
                   );

需求二：编写一个 SQL 查询，查询购买了 S8 手机却没有购买 iPhone 的买家。注意这里 S8 和 iPhone 是 Product 表中的产品。

展示效果：

buyer_id
1

最终SQL:

-- 方法一
select 
      distinct buyer_id
from 
      45_product p 
inner join 
      45_sales s
on 
      p.product_id=s.product_id
where 
      product_name='S8' and buyer_id not in (select
                                                    buyer_id
                                              from
                                                    45_product p
                                              inner join
                                                    45_sales s
                                              on
                                                    p.product_id=s.product_id
                                              where
                                                    product_name='iPhone'
                                              );
-- 方法二
select 
      s8 as buyer_id
from 
      (select 
             distinct buyer_id s8
       from
             45_product p 
       inner join 
             45_sales s
       on
             p.product_id=s.product_id
       where
             product_name='S8') t1
left join
      (select
             distinct buyer_id ip
       from
             45_product p
       inner join 
             45_sales s
       on
             p.product_id=s.product_id
       where
             product_name='iPhone'
      ) t2
on 
       s8=ip
where 
       ip is null;

需求三：编写一个 SQL 查询，报告2019年仅在春季才售出的产品。即在2019-01-01至2019-03-31（含）之间。

展示效果：

product_id	product_name
2	G4

最终SQL:

SELECT
    DISTINCT s.product_id,
    p.product_name
FROM
    45_Sales AS s
INNER JOIN
    45_Product AS p
ON
    s.product_id = p.product_id
WHERE
    s.product_id NOT IN(
        SELECT
             DISTINCT product_id
        FROM
             45_Sales
        WHERE
            sale_date NOT BETWEEN '2019-01-01' AND '2019-03-31');

46. 小众书籍

需求：筛选出过去一年中订单总量少于10本的书籍。注意：不考虑上架（available from）距今不满一个月的书籍。并且假设今天是2019-06-23 。

展示效果：

book_id	name
1	Kalila And Demna
2	28 Letters
5	The Hunger Games

Create table If Not Exists 46_Books (book_id int, name varchar(50), available_from date);
Create table If Not Exists 46_Orders (order_id int, book_id int, quantity int, dispatch_date date);
Truncate table 46_Books;
insert into 46_Books (book_id, name, available_from) values (1, 'Kalila And Demna', '2010-01-01');
insert into 46_Books (book_id, name, available_from) values (2, '28 Letters', '2012-05-12');
insert into 46_Books (book_id, name, available_from) values (3, 'The Hobbit', '2019-06-10');
insert into 46_Books (book_id, name, available_from) values (4, '13 Reasons Why', '2019-06-01');
insert into 46_Books (book_id, name, available_from) values (5, 'The Hunger Games', '2008-09-21');
Truncate table 46_Orders;
insert into 46_Orders (order_id, book_id, quantity, dispatch_date) values (1, 1, 2, '2018-07-26');
insert into 46_Orders (order_id, book_id, quantity, dispatch_date) values (2, 1, 1, '2018-11-05');
insert into 46_Orders (order_id, book_id, quantity, dispatch_date) values (3, 3, 8, '2019-06-11');
insert into 46_Orders (order_id, book_id, quantity, dispatch_date) values (4, 4, 6, '2019-06-05');
insert into 46_Orders (order_id, book_id, quantity, dispatch_date) values (5, 4, 5, '2019-06-20');
insert into 46_Orders (order_id, book_id, quantity, dispatch_date) values (6, 5, 9, '2009-02-02');
insert into 46_Orders (order_id, book_id, quantity, dispatch_date) values (7, 5, 8, '2010-04-13');

最终SQL:

select 
      t1.book_id,
      (select name from 46_books t3 where t1.book_id = t3.book_id) as name
from 
      (select
             *
       from
             46_books
       where
             available_from < date_sub('2019-06-23',interval 1 Month)
      ) t1 
left join 
      (select 
             *,
             case
                  when dispatch_date between '2018-06-23' and '2019-06-23' then quantity
                  else 0
              end num
       from 46_orders
      )  t2
on 
     t1.book_id=t2.book_id 
group by 
     t1.book_id 
having 
     sum(if(t2.num is null,0,t2.num))<10;

47. 每日新用户统计

需求一：编写一个 SQL 查询，以查询从今天起最多 90 天内，每个日期内首次登录的用户数。假设今天是 2019-06-30.

展示效果：

login_date	user_count
2019-05-01	1
2019-06-21	2

Create table If Not Exists 47_Traffic (user_id int, activity ENUM('login', 'logout', 'jobs', 'groups', 'homepage'), activity_date date);
Truncate table 47_Traffic;
insert into 47_Traffic (user_id, activity, activity_date) values (1, 'login', '2019-05-01');
insert into 47_Traffic (user_id, activity, activity_date) values (1, 'homepage', '2019-05-01');
insert into 47_Traffic (user_id, activity, activity_date) values (1, 'logout', '2019-05-01');
insert into 47_Traffic (user_id, activity, activity_date) values (2, 'login', '2019-06-21');
insert into 47_Traffic (user_id, activity, activity_date) values (2, 'logout', '2019-06-21');
insert into 47_Traffic (user_id, activity, activity_date) values (3, 'login', '2019-01-01');
insert into 47_Traffic (user_id, activity, activity_date) values (3, 'jobs', '2019-01-01');
insert into 47_Traffic (user_id, activity, activity_date) values (3, 'logout', '2019-01-01');
insert into 47_Traffic (user_id, activity, activity_date) values (4, 'login', '2019-06-21');
insert into 47_Traffic (user_id, activity, activity_date) values (4, 'groups', '2019-06-21');
insert into 47_Traffic (user_id, activity, activity_date) values (4, 'logout', '2019-06-21');
insert into 47_Traffic (user_id, activity, activity_date) values (5, 'login', '2019-03-01');
insert into 47_Traffic (user_id, activity, activity_date) values (5, 'logout', '2019-03-01');
insert into 47_Traffic (user_id, activity, activity_date) values (5, 'login', '2019-06-21');
insert into 47_Traffic (user_id, activity, activity_date) values (5, 'logout', '2019-06-21');

最终SQL:

select 
    minx as login_date,
    count(user_id) as user_count
from 
   (select 
          user_id,
          min(activity_date) as minx
    from 
          47_Traffic
    where
          activity='login'
    group by 
          user_id
    having 
          datediff('2019-06-30',minx)<=90
    )s
group by 
    minx;

48. 每位学生的最高成绩

需求：查询每位学生获得的最高成绩和它所对应的科目，若科目成绩并列，取 course_id 最小的一门。查询结果需按 student_id 增序进行排序。

展示效果：

student_id	course_id	grade
1	2	99
2	2	95
3	3	82

Create table If Not Exists 48_Enrollments (student_id int, course_id int, grade int);
Truncate table 48_Enrollments;
insert into 48_Enrollments (student_id, course_id, grade) values (2, 2, 95);
insert into 48_Enrollments (student_id, course_id, grade) values (2, 3, 95);
insert into 48_Enrollments (student_id, course_id, grade) values (1, 1, 90);
insert into 48_Enrollments (student_id, course_id, grade) values (1, 2, 99);
insert into 48_Enrollments (student_id, course_id, grade) values (3, 1, 80);
insert into 48_Enrollments (student_id, course_id, grade) values (3, 2, 75);
insert into 48_Enrollments (student_id, course_id, grade) values (3, 3, 82);

最终SQL:

select 
      e1.student_id,
      min(e1.course_id) as course_id,
      max(e1.grade) as grade
from 
      48_Enrollments e1
right join
     (select
            student_id,
            max(grade) as max1 
      from 
            48_Enrollments
      group by
            student_id 
     )e2
on
      e2.student_id=e1.student_id 
      and
      e2.max1 = e1.grade
group by 
      e1.student_id
order by 
      e1.student_id;

49. 举报记录

需求一：编写一条SQL，查询昨天不同举报类型的数量，假设今天是 2019-07-05。在action 列标记为report 被认为遭到举报，对应的产生举报原因。

展示效果：

report_reason	report_count
spam	1

Create table If Not Exists 49_Actions (user_id int, post_id int, action_date date, action ENUM('view', 'like', 'reaction', 'comment', 'report', 'share'), extra varchar(10));
Truncate table 49_Actions;
insert into 49_Actions (user_id, post_id, action_date, action, extra) values (1, 1, '2019-07-01', 'view', null);
insert into 49_Actions (user_id, post_id, action_date, action, extra) values (1, 1, '2019-07-01', 'like', null);
insert into 49_Actions (user_id, post_id, action_date, action, extra) values (1, 1, '2019-07-01', 'share', null);
insert into 49_Actions (user_id, post_id, action_date, action, extra) values (2, 4, '2019-07-04', 'view', null);
insert into 49_Actions (user_id, post_id, action_date, action, extra) values (2, 4, '2019-07-04', 'report', 'spam');
insert into 49_Actions (user_id, post_id, action_date, action, extra) values (3, 4, '2019-07-04', 'view', null);
insert into 49_Actions (user_id, post_id, action_date, action, extra) values (3, 4, '2019-07-04', 'report', 'spam');
insert into 49_Actions (user_id, post_id, action_date, action, extra) values (4, 3, '2019-07-02', 'view', null);
insert into 49_Actions (user_id, post_id, action_date, action, extra) values (4, 3, '2019-07-02', 'report', 'spam');
insert into 49_Actions (user_id, post_id, action_date, action, extra) values (5, 2, '2019-07-03', 'view', null);
insert into 49_Actions (user_id, post_id, action_date, action, extra) values (5, 2, '2019-07-03', 'report', 'racism');
insert into 49_Actions (user_id, post_id, action_date, action, extra) values (5, 5, '2019-07-03', 'view', null);
insert into 49_Actions (user_id, post_id, action_date, action, extra) values (5, 5, '2019-07-03', 'report', 'racism');

最终SQL:

select 
      extra report_reason,
      count(distinct post_id) report_count 
from 
      49_Actions 
where 
      datediff('2019-07-05', action_date) = 1
      and
      extra is not null
      and
      action = 'report' 
group by
      extra;

50. 查询活跃业务

需求：写一段 SQL 来查询所有活跃的业务。如果一个业务的某个事件类型的发生次数大于此事件类型在所有业务中的平均发生次数，并且该业务至少有两个这样的事件类型，那么该业务就可被看做是活跃业务。

展示效果：

business_id
1

reviews、 ads 和 page views 的总平均发生次数分别是 (7+3)/2=5, (11+7+6)/3=8, (3+12)/2=7.5。
id 为 1 的业务有 7 个 ‘reviews’ 事件（大于 5）和 11 个 ‘ads’ 事件（大于 8），所以它是活跃业务。

Create table If Not Exists 50_Events (business_id int, event_type varchar(10), occurences int);
Truncate table 50_Events;
insert into 50_Events (business_id, event_type, occurences) values (1, 'reviews', 7);
insert into 50_Events (business_id, event_type, occurences) values (3, 'reviews', 3);
insert into 50_Events (business_id, event_type, occurences) values (1, 'ads', 11);
insert into 50_Events (business_id, event_type, occurences) values (2, 'ads', 7);
insert into 50_Events (business_id, event_type, occurences) values (3, 'ads', 6);
insert into 50_Events (business_id, event_type, occurences) values (1, 'page views', 3);
insert into 50_Events (business_id, event_type, occurences) values (2, 'page views', 12);

最终SQL:

select 
    e2.business_id
from 
    50_events e2
join 
   (select 
          event_type,
          avg(occurences) as avg_occ
    from
          50_events
    group by 
          event_type) e1
on 
    e2.event_type = e1.event_type 
    and 
    e2.occurences > e1.avg_occ
group by
    e2.business_id
having 
    count(business_id) >=2;

51. 用户购买平台

需求一：写一段 SQL 来查找每天仅使用手机端用户、仅使用桌面端用户和同时使用桌面端和手机端的用户人数和总支出金额。

展示效果：

spend_date	platform	total_amount	total_users
2019-07-01	desktop	100	1
2019-07-01	mobile	100	1
2019-07-01	both	200	1
2019-07-02	desktop	100	1
2019-07-02	mobile	100	1
2019-07-02	both	0	0

Create table If Not Exists 51_Spending (user_id int, spend_date date, platform ENUM('desktop', 'mobile'), amount int);
Truncate table 51_Spending;
insert into 51_Spending (user_id, spend_date, platform, amount) values (1, '2019-07-01', 'mobile', 100);
insert into 51_Spending (user_id, spend_date, platform, amount) values (1, '2019-07-01', 'desktop', 100);
insert into 51_Spending (user_id, spend_date, platform, amount) values (2, '2019-07-01', 'mobile', 100);
insert into 51_Spending (user_id, spend_date, platform, amount) values (2, '2019-07-02', 'mobile', 100);
insert into 51_Spending (user_id, spend_date, platform, amount) values (3, '2019-07-01', 'desktop', 100);
insert into 51_Spending (user_id, spend_date, platform, amount) values (3, '2019-07-02', 'desktop', 100);

在 2019-07-01, 用户1 同时使用桌面端和手机端购买, 用户2 仅使用了手机端购买，而用户3 仅使用了桌面端购买。

在 2019-07-02, 用户2 仅使用了手机端购买, 用户3 仅使用了桌面端购买，且没有用户同时使用桌面端和手机端购买。

最终SQL:

select 
      temp1.spend_date,
      temp1.platform, 
      ifnull(temp3.total_amount, 0) total_amount, 
      ifnull(temp3.total_users,0) total_users
from
     (select
            distinct(spend_date),
            p.platform   
      from 
            51_Spending,
           (select
                  'desktop' as platform union
            select 
                  'mobile' as platform union
            select
                  'both' as platform
           ) as p 
       ) as temp1
left join 
      (select
             spend_date,
             platform,
             sum(amount) as total_amount,
             count(user_id) total_users
       from
            (select
                   spend_date,
                   user_id, 
                   case count(distinct platform)
                        when 1 then platform
                        when 2 then 'both'
                   end as  platform, 
                   sum(amount) as amount
             from 
                   51_Spending
             group by
                   spend_date,
                   user_id
            ) as temp2
      group by
             spend_date,
             platform
      ) as  temp3
on 
      temp1.platform = temp3.platform and
      temp1.spend_date = temp3.spend_date;

52. 查询近30天活跃用户

需求一：请写SQL查询出截至 2019-07-27（包含2019-07-27），近 30天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）。

展示效果：

day	active_users
2019-07-20	2
2019-07-21	2

Create table If Not Exists 52_Activity (user_id int, session_id int, activity_date date, activity_type ENUM('open_session', 'end_session', 'scroll_down', 'send_message'));
Truncate table 52_Activity;
insert into 52_Activity (user_id, session_id, activity_date, activity_type) values (1, 1, '2019-07-20', 'open_session');
insert into 52_Activity (user_id, session_id, activity_date, activity_type) values (1, 1, '2019-07-20', 'scroll_down');
insert into 52_Activity (user_id, session_id, activity_date, activity_type) values (1, 1, '2019-07-20', 'end_session');
insert into 52_Activity (user_id, session_id, activity_date, activity_type) values (2, 4, '2019-07-20', 'open_session');
insert into 52_Activity (user_id, session_id, activity_date, activity_type) values (2, 4, '2019-07-21', 'send_message');
insert into 52_Activity (user_id, session_id, activity_date, activity_type) values (2, 4, '2019-07-21', 'end_session');
insert into 52_Activity (user_id, session_id, activity_date, activity_type) values (3, 2, '2019-07-21', 'open_session');
insert into 52_Activity (user_id, session_id, activity_date, activity_type) values (3, 2, '2019-07-21', 'send_message');
insert into 52_Activity (user_id, session_id, activity_date, activity_type) values (3, 2, '2019-07-21', 'end_session');
insert into 52_Activity (user_id, session_id, activity_date, activity_type) values (4, 3, '2019-06-25', 'open_session');
insert into 52_Activity (user_id, session_id, activity_date, activity_type) values (4, 3, '2019-06-25', 'end_session');

最终SQL:

select 
      t.activity_date as day,
      count(distinct t.user_id) as active_users
from 
     (select 
            activity_date,
            user_id
      from
            52_Activity
      where 
            datediff('2019-07-27',activity_date) <30
            and
            datediff( '2019-07-27', activity_date) >=0
      group by
            user_id,
            activity_date
      having 
            count(activity_type)>0
     ) as t
group by 
      t.activity_date;

需求二：编写SQL查询以查找截至2019年7月27日（含）的30天内每个用户的平均会话数，四舍五入到小数点后两位。我们要为用户计算的会话是在该时间段内至少进行了一项活动的会话。

展示效果：

average_sessions_per_user
1.00

最终SQL:

SELECT 
      ROUND(IFNULL(AVG(count_session_id), 0), 2) AS average_sessions_per_user
FROM
     (SELECT
            COUNT(DISTINCT session_id) AS count_session_id
      FROM
            52_Activity
      WHERE 
            activity_date BETWEEN DATE_SUB("2019-07-27", INTERVAL 29 DAY) AND "2019-07-27"
      GROUP BY
            user_id
      ) AS temp;

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. 市场分析

需求一：请写出一条SQL语句以查询每个用户的注册日期和在 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;

需求二：写一个 SQL 查询确定每一个用户按日期顺序卖出的第二件商品的品牌是否是他们最喜爱的品牌。如果一个用户卖出少于两件商品，查询的结果是 no 。

展示效果：

seller_id	2nd_item_fav_brand
1	no
2	no
3	yes
4	no

最终SQL:

-- 方法一
select 
      user_id as seller_id,
      if (r2.item_brand is null || r2.item_brand != favorite_brand, "no", "yes") as 2nd_item_fav_brand
from 
      54_users
left join 
     (select
            r1.seller_id,
            i.item_brand 
      from
           (select 
                  @rk := if (@seller = a.seller_id, @rk + 1, 1) as `rank`,
                  @seller := a.seller_id as seller_id, 
                  a.item_id
            from 
                 (select 
                       seller_id,
                       item_id
                  from 
                       54_orders 
                  order by 
                       seller_id, order_date) a,
                 (select @seller := -1, @rk := 0) b
            ) r1
     join 
          54_items i
     on
          r1.item_id = i.item_id
     where
          r1.`rank` = 2) r2 
on
     user_id = r2.seller_id;
-- 方法二：
select
     user_id seller_id,
     if(favorite_brand = item_brand, 'yes', 'no') 2nd_item_fav_brand
from 
     54_users
left join
    (select
          o1.seller_id,
          item_brand
     from
          54_orders o1 
     join
          54_orders o2
     on
          o1.seller_id = o2.seller_id
     join
          54_items i
     on
          o1.item_id = i.item_id
     group by
          o1.order_id
     having 
          sum(o1.order_date > o2.order_date) = 1
     ) tmp
on user_id = seller_id;
-- 方法三：
select 
     u.seller_id,
     if(favorite_brand = item_brand, 'yes', 'no') as  2nd_item_fav_brand                          
from
    (select user_id as seller_id from 54_Users) u
left join
    (select 
           * 
     from
          (select
                o.order_date,
                o.seller_id,
                i.item_brand,
                u.favorite_brand,
                rank() over(partition by o.seller_id order by o.order_date) rnk
           from 
                54_Orders o 
           left join
                54_Users u
           on 
                o.seller_id = u.user_id
           left join
                54_Items i
           on 
                o.item_id = i.item_id
           ) t1
     where rnk = 2
     ) t2
on 
     u.seller_id = t2.seller_id

55. 指定日期产品价格

需求一：写一段 SQL来查找在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 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_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 58_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 查询来查找每组中的获胜者。每组的获胜者是在组内得分最高的选手。如果平局，player_id 最小的的选手获胜。

展示效果：

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,
      max(total_scores) max_scores
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 total_scores
     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,
          total_scores desc,
          player_id
    ) as temp
group by 
     group_id

order by 
     group_id,
     total_scores desc,
     player_id;

60. 最后一个能进入电梯的人

需求：查找最后一个能进入电梯且不超过重量限制的 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;