set enable_result_cache_for_session = off;
:EXPLAIN
-- start query 48 in stream 6 using template query8.tpl and seed 2083325724
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 (
                          '55686','61903','71967','72240','34874','82660',
                          '22279','62236','90251','21432','94118',
                          '90182','23706','60292','82700','45781',
                          '61059','65974','47558','18823','87624',
                          '34914','61890','23750','51978','63251',
                          '48393','65068','93858','56914','32766',
                          '92603','86341','45324','12797','93313',
                          '78842','18786','18181','64985','69855',
                          '78122','37501','59562','32884','18517',
                          '38748','74284','18907','51283','28634',
                          '52524','75909','47283','50223','20399',
                          '16028','33380','55238','22741','62308',
                          '39140','37656','11516','57110','82854',
                          '20802','42211','55635','89213','87054',
                          '43225','19404','20665','83146','62897',
                          '79064','80922','51078','39361','19583',
                          '87319','83255','60243','19658','13524',
                          '14439','93059','33838','60213','71401',
                          '36146','21298','26498','87706','83669',
                          '58951','61020','93618','61360','15652',
                          '54383','14593','44942','35083','92616',
                          '48452','31627','95266','65736','50974',
                          '84750','34733','57740','91652','22563',
                          '20388','47701','11286','98878','82733',
                          '95529','55932','31352','54414','68529',
                          '75298','66409','21155','16573','92903',
                          '77257','23898','83523','91883','45671',
                          '30536','61329','50099','32628','41517',
                          '13797','38166','47317','12574','69789',
                          '69683','73868','19322','52407','75850',
                          '43985','40439','91468','87741','58057',
                          '26381','32444','26527','84180','87605',
                          '23369','28424','53208','84399','33735',
                          '36414','72542','34105','97640','34188',
                          '49350','18104','46579','64080','74924',
                          '97606','31606','47312','54160','67857',
                          '80245','33338','53127','42135','85287',
                          '50079','42764','89160','86018','82608',
                          '69762','52317','25379','66840','46215',
                          '34898','52238','61972','62978','56876',
                          '95552','67178','24761','77425','48817',
                          '63361','48398','18804','10765','13519',
                          '13862','15601','16259','30334','72082',
                          '23444','38896','42698','37456','64697',
                          '19527','69105','75306','91582','48983',
                          '49578','30878','31322','65369','62443',
                          '31587','74449','83189','87672','61304',
                          '29297','58954','60287','61581','91923',
                          '63319','76751','67113','20485','56112',
                          '51363','26782','66234','45292','19168',
                          '92288','35126','52339','95741','40224',
                          '32475','87424','54339','92116','45855',
                          '53847','61167','38709','88723','77489',
                          '34230','56179','11041','17639','73274',
                          '39587','98705','92537','85952','82577',
                          '27855','10005','56115','16253','63041',
                          '94843','23230','10193','71146','78216',
                          '74474','91823','60366','13746','72424',
                          '66517','28942','50267','76633','90586',
                          '35601','57657','44789','79482','65777',
                          '30694','99076','66164','44704','79629',
                          '16789','90676','73715','95539','54418',
                          '40592','11323','80994','19585','22388',
                          '83564','19052','53102','62098','76131',
                          '54163','65865','81588','31040','71411',
                          '31114','19581','32453','36082','25205',
                          '69199','16636','17708','53981','92855',
                          '80018','51429','96323','46637','61188',
                          '69674','55096','88091','72352','14857',
                          '22658','61751','79609','94149','27015',
                          '44025','43813','17702','47654','64139',
                          '12393','37240','89444','65464','72354',
                          '68652','39342','91740','22821','23838',
                          '39973','88649','29421','27389','31121',
                          '70024','42144','91976','98113','37301',
                          '80473','52481','65475','41831','37061',
                          '98730','47692','84027','20317','92192',
                          '74810','41305','98034','58624','29464',
                          '33436','93240','96793','23695','29888',
                          '12155','67493','44602','85951')
     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 = 2002
  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 48 in stream 6 using template query8.tpl