set enable_result_cache_for_session = off;
:EXPLAIN
-- start query 94 in stream 10 using template query8.tpl and seed 1438525295
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 (
                          '32047','42657','27538','43182','96086','17951',
                          '53301','15905','79932','25763','68635',
                          '58604','48690','74965','19580','60202',
                          '68604','58613','32953','29985','82463',
                          '68151','79431','70125','77348','99922',
                          '24169','96735','61430','47283','43307',
                          '79772','39593','93552','89115','24396',
                          '15454','32067','77146','40463','81818',
                          '36170','72687','84781','87249','90827',
                          '93245','82930','99704','44821','11369',
                          '37353','31742','68569','28615','69959',
                          '41321','48514','88799','85760','61884',
                          '68901','11477','53165','80445','70436',
                          '26035','84600','70713','92976','85989',
                          '94740','41810','69277','15518','18580',
                          '21650','35437','64525','23761','23396',
                          '65065','82631','14673','52869','88134',
                          '25781','75226','22992','34997','93047',
                          '36918','36993','80438','53070','60873',
                          '31758','42266','26554','14999','69010',
                          '53848','66692','40945','57834','23037',
                          '68348','47729','27831','57777','47316',
                          '31745','17753','16758','11276','59260',
                          '12161','61256','87078','34393','30303',
                          '74642','79187','16765','82755','20371',
                          '30245','61678','41180','52579','97797',
                          '22545','78941','99303','48915','69485',
                          '56686','68041','89081','47338','48012',
                          '61622','59061','25365','54793','14081',
                          '88352','35175','40414','61610','30021',
                          '65108','95577','61950','54832','23071',
                          '87713','58716','60731','50760','32902',
                          '61042','64684','82031','15651','44921',
                          '59064','42648','86471','32644','61331',
                          '92314','74451','35964','61345','92894',
                          '34822','60852','98352','92084','12124',
                          '66849','57345','61829','64432','16693',
                          '98679','59575','58786','98410','80799',
                          '17814','33482','33280','94388','19984',
                          '83822','35529','42827','27050','24110',
                          '29427','94764','83914','13310','69360',
                          '65850','28529','67832','91414','43476',
                          '65279','44577','86750','96844','50130',
                          '81550','59070','20838','70944','33672',
                          '23693','78106','34932','66479','40508',
                          '35882','24315','79879','59793','56150',
                          '62271','33891','17913','63376','20367',
                          '96719','54952','56668','96479','78364',
                          '22953','75894','49619','17167','36348',
                          '31986','42780','24079','81560','34697',
                          '23833','95456','21264','13958','80959',
                          '60435','97717','72460','56979','53891',
                          '19482','52284','96236','91431','96070',
                          '56812','56823','60380','53225','54815',
                          '80357','97034','13797','22491','52034',
                          '20680','89003','28760','11963','23126',
                          '51058','22295','16389','53923','62483',
                          '94940','80014','69293','43511','16548',
                          '48378','75813','16231','22130','51510',
                          '57760','91345','77360','11793','15267',
                          '87325','84495','52222','30038','53356',
                          '10322','37797','15884','15773','50175',
                          '42547','23428','38788','42130','17764',
                          '71794','54853','57770','51050','63107',
                          '58548','84192','22682','87424','59763',
                          '97703','62392','39786','17653','72556',
                          '45973','58368','44607','94796','20962',
                          '66759','43202','30936','82227','97259',
                          '64916','55400','49559','61932','70477',
                          '48278','23563','27075','18572','78274',
                          '87646','34921','20251','63570','96614',
                          '45523','10126','29600','22240','13199',
                          '52933','23739','98188','75781','26224',
                          '86467','48985','38631','81207','79321',
                          '74149','99005','99408','43588','50859',
                          '13463','71156','60403','35031','19717',
                          '40109','94680','20545','53704','23900',
                          '91213','32973','67984','47282','75062',
                          '21190','86578','43117','49979','95999',
                          '85730','68477','46682','32077')
     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 = 1998
  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 94 in stream 10 using template query8.tpl