How to get average of views before clicking on advertisement?

Question

I've got two tables containing information about viewing and clicking on advertisement by each user:

Clicks:

userID   Action   Time
123      c        2016-01-08 01:57:00
123      c        2016-01-11 03:17:12
200      c        2016-01-09 02:20:10
332      c        2016-01-12 07:07:07

Views:

userID   Action   Time
123      0        2016-01-07 01:33:00
123      0        2016-01-10 04:12:13
200      w        2016-01-08 02:20:10
332      0        2016-01-09 07:07:07
332      0        2016-01-10 07:07:07
332      0        2016-01-11 07:07:07

I want to know what was the average of views before clicking on advertisement first time, second time etc. From the example above: User 123 clicked first time having only one view before, user 200 also, and user 332 had three views before clicking first time. So the average for the first click is (1+1+3)/3=1.67. Only user 123 clicked twice, after two views (in total).

The result I want looks like this:

Nr_clicks  Avg_views
1          1.67
2          2
...

The Action column in Views table can take different values (but not c), in Clicks table is always c. I was trying to use JOIN with subquery and then average results but I get only some errors. I'm working in Hive.

Answer 1

Ok, it take me some time but I think it works well:

select clickcume nr_clicks, avg(view_b) avg_view_before from(
select userid, clickcume, max(viewcume) view_b from(
select a.*, b.time_v, b.viewcume  from   
(
  select userid, time as time_c, 
         count(*) over (partition by userid order by time) as clickcume
  from views) a join 
(
  select userid, time as time_v,
         count(*) over (partition by userid order by time) as viewcume
  from clicks) b 
  on (a.userid=b.userid)
where time_c>=time_v ) c
group by userid, clickcume ) d
group by clickcume