| -- 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 |
