stratascratch2-Acceptance Rate By Date

What is the overall friend acceptance rate by date? Your output should have the rate of acceptances by the date the request was sent. Order by the earliest date to latest. Assume that each friend request starts by a user sending (i.e., user_id_sender) a friend request to another user (i.e., user_id_receiver) that's logged in the table with action = 'sent'. If the request is accepted, the table logs action = 'accepted'. If the request is not accepted, no record of action = 'accepted' is logged.

Table: fb_friend_requests

--建表
use strata;
create table fb_friend_requests
(
 user_id_sender string
,user_id_receiver string
,`date` DATE
,action string
)
row format delimited fields terminated by ',';
load data local inpath '/tmp/strata/fb_friend_requests.txt' overwrite into table fb_friend_requests;

--查看表结构
hive> select * from fb_friend_requests;
OK
ad4943sdz       948ksx123d      2020-01-04      sent
ad4943sdz       948ksx123d      2020-01-06      accepted
dfdfxf9483      9djjjd9283      2020-01-04      sent
dfdfxf9483      9djjjd9283      2020-01-15      accepted
ffdfff4234234   lpjzjdi4949     2020-01-06      sent
fffkfld9499     993lsldidif     2020-01-06      sent
fffkfld9499     993lsldidif     2020-01-10      accepted
fg503kdsdd      ofp049dkd       2020-01-04      sent
fg503kdsdd      ofp049dkd       2020-01-10      accepted
hh643dfert      847jfkf203      2020-01-04      sent
r4gfgf2344      234ddr4545      2020-01-06      sent
r4gfgf2344      234ddr4545      2020-01-11      accepted
Time taken: 0.072 seconds, Fetched: 12 row(s)

 

--
with accept as
(
select * 
from fb_friend_requests where action='accepted'
),
sent as
(
select * 
from fb_friend_requests where action='sent'
)
select s.`date`,count(a.user_id_receiver)/count(s.user_id_sender) as rate
from 
sent s left join accept a on s.user_id_sender=a.user_id_sender
and s.user_id_receiver=a.user_id_receiver
group by s.`date`;

--output
OK
2020-01-04      0.75
2020-01-06      0.6666666666666666