Hive query to find conversion ratio

Question

I am trying this query in Hive and it's not working.

select
  (
    (
      select
        count(*)
      from
        click_streaming
      where
        page_id= 'e7bc5fb2-1231-11eb-adc1-0242ac120002'
        and is_page_view = 'Yes'
    ) / (
      select
        count(*)
      from
        click_streaming
      where
        button_id= 'fcba68aa-1231-11eb-adc1-0242ac120002'
        and is_button_click= 'Yes'
    )
  ) as conversion_ratio;

Error I am getting: cannot recognize input near 'select' 'count' '(' in expression specification

I am basically trying to get conversion rate of customers who view the page and click the button to book a cab.

Answer 1

This is not how the syntax can be. Just join them both or use a case when to do your job.

select 
sum(case when page_id= 'e7bc5fb2-1231-11eb-adc1-0242ac120002' and is_page_view = 'Yes' then 1 else 0 end) /
sum(case when button_id= 'fcba68aa-1231-11eb-adc1-0242ac120002' and is_button_click= 'Yes' then 1 else 0 end) conv_ratio
FROM 
 click_streaming

or you can reuse your SQLs but you got to join them

select c1/c2
from (
select
        count(*) c1
      from
        click_streaming
      where
        page_id= 'e7bc5fb2-1231-11eb-adc1-0242ac120002'
        and is_page_view = 'Yes') rs
join (select
        count(*) c2
      from
        click_streaming
      where
        button_id= 'fcba68aa-1231-11eb-adc1-0242ac120002'
        and is_button_click= 'Yes')rs2