set enable_result_cache_for_session = off;
:EXPLAIN
-- start query 63 in stream 0 using template query8.tpl and seed 1516474902
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 (
                          '89825','29355','15432','73712','88542','32009',
                          '33786','80591','73206','33044','31723',
                          '62239','88215','24878','28618','13437',
                          '69209','51657','95483','29749','80252',
                          '20894','78704','97863','61577','29432',
                          '44311','94910','46255','59761','37879',
                          '20540','31865','13637','16761','36001',
                          '59491','14658','91362','95699','81271',
                          '62060','34101','92491','11072','27170',
                          '90868','39519','30090','43711','91521',
                          '53903','83227','50516','87666','11554',
                          '68071','31114','40340','82395','11172',
                          '35116','83154','28169','48711','64327',
                          '19606','80070','59580','11107','32987',
                          '53489','81151','67474','70209','33258',
                          '61864','49165','86442','37056','65541',
                          '66143','49162','24310','68631','21320',
                          '73499','90155','50760','15140','51973',
                          '82378','10475','17766','29801','98312',
                          '14341','12471','72074','17416','46740',
                          '26509','57796','30772','18737','54108',
                          '83702','57530','77053','25378','15975',
                          '10896','76722','31568','83478','13791',
                          '56592','43160','10423','76873','23483',
                          '96229','22330','51348','70435','47799',
                          '27945','13740','98131','62362','35230',
                          '33835','42245','23990','12224','34880',
                          '73620','16412','39175','34015','91701',
                          '30964','32271','20592','89709','58825',
                          '19638','10453','56952','39117','32832',
                          '75437','14840','14628','43927','84718',
                          '61855','11973','17263','50127','91665',
                          '14521','38266','17322','19456','52255',
                          '58754','50607','35054','65152','55459',
                          '86233','24432','96425','28046','27900',
                          '25098','96764','25457','44157','21052',
                          '53575','19452','37115','32629','42039',
                          '76198','44471','27816','25110','56006',
                          '79288','97945','64587','47716','38634',
                          '25843','22033','43583','43140','14957',
                          '83934','92837','35145','43143','12552',
                          '43175','85510','52306','74817','75847',
                          '98180','22849','99143','82431','27881',
                          '69988','27890','63063','45280','79910',
                          '35108','92729','80708','10239','92510',
                          '58808','86416','93473','32785','86916',
                          '69659','26497','39927','58623','47648',
                          '65934','54179','28320','17353','60683',
                          '97186','27134','98353','31464','19022',
                          '77452','57328','57697','53554','46604',
                          '91150','99043','28119','48470','47735',
                          '80243','65621','87192','76282','96559',
                          '80358','77483','69518','78343','52689',
                          '50184','14415','47279','90666','61272',
                          '30128','20964','47789','94002','19199',
                          '76135','40252','27093','71208','95141',
                          '24653','37584','62918','44958','50829',
                          '96821','77020','20639','67448','63851',
                          '10660','89804','37007','88921','12648',
                          '26559','46469','51883','22238','56874',
                          '67840','30902','41560','51101','36319',
                          '32351','40232','77581','96250','76256',
                          '34300','95004','54057','88536','79846',
                          '56711','80899','42316','12407','77400',
                          '23773','21054','30540','44689','55732',
                          '19554','71946','48264','94269','73474',
                          '29871','18714','48718','58228','22331',
                          '68956','46518','38667','21031','41672',
                          '56864','56371','15917','59914','11813',
                          '11383','18925','93733','15504','69266',
                          '89999','86768','67780','82339','45900',
                          '17126','72204','19572','28100','89550',
                          '24895','34501','65294','74499','93821',
                          '80000','33298','45513','16399','70315',
                          '68309','88149','40016','25022','56795',
                          '68808','12343','74964','18444','31883',
                          '75380','73826','64449','15345','52859',
                          '35426','36681','18824','17722','93897',
                          '34087','91632','21327','67892','13283',
                          '23569','98078','29269','22759')
     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 = 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 63 in stream 0 using template query8.tpl