train_join.sql

WITH t1 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.*, 
    t2.promo2sinceweek, 
    t2.competitionopensinceyear, 
    t2.assortment, 
    t2.promo2sinceyear, 
    t2.competitiondistance, 
    t2.promointerval, 
    t2.promo2, 
    t2.storetype, 
    t2.competitionopensincemonth
  FROM
    t1
    JOIN store_raw t2 ON (t1.store = t2.store)
;