-- start query 1 in stream 0 using template ../query_templates_qualified/query8.tpl
select  s_store_name
      ,sum(ss_net_profit)
 from store_sales
     ,date_dim
     ,store,
     (select ca_zip
     from (
      SELECT substr(ca_zip,1,5) ca_zip
      FROM customer_address
      WHERE substr(ca_zip,1,5) IN (
                          '24128','57834','13354','15734','78668',
                          '76232','62878','45375','63435','22245',
                          '65084','49130','40558','25733','15798',
                          '87816','81096','56458','35474','27156',
                          '83926','18840','28286','24676','37930',
                          '77556','27700','45266','94627','62971',
                          '20548','23470','47305','53535','21337',
                          '26231','50412','69399','17879','51622',
                          '43848','21195','83921','15559','67853',
                          '15126','16021','26233','53268','10567',
                          '91137','76107','11101','59166','38415',
                          '61265','71954','15371','11928','15455',
                          '98294','68309','69913','59402','58263',
                          '25782','18119','35942','33282','42029',
                          '17920','98359','15882','45721','60279',
                          '18426','64544','25631','43933','37125',
                          '98235','10336','24610','68101','56240',
                          '40081','86379','44165','33515','88190',
                          '84093','27068','99076','36634','50308',
                          '28577','39736','33786','71286','26859',
                          '55565','98569','70738','19736','64457',
                          '17183','28915','26653','58058','89091',
                          '54601','24206','14328','55253','82136',
                          '67897','56529','72305','67473','62377',
                          '22752','57647','62496','41918','36233',
                          '86284','54917','22152','19515','63837',
                          '18376','42961','10144','36495','58078',
                          '38607','91110','64147','19430','17043',
                          '45200','63981','48425','22351','30010',
                          '21756','14922','14663','77191','60099',
                          '29741','36420','21076','91393','28810',
                          '96765','23006','18799','49156','98025',
                          '23932','67467','30450','50298','29178',
                          '89360','32754','63089','87501','87343',
                          '29839','30903','81019','18652','73273',
                          '25989','20260','68893','53179','30469',
                          '28898','31671','24996','18767','64034',
                          '91068','51798','51200','63193','39516',
                          '72550','72325','51211','23968','86057',
                          '10390','85816','45692','65164','21309',
                          '18845','68621','92712','68880','90257',
                          '47770','13955','70466','21286','67875',
                          '82636','36446','79994','72823','40162',
                          '41367','41766','22437','58470','11356',
                          '76638','68806','25280','67301','73650',
                          '86198','16725','38935','13394','61810',
                          '81312','15146','71791','31016','72013',
                          '37126','22744','73134','70372','30431',
                          '39192','35850','56571','67030','22461',
                          '88424','88086','14060','40604','19512',
                          '72175','51649','19505','24317','13375',
                          '81426','18270','72425','45748','55307',
                          '53672','52867','56575','39127','30625',
                          '10445','39972','74351','26065','83849',
                          '42666','96976','68786','77721','68908',
                          '66864','63792','51650','31029','26689',
                          '66708','11376','20004','31880','96451',
                          '41248','94898','18383','60576','38193',
                          '48583','13595','76614','24671','46820',
                          '82276','10516','11634','45549','88885',
                          '18842','90225','18906','13376','84935',
                          '78890','58943','15765','50016','69035',
                          '49448','39371','41368','33123','83144',
                          '14089','94945','73241','19769','47537',
                          '38122','28587','76698','22927','56616',
                          '34425','96576','78567','97789','94983',
                          '79077','57855','97189','46081','48033',
                          '19849','28488','28545','72151','69952',
                          '43285','26105','76231','15723','25486',
                          '39861','83933','75691','46136','61547',
                          '66162','25858','22246','51949','27385',
                          '77610','34322','51061','68100','61860',
                          '13695','44438','90578','96888','58048',
                          '99543','73171','56691','64528','56910',
                          '83444','30122','68014','14171','16807',
                          '83041','34102','51103','79777','17871',
                          '12305','22685','94167','28709','35258',
                          '57665','71256','57047','11489','31387',
                          '68341','78451','14867','25103','35458',
                          '25003','54364','73520','32213','35576')
     intersect
      select ca_zip
      from (SELECT substr(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 (substr(s_zip,1,2) = substr(V1.ca_zip,1,2))
 group by s_store_name
 order by s_store_name
 limit 100

-- end query 1 in stream 0 using template ../query_templates_qualified/query8.tpl