tpcds.query60.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.
with ss as (
select
i_item_id,sum(ss_ext_sales_price) total_sales
from
store_sales,
date_dim,
customer_address,
item
where
i_item_id in (select
i_item_id
from
item
where i_category in ('Music'))
and ss_item_sk = i_item_sk
and ss_sold_date_sk = d_date_sk
and d_year = 1998
and d_moy = 9
and ss_addr_sk = ca_address_sk
and ca_gmt_offset = -5
group by i_item_id),
cs as (
select
i_item_id,sum(cs_ext_sales_price) total_sales
from
catalog_sales,
date_dim,
customer_address,
item
where
i_item_id in (select
i_item_id
from
item
where i_category in ('Music'))
and cs_item_sk = i_item_sk
and cs_sold_date_sk = d_date_sk
and d_year = 1998
and d_moy = 9
and cs_bill_addr_sk = ca_address_sk
and ca_gmt_offset = -5
group by i_item_id),
ws as (
select
i_item_id,sum(ws_ext_sales_price) total_sales
from
web_sales,
date_dim,
customer_address,
item
where
i_item_id in (select
i_item_id
from
item
where i_category in ('Music'))
and ws_item_sk = i_item_sk
and ws_sold_date_sk = d_date_sk
and d_year = 1998
and d_moy = 9
and ws_bill_addr_sk = ca_address_sk
and ca_gmt_offset = -5
group by i_item_id)
select
i_item_id
,sum(total_sales) total_sales
from (select * from ss
union all
select * from cs
union all
select * from ws) tmp1
group by i_item_id
order by i_item_id
,total_sales
limit 100