set enable_result_cache_for_session = off;
:EXPLAIN
-- start query 49 in stream 5 using template query8.tpl and seed 718275362
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 (
                          '42448','11241','17041','62423','96542','87878',
                          '30318','10816','26459','45459','38453',
                          '47841','99653','49956','43253','27265',
                          '57646','41317','49544','56012','99926',
                          '71318','21013','87162','11454','37405',
                          '99037','58728','30879','37571','57238',
                          '29052','56231','77615','16338','17967',
                          '51929','74148','25796','71647','22910',
                          '12555','78745','67081','87120','20991',
                          '91332','49159','27535','40936','95686',
                          '30168','82533','97774','90056','63980',
                          '60852','61994','63199','22422','57986',
                          '20011','43832','77749','62492','11423',
                          '24729','62690','90108','44638','11240',
                          '13007','71108','74387','44174','76073',
                          '83992','69641','40173','76923','82570',
                          '15132','75653','17056','86399','24968',
                          '28399','45194','82045','27048','48670',
                          '58076','80265','18484','27980','18042',
                          '85308','49636','75155','73946','81683',
                          '50916','42418','60985','19470','25130',
                          '92769','44670','59636','44953','79971',
                          '87698','67254','37880','98364','20829',
                          '99847','28653','95112','68927','93375',
                          '94937','85892','12727','69633','60058',
                          '15403','95142','10415','66777','14135',
                          '64080','78887','91478','85145','98739',
                          '61791','29455','34429','18271','39123',
                          '26205','74031','22861','61430','95172',
                          '17436','84546','93232','19177','41666',
                          '93173','39713','26277','28275','10063',
                          '28732','25673','78182','36919','49248',
                          '14669','68568','82571','63172','56351',
                          '59532','35254','21160','92690','68763',
                          '96601','61018','66192','66459','42278',
                          '66242','35923','41179','29658','89105',
                          '48492','56793','89749','29752','49527',
                          '78817','65976','96910','10929','68958',
                          '76157','16782','15172','33721','34851',
                          '80592','59233','73496','75394','28837',
                          '48202','48322','96475','24082','44080',
                          '61109','25264','39426','68915','96094',
                          '58512','47646','79776','67685','53397',
                          '55206','39723','95461','45964','75230',
                          '85799','16693','98401','79922','25577',
                          '46027','17248','74481','24742','99043',
                          '33177','98464','34407','82158','16175',
                          '86581','59016','10583','26883','25559',
                          '18797','30177','14773','34915','71332',
                          '64462','17789','75165','62315','96605',
                          '11681','30750','28826','31875','12036',
                          '75839','15881','33477','45270','37824',
                          '22641','98721','64525','94855','20788',
                          '50668','89687','17457','28833','21898',
                          '20027','34273','26178','24646','93528',
                          '57609','71500','85558','83405','26566',
                          '15658','56744','85875','45902','58316',
                          '15662','37209','29245','21510','44316',
                          '50845','47599','80080','38024','22403',
                          '61730','56582','13576','48497','76599',
                          '32072','19240','95507','68481','35135',
                          '40268','63565','20399','30359','15072',
                          '84235','41968','43337','22119','63413',
                          '41453','89948','24069','52245','14876',
                          '24115','20964','69997','29391','40106',
                          '49845','67904','77084','49042','76456',
                          '22413','65970','47192','59343','50326',
                          '65432','42108','74855','59084','29691',
                          '16283','32194','65749','68253','50621',
                          '86525','67575','49340','72076','30403',
                          '81910','61731','63586','19847','95681',
                          '63449','80996','94627','11392','16341',
                          '30410','95696','81632','41813','32769',
                          '89357','99323','84792','36027','21187',
                          '71314','49241','29728','99928','96649',
                          '18513','70497','61709','81428','98064',
                          '57764','35083','31816','73947','73784',
                          '15048','89509','38638','63014','54334',
                          '61391','93432','37213','49730','61769',
                          '37395','40341','58772','92161')
     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 = 2000
  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 49 in stream 5 using template query8.tpl