A table has two fields, and id date, new and retained to 2018/10/4 day began to calculate users, such as 2018/10/4 new d1, d2 two id, it hasn't been in 2018/10/1-2018/10/4, 2018/10/05 new e1, e2 and e3 three id, he hasn't appeared, in 2018/10/01-2018/10/04 2018/10/06 new f1, f2 and f3 three fields, it hasn't been in 2018/10/01-2018/10/05, and these new users for 1, 3, 7, 30 days retention rates how to write things, have to in the hive to achieve

Retain retained new 1, 3, 7, 30 retained retained
2018/10/4 2 100% 50% * *
33% 2018/10/5 3 * * *
2018/10/6 3 * * * *


Date id
2018/10/1 a1
2018/10/1 a2
2018/10/1 a3
2018/10/1 a4
2018/10/1 a5
2018/10/1 b1
2018/10/1 b2
2018/10/2 c1
2018/10/2 c2
2018/10/3 b1
2018/10/3 b2
2018/10/3 a3
2018/10/4 d1
2018/10/4 d2
2018/10/4 a1
2018/10/4 a2
2018/10/5 e1
2018/10/5 e2
2018/10/5 e3
2018/10/5 d1
2018/10/5 d2
2018/10/5 e4
2018/10/6 e3
2018/10/6 f1
2018/10/6 f2
2018/10/6 f3
2018/10/6 d2
2018/10/6 d5
2018/10/6 e8
2018/10/7 a1
2018/10/7 a2
2018/10/7 d1