set enable_result_cache_for_session = off;
:EXPLAIN
-- start query 76 in stream 8 using template query8.tpl and seed 954210651
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 (
                          '94035','41945','81261','82607','29089','58117',
                          '33261','91161','19294','38471','48412',
                          '10925','37595','55892','95783','12202',
                          '59024','33133','41482','97001','54334',
                          '77962','31725','93717','63695','39380',
                          '64306','44211','26642','10911','37547',
                          '96992','53356','15259','78768','59209',
                          '25845','10008','20323','31758','60437',
                          '37254','20822','45365','86612','61892',
                          '63320','81243','38713','79707','55658',
                          '64000','31096','97878','57694','68082',
                          '79928','74459','96200','86419','96787',
                          '21435','38413','77161','32450','27741',
                          '18915','78336','19393','10949','85677',
                          '85328','19705','89727','78440','19707',
                          '44070','10647','81307','11922','45207',
                          '88904','49685','56703','87017','72857',
                          '45161','36790','81762','43469','54449',
                          '82450','25799','79358','96195','78034',
                          '21953','90899','64968','38712','53919',
                          '52140','28646','88338','69689','47071',
                          '22478','50296','34035','61497','11926',
                          '74156','83482','33334','38531','96819',
                          '75692','56968','28533','44480','59761',
                          '55062','38817','81835','37297','20195',
                          '41024','93357','28461','94702','32543',
                          '27329','18032','96651','18171','46880',
                          '50116','42658','63386','49700','72998',
                          '43477','62093','65902','99670','62257',
                          '93113','27224','25143','64066','31167',
                          '78509','57163','17086','65649','14036',
                          '49903','12877','65945','64578','28441',
                          '65859','25049','69991','80377','16649',
                          '36627','25043','90321','12931','45106',
                          '63623','20629','78423','52404','57003',
                          '50881','46413','58664','72868','23443',
                          '72803','39243','57182','14707','54958',
                          '81499','58874','86998','81604','16544',
                          '80147','99889','50444','85384','64144',
                          '56547','16044','21525','73739','85481',
                          '43604','33525','61711','58628','23156',
                          '71118','39210','83971','97145','35082',
                          '14395','71777','86020','44045','88046',
                          '76033','95705','80136','60875','77001',
                          '40919','20624','79642','26105','59824',
                          '52164','95586','50673','46562','46479',
                          '42940','51253','74671','13668','85286',
                          '95823','67761','42473','29489','19388',
                          '16637','87531','13566','73753','90212',
                          '89970','11557','68967','84547','63481',
                          '83559','16164','47344','66365','66201',
                          '54949','20869','27618','80566','70699',
                          '56241','46287','47228','60690','42887',
                          '14730','12684','99032','52179','80696',
                          '52950','25565','92783','81309','22063',
                          '64305','78030','67768','91593','10780',
                          '89711','33812','39202','94395','45941',
                          '94311','51771','78340','97614','54808',
                          '82630','44932','93383','55169','31310',
                          '24469','74438','73562','36855','92583',
                          '20558','68511','95265','99714','96024',
                          '12739','64935','82570','34861','40412',
                          '32269','90240','94205','76970','36033',
                          '31555','93694','27757','57250','79509',
                          '24455','32883','18833','28260','89242',
                          '44328','28614','34695','88695','93135',
                          '89484','64199','48988','27075','47131',
                          '32265','71377','26641','42472','56080',
                          '15140','17547','90983','78473','49049',
                          '62536','96682','79671','79329','59307',
                          '44071','89231','29747','43087','49296',
                          '39066','97171','26964','56711','55954',
                          '37665','59147','45023','81745','77550',
                          '25016','14007','75836','32300','22232',
                          '90519','31921','75766','78237','13447',
                          '66920','49563','81204','68184','76192',
                          '57206','85706','44002','96305','61788',
                          '91082','52282','49495','22990','99444',
                          '63674','36365','39105','77320','22391',
                          '51246','88345','40215','70078')
     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 = 1999
  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 76 in stream 8 using template query8.tpl