set enable_result_cache_for_session = off;
:EXPLAIN
-- start query 27 in stream 1 using template query8.tpl and seed 1521324121
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 (
                          '67973','52982','89781','14426','97660','64260',
                          '61386','43933','21096','54309','30347',
                          '93327','17658','39342','24335','86959',
                          '95689','72982','98412','75192','19091',
                          '92314','56228','90387','24970','96118',
                          '80961','13659','89406','90195','48052',
                          '43267','60954','34870','14222','23559',
                          '60245','94466','34192','81509','73689',
                          '87344','18909','58089','99899','40566',
                          '31329','65684','40450','87480','97072',
                          '67516','40085','71692','70601','96624',
                          '64142','96183','68901','77860','57353',
                          '33865','48399','30497','24322','73065',
                          '41140','16789','46236','52323','68622',
                          '66227','70387','45613','33207','39769',
                          '58071','16089','84289','24008','71725',
                          '34543','45961','22363','92804','10977',
                          '55707','67265','54928','40001','59945',
                          '95301','15147','92433','61076','91025',
                          '67615','19137','91125','15324','78558',
                          '85183','77978','22432','65051','54571',
                          '42462','61150','81910','32801','65799',
                          '41782','73170','23753','17699','78158',
                          '87021','53953','68600','91029','84917',
                          '12354','30111','24176','12649','47086',
                          '16758','46150','84990','69200','17164',
                          '93093','32174','48480','50889','32912',
                          '21840','41913','88535','20622','73625',
                          '37039','98184','26228','98145','73600',
                          '97472','45672','15803','57379','69691',
                          '31107','77753','75581','28637','56562',
                          '95548','66133','17148','89335','53924',
                          '64621','23405','84476','45489','72225',
                          '57337','11373','54467','67397','53871',
                          '93451','77848','33594','96158','95038',
                          '73330','22984','18139','41491','35854',
                          '17229','10825','47996','49376','41605',
                          '99067','41342','90670','29853','51220',
                          '25951','61114','88377','41003','70605',
                          '31547','63871','74365','97468','91447',
                          '22166','76099','51931','29176','74478',
                          '79487','70935','66453','28446','55994',
                          '71598','66241','61732','55308','85778',
                          '19855','53944','49382','74140','67347',
                          '92844','71978','97627','75670','27180',
                          '51959','88378','14435','82648','19361',
                          '87422','27518','32452','27031','70770',
                          '48623','56720','85394','64214','39265',
                          '29122','17719','65002','38832','72484',
                          '38525','94606','22167','15566','80155',
                          '76476','40651','30263','97186','89050',
                          '67860','54819','24197','12990','38489',
                          '32692','27676','85915','21901','60463',
                          '31334','17523','28272','74431','32647',
                          '32161','74268','40184','66670','36485',
                          '93136','48065','54128','97747','57601',
                          '38435','30445','23626','22823','95213',
                          '93661','99591','76953','12206','70334',
                          '54974','93859','40034','19538','39175',
                          '72469','83564','79446','69521','57883',
                          '89598','97393','66432','54796','71669',
                          '67702','99342','62322','64900','64637',
                          '93056','22705','49925','26119','51936',
                          '30864','69368','65180','39755','19081',
                          '71270','80005','54022','79582','88697',
                          '97552','60243','61617','78693','63036',
                          '16585','10784','31123','55660','60205',
                          '21919','64084','41194','60793','99210',
                          '75675','83352','28701','49752','96186',
                          '84157','58083','83173','11503','78390',
                          '45536','25795','65139','68947','27484',
                          '46003','10258','45454','69323','63668',
                          '80812','13112','27776','14818','81942',
                          '56462','46449','66922','21987','29609',
                          '83168','28783','86908','68985','33049',
                          '16358','47495','39653','34331','97339',
                          '53931','31386','44254','95742','48612',
                          '69248','11900','12796','27485','36565',
                          '65303','55496','60011','80004','35632',
                          '46611','24178','99052','23520')
     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 27 in stream 1 using template query8.tpl