set enable_result_cache_for_session = off;
:EXPLAIN
-- start query 24 in stream 14 using template query8.tpl and seed 1135143165
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 (
                          '89971','20394','66481','35321','16896','22622',
                          '48629','74880','12904','88459','10393',
                          '74681','62220','24583','73222','92901',
                          '43101','63444','80666','91595','43667',
                          '33424','73718','65874','68820','33466',
                          '96133','46557','66810','18941','48621',
                          '13554','58925','19296','99765','15002',
                          '71155','81409','87110','10085','32686',
                          '22580','68921','21633','72200','80273',
                          '43179','78420','26838','61955','98691',
                          '49429','43228','36240','63366','78903',
                          '47665','94495','61070','22538','34633',
                          '79041','87127','36434','92149','48467',
                          '95115','94888','98012','40608','46206',
                          '56762','92748','47027','25643','13040',
                          '38494','38639','85689','37749','52115',
                          '15409','79616','48352','44461','65386',
                          '70684','69889','20736','74541','51890',
                          '55412','25451','47167','43454','58328',
                          '75877','88227','50165','41129','44402',
                          '15010','64622','26540','68362','89031',
                          '55263','56756','22975','45726','28778',
                          '25173','38547','27925','56074','53747',
                          '86706','78373','87783','99956','71240',
                          '80090','16174','73399','53709','94328',
                          '21476','17468','99534','58155','34215',
                          '16956','31241','36740','67418','18802',
                          '46051','60666','45816','47914','71827',
                          '62842','62050','97875','49927','53372',
                          '47832','14491','75201','19869','63793',
                          '36435','90622','93259','99208','20472',
                          '73047','67153','37081','60037','55088',
                          '24917','40398','30091','59442','79314',
                          '37097','16497','61623','18358','11419',
                          '98289','24828','59263','50569','86031',
                          '55415','33361','83877','37806','61045',
                          '28403','39412','14027','87952','56920',
                          '76427','80000','21623','68745','22576',
                          '11085','13336','11670','52842','97269',
                          '24126','31112','24338','66773','14388',
                          '78728','19032','19996','38001','44174',
                          '97563','93434','75833','89614','43065',
                          '65096','74885','33026','83596','62766',
                          '43663','13747','67390','70517','97074',
                          '72654','43685','93574','72422','65839',
                          '51567','93562','78887','72842','40419',
                          '35695','18479','75746','18852','67388',
                          '15744','93230','36754','45551','40804',
                          '88434','39432','76940','63976','91301',
                          '18485','97579','50546','66651','76245',
                          '41446','80409','20044','59362','89772',
                          '91742','84578','57104','57496','30540',
                          '96448','41025','99042','15361','31561',
                          '22275','30558','81453','20405','60606',
                          '94195','95673','95992','74916','23905',
                          '20212','95203','63942','38306','42423',
                          '91380','72053','73265','16633','85877',
                          '20131','61731','81191','81545','85430',
                          '54455','21323','51818','46142','82902',
                          '27289','71432','99494','12864','38469',
                          '88526','61133','77641','37607','23406',
                          '74872','74898','82298','65316','38614',
                          '96155','96160','64388','33311','28603',
                          '82989','96622','83255','47102','67340',
                          '85611','67768','39831','28579','85693',
                          '22497','64536','17292','36424','10488',
                          '79760','21303','51407','64105','32224',
                          '95245','96865','32950','46585','26378',
                          '83348','95944','20663','49978','54052',
                          '98696','87869','79277','13472','99532',
                          '17065','58213','68263','62523','95050',
                          '26060','91869','79210','80864','24555',
                          '42933','17856','92267','97600','67766',
                          '35993','87453','57138','92451','98698',
                          '83398','82431','55339','92210','21835',
                          '55996','20312','40390','48026','70286',
                          '24011','15964','66382','43568','46690',
                          '44846','23059','56870','68093','36021',
                          '57758','11424','33073','47030','31311',
                          '41754','76164','21114','37684')
     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 24 in stream 14 using template query8.tpl