set enable_result_cache_for_session = off;
:EXPLAIN
-- start query 62 in stream 13 using template query8.tpl and seed 336901415
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 (
                          '96712','47121','75662','71847','88263','70183',
                          '76988','45884','98313','69799','11935',
                          '19163','46673','79359','69509','83059',
                          '39318','74365','20720','85870','15734',
                          '26943','36478','16604','63389','84715',
                          '50762','15293','61984','83380','59583',
                          '74809','91700','56075','67999','48037',
                          '48719','96395','67929','62008','16919',
                          '59051','56640','32596','60084','52572',
                          '68501','21439','61208','82726','26010',
                          '75688','56226','34131','40959','86934',
                          '18965','76737','29256','87328','74639',
                          '20980','60459','68426','81272','26174',
                          '49475','92069','17408','20992','84376',
                          '66381','89515','29936','54599','39789',
                          '85496','98958','48919','31386','58995',
                          '69786','82873','29837','91027','91504',
                          '38739','56622','78490','91107','12017',
                          '61937','10743','88808','57016','65671',
                          '79727','18449','23153','36163','41034',
                          '14983','22315','89811','66717','30008',
                          '85269','69188','89015','40608','32869',
                          '11338','40654','72457','71347','52757',
                          '94175','92072','19356','66231','32061',
                          '36874','15715','24722','54550','52745',
                          '42505','72562','16333','24472','58945',
                          '98265','69965','51577','47229','48744',
                          '39797','97546','63458','51960','31994',
                          '18730','27472','52420','33475','86635',
                          '40748','95430','88716','20415','95358',
                          '19728','20749','75003','77298','47311',
                          '90079','71913','35489','59488','14757',
                          '56483','14607','78032','88158','14768',
                          '54143','31209','44102','13303','14474',
                          '71298','75142','81253','16331','12635',
                          '89692','35614','72679','17481','62379',
                          '18747','53146','81681','29020','41556',
                          '10543','35223','46142','76599','57391',
                          '75695','33423','10094','55869','21027',
                          '66801','57826','69785','83096','94826',
                          '26373','60131','15437','11426','21206',
                          '35360','14551','51017','10369','25283',
                          '58385','83704','21191','73820','46045',
                          '47449','12458','79597','59118','21884',
                          '24593','18797','11493','97396','44507',
                          '79390','80308','99372','22143','93046',
                          '74652','53365','82415','63362','37885',
                          '67898','46451','53614','32784','10135',
                          '58837','59307','43279','75694','45287',
                          '80816','10261','57228','97169','75896',
                          '19903','45060','67756','55256','82168',
                          '92752','69578','29658','60200','55586',
                          '39485','69794','47458','56755','84328',
                          '60956','76004','91854','98086','46010',
                          '36602','87110','30204','58121','24409',
                          '51470','33180','54426','22674','96159',
                          '75809','43334','73464','76295','18145',
                          '50741','54775','24014','27458','87069',
                          '68659','60264','33812','15942','34139',
                          '76236','48191','18699','57213','31099',
                          '48315','65516','25379','41543','24770',
                          '12460','95190','18021','93957','98365',
                          '14026','97102','10489','60699','63408',
                          '29334','63594','56816','55151','63630',
                          '95369','81492','83674','54499','35585',
                          '81491','94284','45883','50152','60976',
                          '43747','32486','95029','13515','32414',
                          '70820','94594','27776','96164','42698',
                          '27128','50148','70004','10258','83733',
                          '12329','86836','17046','37383','14986',
                          '80700','58126','59229','36245','82471',
                          '35894','84002','40891','57243','51413',
                          '19664','59295','29743','73147','56966',
                          '11469','10706','30983','16160','25507',
                          '57234','94917','79645','57958','86950',
                          '23066','20294','19796','84671','98369',
                          '50815','34867','12499','15058','88541',
                          '87305','17307','67596','58291','65402',
                          '10249','31301','96473','87120','62290',
                          '99320','27305','95064','43084')
     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 = 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 62 in stream 13 using template query8.tpl
