set enable_result_cache_for_session = off;
:EXPLAIN
-- start query 52 in stream 19 using template query8.tpl and seed 130157632
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 (
                          '67482','48919','74574','15727','88460','58322',
                          '29128','71983','75305','11637','97719',
                          '54942','83783','55901','36475','11833',
                          '81382','34638','30365','61165','32709',
                          '96971','22453','30090','35000','42807',
                          '90933','65049','76087','20232','91139',
                          '65584','64381','82923','43159','13729',
                          '72403','61546','10548','50129','98766',
                          '89061','44408','73715','10043','70574',
                          '75043','49368','72862','49895','27992',
                          '27301','16765','69183','70817','21483',
                          '37892','86569','11672','76111','73720',
                          '67698','27650','99261','72410','51089',
                          '63408','36972','46997','86533','50155',
                          '44533','92449','86875','41842','77460',
                          '28047','70010','66145','30812','19190',
                          '41357','71700','75467','53464','84184',
                          '25986','15119','51183','18559','65886',
                          '52930','98023','46472','10023','70799',
                          '43140','39929','64006','41902','15559',
                          '87038','69283','58022','27944','64762',
                          '47852','80019','21411','87695','90539',
                          '85529','86333','72478','31057','60522',
                          '17903','60985','30614','12833','83930',
                          '49041','57530','30948','51431','24366',
                          '71118','72491','97299','62673','51137',
                          '12923','51149','10196','51112','27054',
                          '28056','27877','68047','95844','72173',
                          '12492','26003','48550','78772','53163',
                          '89730','56391','55306','87987','83942',
                          '92324','96032','67133','84296','96863',
                          '85030','23411','12161','39390','98168',
                          '51646','16366','73173','30443','47809',
                          '15614','60736','45157','26856','77286',
                          '54189','95854','32416','77907','37221',
                          '29693','84448','68670','38171','94191',
                          '94334','49545','69030','84877','90921',
                          '92222','39624','50624','19284','32181',
                          '50334','97887','83469','91663','12341',
                          '73328','52693','59392','44381','68387',
                          '25392','14721','17170','46021','62100',
                          '84181','58527','95823','33620','11705',
                          '26503','41327','65858','25001','45546',
                          '92186','97364','26972','19065','48080',
                          '32615','57122','56810','79047','80583',
                          '13035','11103','77635','18944','65612',
                          '78620','16501','92768','19514','84183',
                          '14323','34699','63900','42383','41792',
                          '48034','44171','18145','59660','94531',
                          '54667','86299','48241','37460','69777',
                          '69425','72823','25948','94631','13248',
                          '43424','99690','57322','45782','32611',
                          '21647','33617','48237','91904','92921',
                          '92204','51587','78225','13978','49569',
                          '70375','58366','72039','18628','42371',
                          '43201','43231','54683','98031','28835',
                          '49841','88384','64082','68950','50233',
                          '14967','97813','33481','66340','19320',
                          '65164','40801','93351','96282','46777',
                          '48169','25118','53960','80990','60734',
                          '82613','37823','55408','89770','37201',
                          '72656','89234','16684','77052','43589',
                          '66937','61106','66627','50524','72259',
                          '34940','90555','96434','59261','89076',
                          '39132','51006','41396','41066','98439',
                          '45079','83066','51208','81302','69161',
                          '54018','24720','40378','85641','48052',
                          '23512','44112','95517','85790','95996',
                          '10699','10618','73580','25451','90225',
                          '30930','14012','43074','57112','85999',
                          '17980','97586','90476','52985','69303',
                          '84004','10845','61674','12071','17288',
                          '48619','61771','54754','62694','82084',
                          '55177','12503','32983','64525','36955',
                          '98208','42834','96416','11994','62501',
                          '61531','29804','48059','47184','47201',
                          '10175','91773','89815','99532','59629',
                          '53598','92460','23028','75266','53082',
                          '11746','25656','40608','81499','80798',
                          '21061','36554','97315','21944')
     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 = 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 52 in stream 19 using template query8.tpl