set enable_result_cache_for_session = off;
:EXPLAIN
-- start query 17 in stream 11 using template query8.tpl and seed 1206281418
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 (
                          '86229','83086','36488','83768','95520','24279',
                          '91969','63942','17870','12730','59140',
                          '38499','84955','54630','80452','10297',
                          '23079','11041','16543','39423','79323',
                          '33074','15019','31815','85918','18896',
                          '62211','71347','30982','48148','10358',
                          '42105','82289','47354','18995','16693',
                          '89047','27561','32119','64315','65545',
                          '54723','12540','82336','12305','22427',
                          '32547','93551','10569','49400','10001',
                          '23810','79009','37711','36916','62994',
                          '92058','53853','47523','52944','13089',
                          '32524','55755','96620','23426','74559',
                          '87055','11195','52933','59024','30690',
                          '29036','14529','61946','83471','95228',
                          '34293','57935','91869','99813','19994',
                          '70018','47537','78396','53326','79007',
                          '16547','15543','78470','28914','54435',
                          '79517','58677','95609','47402','63677',
                          '94578','73231','82293','65701','96868',
                          '66947','86445','15278','21994','57795',
                          '43374','90156','15162','85726','46134',
                          '71613','89752','46223','21051','67115',
                          '76957','33161','61368','82197','35671',
                          '18702','19389','54811','30095','14287',
                          '42348','55108','59264','37181','51311',
                          '10451','76200','21352','26800','51495',
                          '44000','28092','39036','23555','47544',
                          '31624','14315','72504','29772','81035',
                          '85643','63538','98597','66279','15991',
                          '13278','23845','96435','62995','39130',
                          '64522','40068','85408','70392','15314',
                          '49393','76414','30833','68612','37770',
                          '96157','41343','74990','37894','85755',
                          '95595','23876','77521','97626','89521',
                          '82312','97361','35269','32424','34871',
                          '73779','17672','99428','70015','68265',
                          '33632','30041','12962','49219','70669',
                          '96021','68566','45173','34839','86581',
                          '85302','85771','37668','40591','62135',
                          '87766','73656','82468','17078','61984',
                          '72354','79767','80276','64837','37573',
                          '14266','47260','35630','75579','91681',
                          '27657','11745','71353','16033','62643',
                          '94527','36823','81140','82332','50914',
                          '21757','82044','12940','21571','70443',
                          '44458','28013','61861','97174','97062',
                          '31721','39695','87422','23299','11345',
                          '47804','57145','11091','94192','26388',
                          '20382','64353','17725','62979','29625',
                          '85164','34034','56355','98954','71141',
                          '28700','20241','63355','75661','88219',
                          '57854','42544','30862','57360','81982',
                          '41636','24622','46625','97743','19335',
                          '55389','44615','50897','20589','52036',
                          '69390','87539','25015','75484','49932',
                          '42342','51324','11695','83715','11482',
                          '37291','75573','19562','65001','88466',
                          '42044','38528','83049','41855','43519',
                          '13526','50298','99149','88819','19273',
                          '60777','13469','91415','90709','40216',
                          '40133','87156','18281','41151','92948',
                          '64213','66840','38004','97501','63393',
                          '19937','93930','36034','62548','43903',
                          '28784','69993','68513','90120','67520',
                          '82094','92709','12324','12156','33482',
                          '73072','62183','40141','15056','68350',
                          '91439','11494','67318','84711','77319',
                          '68016','83335','15222','41552','69668',
                          '86083','94900','72207','26487','82693',
                          '92096','81937','75221','27956','60215',
                          '18622','73021','39322','57767','32936',
                          '46361','90775','55215','78936','23713',
                          '23208','41061','38782','35134','52477',
                          '90238','17776','80417','91105','99869',
                          '37113','75308','24752','79931','51703',
                          '91986','28935','76855','21457','92266',
                          '62562','79762','14223','27468','85577',
                          '44237','91176','10510','16887','89262',
                          '56448','88189','90909','17368')
     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 = 2001
  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 17 in stream 11 using template query8.tpl