td_intern rossmann

test_join.sql

INSERT OVERWRITE TABLE testing2
SELECT
rowid() as rowid, t1.id, t1.stateholiday, t1.store, t1.promo, t1.dayofweek, 
t1.date, t1.schoolholiday, t2.promo2sinceweek, t2.competitionopensinceyear,
t2.assortment, t2.promo2sinceyear, t2.competitiondistance, t2.promointerval, t2.promo2,
t2.storetype, t2.competitionopensincemonth,
SUBSTR(t1.date,1,4) as year,
SUBSTR(t1.date,6,2) as month,
SUBSTR(t1.date,9,2) as day
FROM
  test_original t1
  JOIN store_raw t2 ON (t1.store = t2.store);