1141. 查询近30天活跃用户数

1141. 查询近30天活跃用户数

https://leetcode.cn/problems/user-activity-for-the-past-30-days-i/solutions/2329645/xin-shou-jie-ti-fen-xi-ti-mu-yi-bu-yi-bu-0iaj/

• 分析：

  • 要注意计数的时候，要进行去重。

  count(distinct ##)
  

  • 计算近30天
    • 用DATE_ADD函数减：DATE_ADD(‘2019-07-27’,INTERVAL -29 day)
    • 用datediff(date1, date2)，返回date1-date2的差，判断差值是否在范围里：DATEDIFF(CAST(“2019-07-27” AS DATE), activity_date) BETWEEN 0 AND 29

• 代码：

  • 第一种解法：

select activity_date as day,count(distinct user_id) as active_users
from Activity 
where activity_date BETWEEN DATE_ADD('2019-07-27',INTERVAL -29 day) and '2019-07-27'
group by activity_date

• 第二种解法（更快）：

SELECT activity_date AS day, COUNT(DISTINCT user_id) AS active_users
FROM Activity 
WHERE DATEDIFF(CAST("2019-07-27" AS DATE), activity_date) BETWEEN 0 AND 29
GROUP BY activity_date