set enable_result_cache_for_session = off;
:EXPLAIN
-- start query 44 in stream 16 using template query8.tpl and seed 1644459424
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 (
                          '68121','60942','14950','27019','77643','85696',
                          '60269','57042','34678','50832','41909',
                          '74809','12792','31689','91285','50639',
                          '99009','95952','29600','15877','95561',
                          '32849','99433','17884','48860','63280',
                          '27264','83887','53093','79641','38781',
                          '52328','76832','36945','76061','69720',
                          '78438','20096','44110','31856','50943',
                          '63364','88193','40162','17963','90981',
                          '59254','49136','82801','38285','30588',
                          '51982','73981','27368','45212','35067',
                          '93581','44897','88911','57659','16949',
                          '18012','75938','37284','35192','96976',
                          '12997','94636','29566','99823','16739',
                          '30283','18478','11330','30080','82645',
                          '29038','52285','96772','23392','14591',
                          '99037','89233','41664','25563','91534',
                          '11466','42058','53129','52109','99458',
                          '69841','25783','36761','95430','84455',
                          '87524','89190','41063','79199','53980',
                          '29621','95388','83184','98912','21935',
                          '31818','11902','16154','18508','13325',
                          '96193','88093','58743','93560','18700',
                          '41145','98295','40762','71210','15491',
                          '79469','31608','54353','21568','84167',
                          '60961','62809','35512','74620','31161',
                          '17085','44753','76615','61021','86792',
                          '64323','97951','81436','87670','10334',
                          '44421','68826','92300','51058','23163',
                          '95519','47513','21022','89027','41501',
                          '46968','69860','44152','99031','96277',
                          '13450','55849','70529','73904','46776',
                          '89336','43442','45052','92255','19674',
                          '27947','37100','32117','34841','50852',
                          '24716','32720','17297','11755','66447',
                          '20721','26702','56273','41206','67741',
                          '45435','36003','41865','37762','47104',
                          '21889','29898','26671','76363','48787',
                          '60958','83930','29509','10232','96944',
                          '84662','90143','91481','35478','72655',
                          '28687','54709','47408','58245','37420',
                          '97146','60856','53977','55553','38465',
                          '13154','34754','82260','10497','83285',
                          '43680','85001','20719','67896','61748',
                          '84066','82516','70058','72785','85688',
                          '59465','45163','47272','54032','54554',
                          '21810','48919','67465','52297','69889',
                          '28615','56845','28048','62893','83254',
                          '31186','99437','31090','91410','85724',
                          '96582','51966','18190','12724','58710',
                          '21682','56568','70569','97785','74211',
                          '45988','34675','86611','24199','61323',
                          '19441','42758','96973','96634','76728',
                          '49042','64640','75077','62730','49753',
                          '31054','29920','47399','17544','33903',
                          '56090','83496','49472','52132','27456',
                          '46183','92721','38121','75543','76756',
                          '49509','20728','98586','67158','14756',
                          '58291','43324','67117','14595','11936',
                          '65255','78169','18796','96005','98350',
                          '79191','57783','15693','59921','40712',
                          '16456','49929','69068','89724','16680',
                          '26160','98547','48495','46847','94756',
                          '10246','67047','32553','12966','70920',
                          '58883','11859','29280','65052','30380',
                          '66317','49298','93976','67906','87722',
                          '73009','21397','75514','51233','94562',
                          '73291','11663','92960','71078','42336',
                          '39010','64546','68398','73799','29145',
                          '41425','83730','16302','31012','53668',
                          '48430','23620','75199','34541','88541',
                          '95558','72679','19681','44366','24868',
                          '40928','80194','87894','28756','96809',
                          '50634','12409','19556','12234','50231',
                          '37491','89576','46522','75872','30570',
                          '69622','10772','45112','16712','90818',
                          '41377','17095','35634','70342','36815',
                          '81753','99678','83620','30329','83227',
                          '30202','94786','72405','49890','73210',
                          '42770','20054','89478','63186')
     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 = 2002
  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 44 in stream 16 using template query8.tpl