tpcds.query08.sql Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of fastsql Show documentation
Show all versions of fastsql Show documentation
An JDBC datasource implementation.
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