td_intern rossmann

train_join.sql

WITH train_opened as (
SELECT  
rowid() as rowid,
  t.stateholiday, t.store, t.promo, t.dayofweek, 
  t.date, t.schoolholiday, t.sales, 
SUBSTR(t.date,1,4) as year,
SUBSTR(t.date,6,2) as month,
SUBSTR(t.date,9,2) as day
FROM
  train_original t 
  WHERE sales!=0
)INSERT OVERWRITE TABLE training2
SELECT
t1.rowid, t1.stateholiday, t1.store, t1.promo, t1.dayofweek, 
t1.date, t1.schoolholiday,t1.sales, t1.year, t1.month, t1.day,
t2.promo2sinceweek, t2.competitionopensinceyear,
t2.assortment, t2.promo2sinceyear, t2.competitiondistance, t2.promointerval, t2.promo2,
t2.storetype, t2.competitionopensincemonth
FROM
  train_opened t1
  JOIN store_raw t2 ON (t1.store = t2.store)
order by rowid asc;