resources/tpcds-v2.7.0/q35.sql

0001 SELECT
0002   -- select list of q35 in TPCDS v1.4 is below:
0003   -- ca_state,
0004   -- cd_gender,
0005   -- cd_marital_status,
0006   -- count(*) cnt1,
0007   -- min(cd_dep_count),
0008   -- max(cd_dep_count),
0009   -- avg(cd_dep_count),
0010   -- cd_dep_employed_count,
0011   -- count(*) cnt2,
0012   -- min(cd_dep_employed_count),
0013   -- max(cd_dep_employed_count),
0014   -- avg(cd_dep_employed_count),
0015   -- cd_dep_college_count,
0016   -- count(*) cnt3,
0017   -- min(cd_dep_college_count),
0018   -- max(cd_dep_college_count),
0019   -- avg(cd_dep_college_count)
0020   ca_state,
0021   cd_gender,
0022   cd_marital_status,
0023   cd_dep_count,
0024   count(*) cnt1,
0025   avg(cd_dep_count),
0026   max(cd_dep_count),
0027   sum(cd_dep_count),
0028   cd_dep_employed_count,
0029   count(*) cnt2,
0030   avg(cd_dep_employed_count),
0031   max(cd_dep_employed_count),
0032   sum(cd_dep_employed_count),
0033   cd_dep_college_count,
0034   count(*) cnt3,
0035   avg(cd_dep_college_count),
0036   max(cd_dep_college_count),
0037   sum(cd_dep_college_count)
0038 FROM
0039   customer c, customer_address ca, customer_demographics
0040 WHERE
0041   c.c_current_addr_sk = ca.ca_address_sk AND
0042     cd_demo_sk = c.c_current_cdemo_sk AND
0043     exists(SELECT *
0044            FROM store_sales, date_dim
0045            WHERE c.c_customer_sk = ss_customer_sk AND
0046              ss_sold_date_sk = d_date_sk AND
0047              d_year = 2002 AND
0048              d_qoy < 4) AND
0049     (exists(SELECT *
0050             FROM web_sales, date_dim
0051             WHERE c.c_customer_sk = ws_bill_customer_sk AND
0052               ws_sold_date_sk = d_date_sk AND
0053               d_year = 2002 AND
0054               d_qoy < 4) OR
0055       exists(SELECT *
0056              FROM catalog_sales, date_dim
0057              WHERE c.c_customer_sk = cs_ship_customer_sk AND
0058                cs_sold_date_sk = d_date_sk AND
0059                d_year = 2002 AND
0060                d_qoy < 4))
0061 GROUP BY ca_state, cd_gender, cd_marital_status, cd_dep_count,
0062   cd_dep_employed_count, cd_dep_college_count
0063 ORDER BY ca_state, cd_gender, cd_marital_status, cd_dep_count,
0064   cd_dep_employed_count, cd_dep_college_count
0065 LIMIT 100