set enable_result_cache_for_session = off;
:EXPLAIN
-- start query 16 in stream 2 using template query8.tpl and seed 107667321
select  s_store_name
      ,sum(ss_net_profit)
 from store_sales
     ,date_dim
     ,store,
     (select ca_zip
     from (
      SELECT substring(ca_zip,1,5) ca_zip
      FROM customer_address
      WHERE substring(ca_zip,1,5) IN (
                          '42949','94280','14432','57469','58628','88255',
                          '73544','32766','95983','51512','94564',
                          '85215','12770','17888','91079','51016',
                          '99261','69596','92993','35366','96107',
                          '92824','11681','84994','99522','55055',
                          '10866','18490','60672','12859','61261',
                          '53222','90058','50004','13856','83678',
                          '70636','56719','63081','66444','76495',
                          '39507','74007','63907','33284','95025',
                          '38856','50394','13535','17847','83889',
                          '20096','48259','38270','91231','28741',
                          '92695','79223','89941','55379','28036',
                          '89353','50046','78047','53992','20953',
                          '60919','48997','57292','11424','33276',
                          '49478','72962','91711','83198','33662',
                          '89027','33657','11798','73855','17217',
                          '91677','33510','58907','84949','12151',
                          '98467','30754','64142','41611','30366',
                          '39471','93183','77279','75492','19015',
                          '16649','21994','83304','69905','75173',
                          '50798','25884','63069','39381','84533',
                          '76692','18385','19500','70488','57713',
                          '56737','42121','35135','45550','63662',
                          '80718','13067','47032','73812','64437',
                          '41813','40974','21122','24101','87657',
                          '25693','12944','73198','68539','44845',
                          '51935','55299','90423','59191','87694',
                          '60230','91882','55774','83689','45725',
                          '47211','99111','72436','90786','97825',
                          '85677','54747','51684','24829','95067',
                          '91291','21067','39793','68293','86236',
                          '74403','17850','79566','62744','50726',
                          '27403','67418','28076','85392','40345',
                          '19625','25239','11876','79952','59154',
                          '82930','69281','31674','36437','86946',
                          '25458','23205','73336','61554','70297',
                          '59592','51854','99363','37859','26282',
                          '64433','91447','63260','68194','97860',
                          '39529','82425','13444','41216','47072',
                          '46699','63272','25893','55208','32465',
                          '60171','38935','63624','85457','95201',
                          '97565','41972','23923','71315','46388',
                          '52760','32177','36738','25891','39503',
                          '34959','11081','28152','91887','78883',
                          '27583','65271','59237','10825','85460',
                          '54137','58210','68084','97697','50700',
                          '51074','49606','23993','50393','74570',
                          '93454','22077','36417','50453','92167',
                          '47795','61350','12458','44385','50493',
                          '87244','65963','70288','52944','95461',
                          '44663','84550','45575','26352','58383',
                          '77326','78530','46773','96423','47500',
                          '42328','79103','51628','63669','93462',
                          '55121','37879','60178','25804','74288',
                          '87949','73099','92807','99800','68562',
                          '99041','68882','48144','47627','48592',
                          '31952','49158','44927','99006','35621',
                          '57990','27321','70817','18388','84117',
                          '42778','94495','32593','36579','49728',
                          '29562','79240','38272','45714','81788',
                          '43262','30079','73382','36015','58220',
                          '74934','30968','12890','41956','37875',
                          '76032','87584','31695','72788','57755',
                          '92625','90390','47664','28302','48603',
                          '70117','81739','23534','81364','91062',
                          '87810','32743','40391','57016','83958',
                          '97650','95090','26509','63639','35244',
                          '55776','68232','80559','79770','38319',
                          '30214','68846','72518','51985','91545',
                          '69815','97821','65715','17504','72644',
                          '15440','39819','87480','74605','64750',
                          '61213','88132','82761','37499','45053',
                          '76267','16327','69483','68849','30737',
                          '86684','68905','74781','93799','29810',
                          '85036','14360','36053','25999','72240',
                          '72374','71072','93117','15365','27464',
                          '80699','85244','32483','87974','24985',
                          '68258','25334','59880','76287','80144',
                          '80454','53357','26876','63036','12000',
                          '65941','17859','36119','56575')
     intersect
      select ca_zip
      from (SELECT substring(ca_zip,1,5) ca_zip,count(*) cnt
            FROM customer_address, customer
            WHERE ca_address_sk = c_current_addr_sk and
                  c_preferred_cust_flag='Y'
            group by ca_zip
            having count(*) > 10)A1)A2) V1
 where ss_store_sk = s_store_sk
  and ss_sold_date_sk = d_date_sk
  and d_qoy = 1 and d_year = 1999
  and (substring(s_zip,1,2) = substring(V1.ca_zip,1,2))
 group by s_store_name
 order by s_store_name
 limit 100;

-- end query 16 in stream 2 using template query8.tpl