Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $
You can buy this project and download/modify it how often you want.
tpcds.query08.sql Maven / Gradle / Ivy
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', '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 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
