set enable_result_cache_for_session = off;
:EXPLAIN
-- start query 41 in stream 12 using template query8.tpl and seed 1228833340
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 (
                          '93703','43882','97838','88572','80961','81918',
                          '68868','88927','18551','69678','13813',
                          '67059','65630','95004','67971','52700',
                          '63369','35447','64160','85931','19163',
                          '58449','17538','41543','67745','31976',
                          '69394','96855','99555','24073','66678',
                          '60616','45137','59372','23092','73140',
                          '99397','25994','23870','91932','22877',
                          '92651','53129','31469','85764','42323',
                          '53909','51191','86656','22262','85237',
                          '18412','94095','49490','78361','86232',
                          '21722','12052','32836','27714','88002',
                          '34574','51753','68018','88857','95882',
                          '38254','44712','78865','65708','44132',
                          '34305','43399','43243','76811','21144',
                          '73126','36698','89103','27291','22801',
                          '60276','53636','64963','23941','66284',
                          '91707','89566','42734','49363','68466',
                          '63917','14852','99222','76549','76413',
                          '45332','22290','27031','33464','48470',
                          '69397','46700','90661','46956','89540',
                          '54271','80331','20702','29569','50018',
                          '52230','91905','43819','60100','18910',
                          '78415','46161','47997','91716','91352',
                          '96691','86244','36970','66585','38133',
                          '77471','26616','64855','17583','12360',
                          '18073','61462','71304','46194','52201',
                          '64558','60420','31119','93055','48320',
                          '19416','83878','96579','70414','52075',
                          '82575','88745','53802','42071','97595',
                          '41196','20408','67018','94679','77544',
                          '13818','80153','17895','63061','53927',
                          '72994','86368','74533','56469','50217',
                          '35808','99753','79888','70792','28808',
                          '14108','37505','89925','82289','43187',
                          '57968','52955','45520','58551','41354',
                          '65292','16425','90830','41220','42599',
                          '90456','32292','74484','17758','74405',
                          '33618','28360','58649','27532','15369',
                          '71301','36557','13729','89205','67197',
                          '34311','46287','31490','50310','60699',
                          '83298','77680','54511','52430','70398',
                          '86916','65445','82915','11735','84049',
                          '78589','39298','21723','22409','20001',
                          '90204','37223','72196','28412','67533',
                          '27776','84925','53506','81814','18858',
                          '33828','33172','66941','67795','87695',
                          '66601','74644','43488','89340','26747',
                          '18900','21808','82704','40805','61223',
                          '62395','30305','76495','52290','45629',
                          '13302','17815','22321','23393','84464',
                          '31287','79621','82520','46042','66353',
                          '43686','41113','32154','86511','91483',
                          '14646','37072','19343','17892','63196',
                          '17676','63258','55180','36354','18022',
                          '66832','23355','19898','42739','23419',
                          '61144','71315','58926','71418','82961',
                          '92742','29429','14432','51281','41977',
                          '28452','52292','96301','20025','86749',
                          '78661','95480','27440','71556','93545',
                          '16723','68025','25718','65573','93016',
                          '11385','20140','72802','92508','60096',
                          '46104','38660','45442','22825','81977',
                          '81261','20731','97797','31565','53260',
                          '32988','86343','40868','26113','16648',
                          '43690','68185','57571','66037','72584',
                          '52021','30307','73369','42142','16297',
                          '88005','62137','64946','45394','94567',
                          '98119','14510','32689','99343','89558',
                          '65141','57153','54543','19263','10829',
                          '32297','42264','95102','22643','29091',
                          '92295','40276','97499','73670','15462',
                          '78676','37535','42015','68708','91983',
                          '63717','64800','29349','37758','64693',
                          '32238','72907','75238','54776','83621',
                          '28751','46877','37930','63956','27683',
                          '88439','76734','46173','69565','63352',
                          '54970','21889','41909','47183','36574',
                          '39831','83107','71188','86903','53837',
                          '79048','30007','55202','39570')
     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 = 2 and d_year = 1998
  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 41 in stream 12 using template query8.tpl