0%

LeetCode第1645题为例。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
WITH recursive months AS (
SELECT
1 AS month
UNION ALL
SELECT
month + 1
FROM
months
WHERE
month < 12
)
SELECT
m.month AS month,
ifnull(
round(
(
count(
DISTINCT(t.driver_id)
) / (
SELECT
count(driver_id)
FROM
drivers
WHERE
join_date < date_add(
'2020-1-1', INTERVAL m.month month
)
) * 100
),
2
),
0.00
) working_percentage
FROM
(
SELECT
r.ride_id,
r.requested_at requested_date,
ar.ride_id accepted_ride,
ar.driver_id
FROM
rides r
RIGHT JOIN acceptedrides ar ON r.ride_id = ar.ride_id
WHERE
r.requested_at < '2021-01-01'
AND r.requested_at > '2019-12-31'
ORDER BY
r.requested_at
) t
RIGHT JOIN months m ON m.month = month(t.requested_date)
GROUP BY
m.month;
Read more »

留存率

牛客网SQL68

1
2
3
4
5
select
round(count(distinct user_id)*1.0/(select count(distinct user_id) from login) ,3) p
from login
where (user_id,date)
in (select user_id,DATE_ADD(min(date),INTERVAL 1 DAY) from login group by user_id);

牛客网SQL70

1
2
3
4
5
6
7
8
9
10
11
12
13
select (select subdate(t2.login_date, 1)) as date,
round(t2.cnt/(select count(user_id) from login where date=(select subdate(t2.login_date, 1)) and (user_id, (select subdate(t2.login_date, 1))) in (select user_id, min(date) as day1 from login group by user_id) group by date), 3) as p from
(select t.login_date, count(t.2ndlogin_diff) cnt from
(select user_id, date as login_date, datediff(nth_value(date, 2) over(partition by user_id order by date),nth_value(date, 1) over(partition by user_id order by date)) as 2ndlogin_diff
from login order by date) t
where t.2ndlogin_diff=1
group by t.login_date
) t2
union
select date, 0.000 p from login where date not in (select min(date) as day1 from login group by user_id)
union
select l.date, 0.000 p from login l where (select date_add(l.date, interval 1 day)) not in (select date from login)
order by date;

OR

Read more »

查看指定端口进程

sudo lsof -i:port

关闭指定端口进程

sudo kill -9 `sudo lsof -t -i:port`

查看本机IP

curl -4 icanhazip.com

Read more »