7、8.计算函数

7-1.1141.查询近30天活跃用户数

 i.使用DATEDIFF

   COUNT里使用的是user_id而不是session_id，因为要查询的是用户数，虽然说每个 session_id 只属于一个用户，但是每个用户可能拥有多个session_id 

   DATEDIFF除了限制<30,还要限制>=0不然会查询到07-27之后的数据

# Write your MySQL query statement below
SELECT DISTINCT
   activity_date day,COUNT(DISTINCT user_id) active_users
FROM
   Activity
WHERE
   DATEDIFF('2019-07-27',activity_date) < 30 AND DATEDIFF('2019-07-27',activity_date) >= 0
GROUP BY activity_date;

ii.使用between and

# Write your MySQL query statement below
SELECT DISTINCT
   activity_date day,COUNT(DISTINCT user_id) active_users
FROM
   Activity
WHERE
   activity_date BETWEEN '2019-06-28' AND '2019-07-27'
GROUP BY activity_date;

 

7-2.1693.每天的领导和合伙人

# Write your MySQL query statement below
SELECT
    date_id,make_name,
    COUNT(DISTINCT lead_id) unique_leads,
    COUNT(DISTINCT partner_id) unique_partners
FROM
   DailySales
GROUP BY make_name,date_id;

 

7-3.1729.求关注者的数量

# Write your MySQL query statement below
SELECT
   user_id,COUNT(DISTINCT follower_id) followers_count
FROM
   Followers
GROUP BY user_id;

 

8-1.586.订单最多的客户

# Write your MySQL query statement below
SELECT
   customer_number
FROM
   Orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC
LIMIT 1;
   

进阶：最多订单的客户不止一个（HAVING辅助）

i.排序+LIMIT

# Write your MySQL query statement below
SELECT
   customer_number
FROM
   Orders
GROUP BY customer_number
HAVING COUNT(*) = 
     (SELECT COUNT(*) 
      FROM Orders 
      GROUP BY customer_number 
      ORDER BY COUNT(*) DESC 
      LIMIT 1);
   

ii.直接>=ALL

# Write your MySQL query statement below
SELECT
   customer_number
FROM
   Orders
GROUP BY customer_number
HAVING COUNT(*) >= ALL 
     (SELECT COUNT(*) 
      FROM Orders 
      GROUP BY customer_number);
   

 

8-2.511.游戏玩法分析I

MIN函数也可用于 日期

# Write your MySQL query statement below
SELECT
   player_id,MIN(event_date) first_login
FROM
   Activity
GROUP BY player_id; 
 

 

8-3.1890.2020年最后一次登录

year(time_stamp) <==>  time_stamp between '2020-01-01'and'2021-01-01'

'2020-01-01'and'2021-01-01'  不能是 '2020-01-01'and'2020-12-31'，因为2020-12-31默认截止到2020-12-31的0点

# Write your MySQL query statement below
SELECT
   user_id,MAX(time_stamp) last_stamp
FROM
   Logins
WHERE year(time_stamp) = '2020' //time_stamp between '2020-01-01'and'2021-01-01'
GROUP BY user_id;

 

8-4.1741.查找每个员工花费的总时间

分组统计，SUM函数

# Write your MySQL query statement below
SELECT 
    event_day day,emp_id,SUM(out_time - in_time) total_time
FROM
    Employees
GROUP BY event_day,emp_id;