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