set enable_result_cache_for_session = off;
:EXPLAIN
-- start query 74 in stream 7 using template query8.tpl and seed 253754972
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 (
                          '42801','92190','63300','87844','71834','85231',
                          '17810','63011','56122','82109','79216',
                          '37453','13736','64742','11945','38223',
                          '95658','42319','50649','91098','39171',
                          '96179','63317','41734','76589','35762',
                          '21169','15713','74959','35323','57934',
                          '35718','74984','74895','80406','99278',
                          '44368','88047','78876','35702','72193',
                          '24003','59750','95441','70387','99477',
                          '95177','92688','50222','59543','16487',
                          '36784','19455','69668','41117','83998',
                          '30578','16959','88893','42658','86752',
                          '61870','91254','36657','89177','25939',
                          '52991','94795','99151','89116','21660',
                          '45198','52782','15371','67501','87392',
                          '97891','92159','13397','23168','25313',
                          '32386','22085','16480','15708','85959',
                          '22609','92038','85925','43915','18289',
                          '54857','34734','37718','24509','71496',
                          '18341','21187','17925','68912','93388',
                          '37193','94699','83852','42522','43787',
                          '72074','13470','46084','64038','45035',
                          '82133','61923','64407','23860','98306',
                          '74085','10567','29476','98762','65847',
                          '39611','94447','64846','52424','60424',
                          '71742','57504','12153','13131','23060',
                          '82087','55687','94256','13187','29724',
                          '70533','92486','10617','62067','40828',
                          '41811','11511','56052','30636','95453',
                          '89314','28437','77455','82366','91684',
                          '49695','51810','35980','71082','25586',
                          '20124','90486','99957','35324','42676',
                          '44003','39545','33638','98480','44966',
                          '67921','17082','43897','43146','41054',
                          '62001','25199','34989','42033','28538',
                          '76309','48418','88702','40907','12497',
                          '67805','57669','43028','21502','58946',
                          '10400','60360','90897','28330','65715',
                          '88783','39525','47080','23769','20400',
                          '65861','27699','52632','21216','48513',
                          '32855','85180','47133','20346','70312',
                          '79246','27961','81710','83898','88306',
                          '57288','95705','35551','62639','93212',
                          '15670','80821','17854','64205','73313',
                          '67057','23294','54910','67628','24357',
                          '10856','52628','74887','15212','17945',
                          '71895','45320','71670','55454','77950',
                          '38645','64576','15423','35443','57202',
                          '43502','43752','81807','39669','21479',
                          '30287','65452','58973','68020','30965',
                          '54133','82601','66039','75006','16145',
                          '87354','26486','27757','16026','39264',
                          '15208','58523','10258','72061','41366',
                          '22921','27238','89638','22629','46204',
                          '19425','82927','51743','18183','85531',
                          '35914','69880','21132','82455','26365',
                          '96349','35223','14752','50202','10345',
                          '12005','89710','36778','22485','60327',
                          '69023','53506','57396','53568','67402',
                          '57876','85842','13820','16957','42056',
                          '53283','44794','49744','16462','43444',
                          '52036','28237','67905','58521','91970',
                          '80229','22845','18711','30371','17623',
                          '79332','16899','10405','42942','98072',
                          '38038','95517','70075','74345','18029',
                          '54809','37043','83985','42791','69870',
                          '12576','38510','55096','11180','63232',
                          '94626','15111','29747','95424','56403',
                          '47161','28001','48052','50177','90333',
                          '88250','95286','41284','31794','69376',
                          '22858','29065','93963','59529','96321',
                          '47934','20158','97299','20200','52791',
                          '10056','21976','23180','67688','90372',
                          '21428','39685','59899','86082','92186',
                          '78759','95510','13365','17440','63900',
                          '84553','14921','73519','58835','77194',
                          '35357','92341','43845','42362','31385',
                          '52181','58334','17011','54648','91138',
                          '61871','58022','47510','98483','35618',
                          '12674','54186','86822','46425')
     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 74 in stream 7 using template query8.tpl