set enable_result_cache_for_session = off;
:EXPLAIN
-- start query 92 in stream 15 using template query8.tpl and seed 1082581137
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 (
                          '95225','95137','51997','80159','91538','17854',
                          '17385','17596','37338','12286','56828',
                          '86072','10368','65503','43484','16297',
                          '87341','39377','15381','58556','57110',
                          '76513','12722','40869','93286','16277',
                          '77654','95238','73574','89020','86996',
                          '18320','16473','45084','59490','45307',
                          '23022','32527','94324','52391','32500',
                          '58223','97507','21186','75307','42507',
                          '37251','32140','33447','18573','98597',
                          '23094','21637','44780','50413','75596',
                          '95457','81011','56416','45968','64946',
                          '66346','72899','79063','69401','70189',
                          '78564','40905','34816','22218','30879',
                          '14261','66190','30009','32932','50638',
                          '85735','52574','22780','58213','14762',
                          '53620','35754','43109','21753','54603',
                          '67393','10527','25068','17822','52592',
                          '24956','93746','99560','60698','88587',
                          '29702','44745','27288','10323','39432',
                          '11086','58573','83672','32265','29459',
                          '52579','43593','64926','26246','93686',
                          '76870','43157','77890','80541','39151',
                          '67660','70835','12821','24006','59596',
                          '97214','40037','74128','80458','63701',
                          '79189','14536','46592','36060','15416',
                          '95166','59485','13571','30203','68897',
                          '33814','82984','97775','93755','17301',
                          '50511','36720','35562','79418','70460',
                          '42928','61143','90828','95108','89140',
                          '23667','42304','57366','37438','56521',
                          '88119','44129','29187','83535','81937',
                          '65415','55063','49777','67740','49066',
                          '74019','83853','97203','73465','21995',
                          '53170','61131','38674','21145','58424',
                          '56077','63203','56770','25493','91526',
                          '58173','76288','26089','85669','96104',
                          '75290','89581','22657','27620','33035',
                          '91457','42038','59998','84071','18673',
                          '10966','82477','63918','36402','25630',
                          '43098','87938','96651','59745','77476',
                          '69055','20275','13987','83489','15440',
                          '47709','44680','78102','62395','81144',
                          '18304','76010','95385','95755','27236',
                          '20767','21422','79023','25644','81606',
                          '45752','24932','14495','34415','22432',
                          '17692','48552','55450','76858','97679',
                          '81913','44159','21527','61051','72457',
                          '15073','26679','81604','89093','82499',
                          '58111','54295','37024','64702','30943',
                          '29571','43252','27505','36210','44501',
                          '98547','23656','41746','21364','40671',
                          '74665','86422','17617','23929','86189',
                          '64660','56873','95683','68187','76145',
                          '81311','27649','98542','87545','67135',
                          '51838','91000','86523','84737','43589',
                          '93540','71733','30910','22026','84299',
                          '52714','59176','61529','71816','62681',
                          '95664','69308','83248','73842','66308',
                          '51174','12736','11277','66902','98337',
                          '90501','21711','22062','59715','10042',
                          '81213','82999','13574','77336','19180',
                          '52185','27626','26799','70566','77204',
                          '66916','81367','87491','82747','53008',
                          '75950','31950','41025','89598','14032',
                          '18527','55703','82244','44282','46694',
                          '32603','98521','89210','18092','83632',
                          '79549','43328','85079','70264','86585',
                          '43054','50169','27449','61968','76831',
                          '75733','66980','44762','91887','18662',
                          '82415','54815','31251','59121','51296',
                          '86373','54523','13413','80729','26190',
                          '66748','93192','60375','78323','36193',
                          '53368','26233','56384','85156','68828',
                          '28376','91002','49264','18085','73866',
                          '67327','31205','92284','52246','57508',
                          '40380','69664','45536','35238','34176',
                          '13785','93753','77497','30429','71296',
                          '94365','34380','28891','17089','26612',
                          '39512','64731','66844','26086')
     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 = 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 92 in stream 15 using template query8.tpl