set enable_result_cache_for_session = off;
:EXPLAIN
-- start query 99 in stream 9 using template query8.tpl and seed 1881851809
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 (
                          '19349','34716','99692','82904','95232','46795',
                          '39527','37619','77442','47334','57197',
                          '64343','92398','83309','93605','52294',
                          '24157','11834','73098','14246','22389',
                          '58086','65064','88446','45284','74594',
                          '39406','60863','54776','26031','50136',
                          '50601','77571','35890','58477','93794',
                          '94479','36373','18380','31047','99923',
                          '45244','90577','49362','56432','49122',
                          '57329','70005','12363','21863','55586',
                          '17488','97091','64488','82897','59512',
                          '92951','93811','76158','55238','11342',
                          '79038','39625','20181','84634','70121',
                          '16799','95199','75525','65499','18990',
                          '20460','50443','14509','71698','67047',
                          '90777','90669','82048','68710','30420',
                          '23259','91891','77996','28424','25197',
                          '13382','33559','76122','69123','12339',
                          '54940','19653','23158','72148','96877',
                          '48663','88396','57877','31525','76645',
                          '42052','10800','86949','94835','54355',
                          '99117','86867','25548','14378','73058',
                          '49794','47913','50328','13584','10640',
                          '13489','97669','12617','49091','32154',
                          '45133','22199','42558','18700','46544',
                          '39784','18287','89073','45087','43219',
                          '20037','18059','19214','87933','24316',
                          '10015','14233','59792','41202','62973',
                          '71056','93814','11260','56285','70341',
                          '33247','65926','70319','50104','35489',
                          '54206','14531','34586','74225','49204',
                          '33845','26042','18304','71342','52994',
                          '74662','78222','93291','20549','63656',
                          '61709','37315','71385','93502','81455',
                          '73321','69614','82940','18948','40420',
                          '67138','61958','55226','73034','47040',
                          '14705','79651','85933','31227','37380',
                          '11327','48077','71473','28814','72318',
                          '86339','68605','79373','21546','95021',
                          '65541','63893','64667','64751','22359',
                          '48783','27049','16142','48201','61333',
                          '37784','54119','40633','94405','51663',
                          '16116','15420','91737','74240','24428',
                          '59790','24597','29427','94012','36599',
                          '90661','60155','79918','29084','48886',
                          '71037','99418','54895','44629','96916',
                          '70588','92724','97788','75004','73380',
                          '66421','83221','90821','29406','44998',
                          '44973','85747','79871','43238','83314',
                          '45788','12755','50500','73724','37871',
                          '61548','92898','91703','88537','11913',
                          '48188','52610','69575','74319','45522',
                          '62835','97517','60844','33117','23765',
                          '59587','45879','50887','75245','16993',
                          '97158','40772','86022','83666','98286',
                          '67649','50048','30832','66915','42005',
                          '35477','26109','63048','50976','35691',
                          '53783','43534','65643','10161','84132',
                          '53372','95479','68871','29480','29231',
                          '71750','97629','24999','36415','27345',
                          '73922','72562','62881','54514','34632',
                          '61822','20873','15710','41659','52785',
                          '59153','83800','20403','24401','67567',
                          '68859','89321','85150','35021','10316',
                          '73845','53826','71172','12350','99818',
                          '75010','93830','34536','69153','66779',
                          '24435','11979','25269','24153','19393',
                          '25145','49468','26311','54200','65925',
                          '30273','92464','12180','27722','88865',
                          '66538','94004','32337','81829','20862',
                          '18823','33581','58999','92427','74997',
                          '75196','18017','13660','34339','43881',
                          '75177','69616','46421','84329','37444',
                          '24357','28079','61774','59558','49549',
                          '22710','15028','42430','41774','97847',
                          '90166','13589','85938','67560','15572',
                          '87012','52341','41308','51936','12622',
                          '92491','10931','33233','88567','85455',
                          '25006','54564','56082','34692','55059',
                          '44972','40200','64012','58490')
     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 99 in stream 9 using template query8.tpl