sql-style test cases

example_sql_test.sql

create table report1 as 
select
  person_id,
  count(1) as purch_cnt
from
  purchases
where
  purchase_date > '2014-03-01'
group by
  person_id
having purch_cnt > 3;

create table test1_report1 as
select
  person_id, 
  sum(case when purchase_date <= '2014-03-01' then 1 else 0 end) as purch_test
from
  report1
group by 
  person_id
having
  purch_test > 0;
  
-- if this is >0 then we have a problem; you could wrap up "test1_report1 into a subquery to avoid creating a table
-- but this makes it easier to debug if there are results - at least in Hive :)
select count(1) from test1_report1;

create table test2_report1 as
select
  person_id, 
  sum(case when purch_cnt <= 3 then 1 else 0 end) as purch_test
from
  report1
group by 
  person_id
having
  purch_test > 0;

--- see notes above
select count(1) from test2_report1;