set hive.cbo.enable=false;

drop table if exists users_table;
CREATE TABLE users_table(
  `field_1` int,
  `field_2` string,
  `field_3` boolean,
  `field_4` boolean,
  `field_5` boolean,
  `field_6` boolean,
  `field_7` boolean)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
load data local inpath '../../data/files/small_csv.csv' into table users_table;

explain
with all_hits as (
select * from users_table
),
all_exposed_users as (
select distinct
field_1,
field_2
from all_hits
where field_3
),
interacted as (
select distinct
field_1,
field_2
from all_hits
where field_4
)
select
all_exposed_users.field_1,
count(*) as nr_exposed,
sum(if(interacted.field_2 is not null, 1, 0)) as nr_interacted
from all_exposed_users
left outer join interacted
on all_exposed_users.field_1 = interacted.field_1
and all_exposed_users.field_2 = interacted.field_2
group by all_exposed_users.field_1
order by all_exposed_users.field_1;

with all_hits as (
select * from users_table
),
all_exposed_users as (
select distinct
field_1,
field_2
from all_hits
where field_3
),
interacted as (
select distinct
field_1,
field_2
from all_hits
where field_4
)
select
all_exposed_users.field_1,
count(*) as nr_exposed,
sum(if(interacted.field_2 is not null, 1, 0)) as nr_interacted
from all_exposed_users
left outer join interacted
on all_exposed_users.field_1 = interacted.field_1
and all_exposed_users.field_2 = interacted.field_2
group by all_exposed_users.field_1
order by all_exposed_users.field_1;