set enable_result_cache_for_session = off;
:EXPLAIN
-- start query 93 in stream 18 using template query8.tpl and seed 646755314
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 (
                          '83917','98598','53215','81291','20647','67871',
                          '48275','84652','69861','80760','34673',
                          '64573','19880','57786','28066','94856',
                          '76897','30837','21740','23689','84966',
                          '40597','54779','76964','87947','79988',
                          '21906','68876','37817','20725','53418',
                          '47598','70408','47689','28252','38493',
                          '42359','22584','70346','51452','70166',
                          '95606','66132','92296','84312','67731',
                          '22546','47324','50845','81859','21330',
                          '67060','73900','16837','75853','74121',
                          '17029','46191','39656','95394','99894',
                          '46413','31888','10215','46098','14695',
                          '89724','90751','35250','58896','32328',
                          '98773','99881','96003','31903','98549',
                          '21464','14188','31629','26473','21599',
                          '71795','85997','86530','24555','90103',
                          '88162','43472','12168','58425','86406',
                          '83044','16042','52412','27764','74754',
                          '81913','15988','61648','89944','20411',
                          '83997','66617','22027','54084','99952',
                          '98809','27631','80444','14281','40444',
                          '64623','74846','86713','15206','41420',
                          '50537','77424','75962','88146','90908',
                          '46169','44627','58792','86521','89963',
                          '41874','44726','66116','22911','14497',
                          '50823','87349','20795','94650','31755',
                          '87833','75766','66077','10620','16093',
                          '60120','47392','45856','94963','76848',
                          '39215','66841','11319','88993','10000',
                          '20148','13223','78800','24574','44725',
                          '76175','85638','23260','20576','97195',
                          '77017','51705','67847','45009','49754',
                          '52946','11343','48670','65925','90226',
                          '68062','21499','89324','55287','32559',
                          '93789','31681','76767','93543','70954',
                          '39691','12511','38728','10382','35182',
                          '26730','91747','36156','87774','89301',
                          '73783','82543','23891','97349','19444',
                          '73398','43401','65369','29558','18444',
                          '78746','14234','84326','24665','89329',
                          '38554','25668','14940','96848','68627',
                          '58722','39085','83555','33253','36309',
                          '15449','97328','58242','90379','83206',
                          '30171','96736','93165','35711','82764',
                          '98403','38783','96768','10919','19209',
                          '46577','60382','31921','85362','39815',
                          '40380','84780','21213','97990','80841',
                          '96093','43388','41952','83616','41871',
                          '80928','74114','41500','25790','27374',
                          '35468','31313','69820','61336','81610',
                          '52468','16736','38621','77428','60537',
                          '45234','42448','84190','25824','17394',
                          '23161','69311','94853','73785','26431',
                          '58788','46584','49925','13131','30404',
                          '83046','72273','85380','60089','33510',
                          '81382','67442','29208','83214','83052',
                          '20350','14271','77007','85911','99442',
                          '99858','22020','92179','76823','76528',
                          '92316','79716','64563','65646','70010',
                          '36466','82066','35371','98089','47076',
                          '71614','65902','27470','89807','48482',
                          '76493','45238','57644','31455','92254',
                          '69409','62748','84325','79302','17562',
                          '58769','68967','37142','56824','40363',
                          '45826','82940','23622','60215','90623',
                          '78429','81073','14040','86693','73509',
                          '80627','74201','76085','46371','52902',
                          '33691','65829','29048','91594','12874',
                          '68743','80483','85779','51724','40107',
                          '67258','51696','93282','59030','51376',
                          '35474','40167','37429','27798','73870',
                          '57287','45600','99216','79350','53414',
                          '63247','41345','67058','22270','70429',
                          '86987','83826','32766','80192','80738',
                          '71195','23418','56306','78150','29031',
                          '77547','42602','93127','84999','95206',
                          '64828','11609','95353','74985','65633',
                          '54073','36705','75830','48553','19175',
                          '80342','83603','12585','32306')
     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 = 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 93 in stream 18 using template query8.tpl