set enable_result_cache_for_session = off;
:EXPLAIN
-- start query 25 in stream 3 using template query8.tpl and seed 1202377543
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 (
                          '42533','10535','48391','74396','24303','89589',
                          '70518','49646','39370','30616','48615',
                          '43482','59549','84631','48907','90259',
                          '89253','19002','52434','37512','31744',
                          '11517','65603','70114','68537','12819',
                          '99980','66621','82727','99598','93730',
                          '27673','97615','93704','48826','87247',
                          '97339','61220','48415','23946','79916',
                          '75723','90163','84589','90831','52128',
                          '58692','38358','14522','11531','62042',
                          '17380','31406','39036','38037','43545',
                          '68377','61360','49202','62166','73970',
                          '20766','48453','82024','21044','78811',
                          '98476','42089','74845','11082','47857',
                          '30802','78615','81719','31006','64092',
                          '43132','34669','29978','76735','73206',
                          '40222','85612','86793','88331','62122',
                          '77534','34531','93153','34256','46333',
                          '12904','86076','62714','39903','74906',
                          '82663','27575','53949','52185','33593',
                          '73222','92079','97149','76447','62944',
                          '32070','30896','65976','71158','70998',
                          '95335','27186','85451','56929','20094',
                          '17395','63869','39253','79229','95759',
                          '94294','74655','14188','23682','69035',
                          '88272','88901','39199','78600','86712',
                          '72790','39856','99133','79664','43095',
                          '29825','86852','37193','33820','85136',
                          '96632','93495','51188','15642','64691',
                          '58381','39544','71111','11138','23847',
                          '30299','54335','94972','52273','69477',
                          '27684','17959','25894','98258','53369',
                          '48094','15115','54350','64839','42250',
                          '62181','55211','25412','91043','84193',
                          '39158','26374','76789','98021','24707',
                          '74134','13408','51938','45175','73952',
                          '51452','60953','80002','52676','45953',
                          '46865','65267','47757','14202','35784',
                          '25312','62836','35956','89154','10417',
                          '21773','64045','49197','11429','18969',
                          '22734','47592','40776','53201','62060',
                          '51791','87664','87671','99102','55232',
                          '44023','44657','52888','18496','13268',
                          '20518','29487','45866','89228','54540',
                          '50325','58241','98860','74060','49596',
                          '65957','84369','95536','37639','18740',
                          '95145','92530','16478','70788','34828',
                          '76535','65787','83214','33196','33330',
                          '52654','86755','59434','80238','36609',
                          '58633','32501','39561','20541','44068',
                          '52677','44794','64234','69109','18249',
                          '44044','74080','39017','34859','70964',
                          '15012','34645','59224','49679','57668',
                          '55891','75708','93263','22057','91118',
                          '41182','80679','49831','95231','23598',
                          '92017','75212','10335','86616','72638',
                          '34158','99055','70139','74379','70023',
                          '63930','32442','21429','84294','69725',
                          '57050','86911','45436','98077','68237',
                          '80948','10994','78520','85977','73166',
                          '18760','45888','91571','27172','42226',
                          '67530','56238','44024','43317','77557',
                          '63687','95724','35943','82491','46244',
                          '34293','20768','30807','45321','89463',
                          '40395','10854','85959','37896','16407',
                          '97379','84868','61502','50106','35572',
                          '17390','91246','60613','59573','64320',
                          '48597','94399','73445','37041','49029',
                          '40252','73869','89940','14943','34464',
                          '34803','95380','62912','38356','60642',
                          '19494','28716','98877','24697','92065',
                          '77466','53667','91040','62203','70999',
                          '51964','43186','84313','55029','82100',
                          '86475','49532','62637','28086','50215',
                          '14771','68942','67847','67911','88620',
                          '50550','93637','88452','22573','76360',
                          '43805','43770','14577','24887','98996',
                          '71198','13367','39887','60721','37467',
                          '17087','55635','99034','38896','57169',
                          '37434','47156','51293','31933')
     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 = 2001
  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 25 in stream 3 using template query8.tpl