This topic describes how TPC-DS generates test SQL statements and how to resolve issues that may occur when statements are executed.
Generate test SQL statements
The following section describes how to use dsdgen to generate test SQL statements.
# ./dsqgen -input ../query_templates/templates.lst -directory ../query_templates -output ./sql.ansi/ -DIALECT ansi -LOG ./sql.ansi/ansi.log
qgen2 Query Generator (Version 2.13.0)
Copyright Transaction Processing Performance Council (TPC) 2001 - 2020
Warning: This scale factor is valid for QUALIFICATION ONLY
# ls sql.ansi/
ansi.log query_0.sql
The following sample code demonstrates an error message that may be returned when you use dsqgen and how to resolve the error.
- Error message
ERROR: Substitution'_END' is used before being initialized at line 63 in ../query_templates/query1.tpl
- Solution
- Add define _END = "" to the end of every queryN.tpl statement in the /query_templates
directory.
for i in `ls query*tpl` do echo $i; echo "define _END = \"\";" >> $i done
Modify SQL statements to make them compatible with AnalyticDB for MySQL
The SQL statements generated by using dsqgen are of the ANSI standard. The query_0.sql file contains the SQL statements used to perform the test. You must modify the statements to make them compatible with AnalyticDB for MySQL. Otherwise, the following error message may be returned.
- Error message (date function)
ERROR 1815 (HY000) at line 198: [20002, 2021012119323119216818102003453445250] : line 1:1: syntax error, expect ), actual IDENTIFIER pos 540, line 14, column 58, token IDENTIFIER days
- Solution
-
Modify the date functions in the query_0.sql file.
Modify the date function in the following SQL statement:
(cast('2002-08-04' as date) + 30 days)
Replace the preceding code block with the following code block:
date_add(cast('2002-08-04' as date),interval 30 day)
You can also use the following code block:
(cast('2002-08-04' as date) + INTERVAL 30 day)
Modify the date function in the following SQL statement:
(cast ('2002-05-18' as date) - 30 days)
Replace the preceding code block with the following code block:
date_sub(cast ('2002-05-18' as date),interval 30 day)
You can also use the following code block:
(cast('2002-08-04' as date) - INTERVAL 30 day)
- Error message
ERROR 1815 (HY000) at line 656: [20030, 2021012113343619216818102003453356248] : line 0:1: Column 'item_id' is ambiguous
- Solution
- Change
order by item_id
toorder by table alias.item_id
- Error message
ERROR 1815 (HY000): [20038, 2021012115471319216818102003453386566] : line 0:1: Column 'c_last_review_date_sk' cannot be resolved
- Solution
- Change
c_last_review_date_sk
toc_last_review_date
. This is because the table does not contain thec_last_review_date_sk
field. - Error message
ERROR 1815 (HY000) at line 523: [20030, 2021012118235219216818102003453429557] : line 0:1: Column 'd_week_seq' is ambiguous
- Solution
select ...., d1.d_week_seq,.... where d1.d_week_seq = d2.d_week_seq ....... order by total_cnt desc, i_item_desc, w_warehouse_name, d_week_seq;
Replace the last line of the preceding code block with the following code block:
order by total_cnt desc, i_item_desc, w_warehouse_name, d1.d_week_seq;
Execute SQL query statements
In the performance test, 99 SQL query statements are executed. The following section provides 10 of the 99 SQL statements. For information about all the SQL query statements, contact technical support.
- SQL1
WITH customer_total_return AS ( SELECT sr_customer_sk AS ctr_customer_sk, sr_store_sk AS ctr_store_sk, SUM(SR_RETURN_AMT) AS ctr_total_return FROM store_returns, date_dim WHERE sr_returned_date_sk = d_date_sk AND d_year = 2000 GROUP BY sr_customer_sk, sr_store_sk ) SELECT c_customer_id FROM customer_total_return ctr1, store, customer WHERE ctr1.ctr_total_return > ( SELECT AVG(ctr_total_return) * 1.2 FROM customer_total_return ctr2 WHERE ctr1.ctr_store_sk = ctr2.ctr_store_sk ) AND s_store_sk = ctr1.ctr_store_sk AND s_state = 'TN' AND ctr1.ctr_customer_sk = c_customer_sk ORDER BY c_customer_id LIMIT 100;
- SQL2
WITH wscs AS ( SELECT sold_date_sk, sales_price FROM ( SELECT ws_sold_date_sk AS sold_date_sk, ws_ext_sales_price AS sales_price FROM web_sales UNION ALL SELECT cs_sold_date_sk AS sold_date_sk, cs_ext_sales_price AS sales_price FROM catalog_sales ) ), wswscs AS ( SELECT d_week_seq, SUM(CASE WHEN d_day_name = 'Sunday' THEN sales_price ELSE NULL END) AS sun_sales, SUM(CASE WHEN d_day_name = 'Monday' THEN sales_price ELSE NULL END) AS mon_sales , SUM(CASE WHEN d_day_name = 'Tuesday' THEN sales_price ELSE NULL END) AS tue_sales, SUM(CASE WHEN d_day_name = 'Wednesday' THEN sales_price ELSE NULL END) AS wed_sales , SUM(CASE WHEN d_day_name = 'Thursday' THEN sales_price ELSE NULL END) AS thu_sales, SUM(CASE WHEN d_day_name = 'Friday' THEN sales_price ELSE NULL END) AS fri_sales , SUM(CASE WHEN d_day_name = 'Saturday' THEN sales_price ELSE NULL END) AS sat_sales FROM wscs, date_dim WHERE d_date_sk = sold_date_sk GROUP BY d_week_seq ) SELECT d_week_seq1, round(sun_sales1 / sun_sales2, 2) , round(mon_sales1 / mon_sales2, 2) , round(tue_sales1 / tue_sales2, 2) , round(wed_sales1 / wed_sales2, 2) , round(thu_sales1 / thu_sales2, 2) , round(fri_sales1 / fri_sales2, 2) , round(sat_sales1 / sat_sales2, 2) FROM ( SELECT wswscs.d_week_seq AS d_week_seq1, sun_sales AS sun_sales1, mon_sales AS mon_sales1, tue_sales AS tue_sales1, wed_sales AS wed_sales1 , thu_sales AS thu_sales1, fri_sales AS fri_sales1, sat_sales AS sat_sales1 FROM wswscs, date_dim WHERE date_dim.d_week_seq = wswscs.d_week_seq AND d_year = 2001 ) y, ( SELECT wswscs.d_week_seq AS d_week_seq2, sun_sales AS sun_sales2, mon_sales AS mon_sales2, tue_sales AS tue_sales2, wed_sales AS wed_sales2 , thu_sales AS thu_sales2, fri_sales AS fri_sales2, sat_sales AS sat_sales2 FROM wswscs, date_dim WHERE date_dim.d_week_seq = wswscs.d_week_seq AND d_year = 2001 + 1 ) z WHERE d_week_seq1 = d_week_seq2 - 53 ORDER BY d_week_seq1;
- SQL3
SELECT dt.d_year, item.i_brand_id AS brand_id, item.i_brand AS brand, SUM(ss_ext_sales_price) AS sum_agg FROM date_dim dt, store_sales, item WHERE dt.d_date_sk = store_sales.ss_sold_date_sk AND store_sales.ss_item_sk = item.i_item_sk AND item.i_manufact_id = 128 AND dt.d_moy = 11 GROUP BY dt.d_year, item.i_brand, item.i_brand_id ORDER BY dt.d_year, sum_agg DESC, brand_id LIMIT 100;
- SQL4
WITH year_total AS ( SELECT c_customer_id AS customer_id, c_first_name AS customer_first_name, c_last_name AS customer_last_name, c_preferred_cust_flag AS customer_preferred_cust_flag, c_birth_country AS customer_birth_country , c_login AS customer_login, c_email_address AS customer_email_address, d_year AS dyear , SUM((ss_ext_list_price - ss_ext_wholesale_cost - ss_ext_discount_amt + ss_ext_sales_price) / 2) AS year_total , 's' AS sale_type FROM customer, store_sales, date_dim WHERE c_customer_sk = ss_customer_sk AND ss_sold_date_sk = d_date_sk GROUP BY c_customer_id, c_first_name, c_last_name, c_preferred_cust_flag, c_birth_country, c_login, c_email_address, d_year UNION ALL SELECT c_customer_id AS customer_id, c_first_name AS customer_first_name, c_last_name AS customer_last_name, c_preferred_cust_flag AS customer_preferred_cust_flag, c_birth_country AS customer_birth_country , c_login AS customer_login, c_email_address AS customer_email_address, d_year AS dyear , SUM((cs_ext_list_price - cs_ext_wholesale_cost - cs_ext_discount_amt + cs_ext_sales_price) / 2) AS year_total , 'c' AS sale_type FROM customer, catalog_sales, date_dim WHERE c_customer_sk = cs_bill_customer_sk AND cs_sold_date_sk = d_date_sk GROUP BY c_customer_id, c_first_name, c_last_name, c_preferred_cust_flag, c_birth_country, c_login, c_email_address, d_year UNION ALL SELECT c_customer_id AS customer_id, c_first_name AS customer_first_name, c_last_name AS customer_last_name, c_preferred_cust_flag AS customer_preferred_cust_flag, c_birth_country AS customer_birth_country , c_login AS customer_login, c_email_address AS customer_email_address, d_year AS dyear , SUM((ws_ext_list_price - ws_ext_wholesale_cost - ws_ext_discount_amt + ws_ext_sales_price) / 2) AS year_total , 'w' AS sale_type FROM customer, web_sales, date_dim WHERE c_customer_sk = ws_bill_customer_sk AND ws_sold_date_sk = d_date_sk GROUP BY c_customer_id, c_first_name, c_last_name, c_preferred_cust_flag, c_birth_country, c_login, c_email_address, d_year ) SELECT t_s_secyear.customer_id, t_s_secyear.customer_first_name, t_s_secyear.customer_last_name, t_s_secyear.customer_preferred_cust_flag FROM year_total t_s_firstyear, year_total t_s_secyear, year_total t_c_firstyear, year_total t_c_secyear, year_total t_w_firstyear, year_total t_w_secyear WHERE t_s_secyear.customer_id = t_s_firstyear.customer_id AND t_s_firstyear.customer_id = t_c_secyear.customer_id AND t_s_firstyear.customer_id = t_c_firstyear.customer_id AND t_s_firstyear.customer_id = t_w_firstyear.customer_id AND t_s_firstyear.customer_id = t_w_secyear.customer_id AND t_s_firstyear.sale_type = 's' AND t_c_firstyear.sale_type = 'c' AND t_w_firstyear.sale_type = 'w' AND t_s_secyear.sale_type = 's' AND t_c_secyear.sale_type = 'c' AND t_w_secyear.sale_type = 'w' AND t_s_firstyear.dyear = 2001 AND t_s_secyear.dyear = 2001 + 1 AND t_c_firstyear.dyear = 2001 AND t_c_secyear.dyear = 2001 + 1 AND t_w_firstyear.dyear = 2001 AND t_w_secyear.dyear = 2001 + 1 AND t_s_firstyear.year_total > 0 AND t_c_firstyear.year_total > 0 AND t_w_firstyear.year_total > 0 AND CASE WHEN t_c_firstyear.year_total > 0 THEN t_c_secyear.year_total / t_c_firstyear.year_total ELSE NULL END > CASE WHEN t_s_firstyear.year_total > 0 THEN t_s_secyear.year_total / t_s_firstyear.year_total ELSE NULL END AND CASE WHEN t_c_firstyear.year_total > 0 THEN t_c_secyear.year_total / t_c_firstyear.year_total ELSE NULL END > CASE WHEN t_w_firstyear.year_total > 0 THEN t_w_secyear.year_total / t_w_firstyear.year_total ELSE NULL END ORDER BY t_s_secyear.customer_id, t_s_secyear.customer_first_name, t_s_secyear.customer_last_name, t_s_secyear.customer_preferred_cust_flag LIMIT 100;
- SQL5
WITH ssr AS ( SELECT s_store_id, SUM(sales_price) AS sales, SUM(profit) AS profit , SUM(return_amt) AS RETURNS, SUM(net_loss) AS profit_loss FROM ( SELECT ss_store_sk AS store_sk, ss_sold_date_sk AS date_sk, ss_ext_sales_price AS sales_price, ss_net_profit AS profit, CAST(0 AS decimal(7, 2)) AS return_amt , CAST(0 AS decimal(7, 2)) AS net_loss FROM store_sales UNION ALL SELECT sr_store_sk AS store_sk, sr_returned_date_sk AS date_sk, CAST(0 AS decimal(7, 2)) AS sales_price, CAST(0 AS decimal(7, 2)) AS profit, sr_return_amt AS return_amt , sr_net_loss AS net_loss FROM store_returns ) salesreturns, date_dim, store WHERE date_sk = d_date_sk AND d_date BETWEEN CAST('2000-08-23' AS date) AND CAST('2000-08-23' AS date) + INTERVAL '14' DAY AND store_sk = s_store_sk GROUP BY s_store_id ), csr AS ( SELECT cp_catalog_page_id, SUM(sales_price) AS sales, SUM(profit) AS profit , SUM(return_amt) AS RETURNS, SUM(net_loss) AS profit_loss FROM ( SELECT cs_catalog_page_sk AS page_sk, cs_sold_date_sk AS date_sk, cs_ext_sales_price AS sales_price, cs_net_profit AS profit, CAST(0 AS decimal(7, 2)) AS return_amt , CAST(0 AS decimal(7, 2)) AS net_loss FROM catalog_sales UNION ALL SELECT cr_catalog_page_sk AS page_sk, cr_returned_date_sk AS date_sk, CAST(0 AS decimal(7, 2)) AS sales_price, CAST(0 AS decimal(7, 2)) AS profit, cr_return_amount AS return_amt , cr_net_loss AS net_loss FROM catalog_returns ) salesreturns, date_dim, catalog_page WHERE date_sk = d_date_sk AND d_date BETWEEN CAST('2000-08-23' AS date) AND CAST('2000-08-23' AS date) + INTERVAL '14' DAY AND page_sk = cp_catalog_page_sk GROUP BY cp_catalog_page_id ), wsr AS ( SELECT web_site_id, SUM(sales_price) AS sales, SUM(profit) AS profit , SUM(return_amt) AS RETURNS, SUM(net_loss) AS profit_loss FROM ( SELECT ws_web_site_sk AS wsr_web_site_sk, ws_sold_date_sk AS date_sk, ws_ext_sales_price AS sales_price, ws_net_profit AS profit, CAST(0 AS decimal(7, 2)) AS return_amt , CAST(0 AS decimal(7, 2)) AS net_loss FROM web_sales UNION ALL SELECT ws_web_site_sk AS wsr_web_site_sk, wr_returned_date_sk AS date_sk, CAST(0 AS decimal(7, 2)) AS sales_price, CAST(0 AS decimal(7, 2)) AS profit, wr_return_amt AS return_amt , wr_net_loss AS net_loss FROM web_returns LEFT JOIN web_sales ON wr_item_sk = ws_item_sk AND wr_order_number = ws_order_number ) salesreturns, date_dim, web_site WHERE date_sk = d_date_sk AND d_date BETWEEN CAST('2000-08-23' AS date) AND CAST('2000-08-23' AS date) + INTERVAL '14' DAY AND wsr_web_site_sk = web_site_sk GROUP BY web_site_id ) SELECT channel, id, SUM(sales) AS sales , SUM(RETURNS) AS RETURNS, SUM(profit) AS profit FROM ( SELECT 'store channel' AS channel, 'store' OR s_store_id AS id, sales, RETURNS , profit - profit_loss AS profit FROM ssr UNION ALL SELECT 'catalog channel' AS channel, 'catalog_page' OR cp_catalog_page_id AS id, sales, RETURNS , profit - profit_loss AS profit FROM csr UNION ALL SELECT 'web channel' AS channel, 'web_site' OR web_site_id AS id, sales, RETURNS , profit - profit_loss AS profit FROM wsr ) x GROUP BY channel, id WITH ROLLUP ORDER BY channel, id LIMIT 100;
- SQL6
SELECT a.ca_state AS STATE, COUNT(*) AS cnt FROM customer_address a, customer c, store_sales s, date_dim d, item i WHERE a.ca_address_sk = c.c_current_addr_sk AND c.c_customer_sk = s.ss_customer_sk AND s.ss_sold_date_sk = d.d_date_sk AND s.ss_item_sk = i.i_item_sk AND d.d_month_seq = ( SELECT DISTINCT d_month_seq FROM date_dim WHERE d_year = 2001 AND d_moy = 1 ) AND i.i_current_price > 1.2 * ( SELECT AVG(j.i_current_price) FROM item j WHERE j.i_category = i.i_category ) GROUP BY a.ca_state HAVING COUNT(*) >= 10 ORDER BY cnt, a.ca_state LIMIT 100;
- SQL7
SELECT i_item_id, AVG(ss_quantity) AS agg1, AVG(ss_list_price) AS agg2 , AVG(ss_coupon_amt) AS agg3, AVG(ss_sales_price) AS agg4 FROM store_sales, customer_demographics, date_dim, item, promotion WHERE ss_sold_date_sk = d_date_sk AND ss_item_sk = i_item_sk AND ss_cdemo_sk = cd_demo_sk AND ss_promo_sk = p_promo_sk AND cd_gender = 'M' AND cd_marital_status = 'S' AND cd_education_status = 'College' AND (p_channel_email = 'N' OR p_channel_event = 'N') AND d_year = 2000 GROUP BY i_item_id ORDER BY i_item_id LIMIT 100;
- SQL8
SELECT s_store_name, SUM(ss_net_profit) FROM store_sales, date_dim, store, ( SELECT ca_zip FROM ( SELECT substr(ca_zip, 1, 5) AS 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) AS ca_zip , COUNT(*) AS 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;
- SQL9
SELECT CASE WHEN ( SELECT COUNT(*) FROM store_sales WHERE ss_quantity BETWEEN 1 AND 20 ) > 74129 THEN ( SELECT AVG(ss_ext_discount_amt) FROM store_sales WHERE ss_quantity BETWEEN 1 AND 20 ) ELSE ( SELECT AVG(ss_net_paid) FROM store_sales WHERE ss_quantity BETWEEN 1 AND 20 ) END AS bucket1 , CASE WHEN ( SELECT COUNT(*) FROM store_sales WHERE ss_quantity BETWEEN 21 AND 40 ) > 122840 THEN ( SELECT AVG(ss_ext_discount_amt) FROM store_sales WHERE ss_quantity BETWEEN 21 AND 40 ) ELSE ( SELECT AVG(ss_net_paid) FROM store_sales WHERE ss_quantity BETWEEN 21 AND 40 ) END AS bucket2 , CASE WHEN ( SELECT COUNT(*) FROM store_sales WHERE ss_quantity BETWEEN 41 AND 60 ) > 56580 THEN ( SELECT AVG(ss_ext_discount_amt) FROM store_sales WHERE ss_quantity BETWEEN 41 AND 60 ) ELSE ( SELECT AVG(ss_net_paid) FROM store_sales WHERE ss_quantity BETWEEN 41 AND 60 ) END AS bucket3 , CASE WHEN ( SELECT COUNT(*) FROM store_sales WHERE ss_quantity BETWEEN 61 AND 80 ) > 10097 THEN ( SELECT AVG(ss_ext_discount_amt) FROM store_sales WHERE ss_quantity BETWEEN 61 AND 80 ) ELSE ( SELECT AVG(ss_net_paid) FROM store_sales WHERE ss_quantity BETWEEN 61 AND 80 ) END AS bucket4 , CASE WHEN ( SELECT COUNT(*) FROM store_sales WHERE ss_quantity BETWEEN 81 AND 100 ) > 165306 THEN ( SELECT AVG(ss_ext_discount_amt) FROM store_sales WHERE ss_quantity BETWEEN 81 AND 100 ) ELSE ( SELECT AVG(ss_net_paid) FROM store_sales WHERE ss_quantity BETWEEN 81 AND 100 ) END AS bucket5 FROM reason WHERE r_reason_sk = 1;
- SQL10
SELECT cd_gender, cd_marital_status, cd_education_status, COUNT(*) AS cnt1 , cd_purchase_estimate, COUNT(*) AS cnt2, cd_credit_rating , COUNT(*) AS cnt3, cd_dep_count , COUNT(*) AS cnt4, cd_dep_employed_count , COUNT(*) AS cnt5, cd_dep_college_count , COUNT(*) AS cnt6 FROM customer c, customer_address ca, customer_demographics WHERE c.c_current_addr_sk = ca.ca_address_sk AND ca_county IN ('Rush County', 'Toole County', 'Jefferson County', 'Dona Ana County', 'La Porte County') AND cd_demo_sk = c.c_current_cdemo_sk AND EXISTS ( SELECT * FROM store_sales, date_dim WHERE c.c_customer_sk = ss_customer_sk AND ss_sold_date_sk = d_date_sk AND d_year = 2002 AND d_moy BETWEEN 1 AND 1 + 3 ) AND (EXISTS ( SELECT * FROM web_sales, date_dim WHERE c.c_customer_sk = ws_bill_customer_sk AND ws_sold_date_sk = d_date_sk AND d_year = 2002 AND d_moy BETWEEN 1 AND 1 + 3 ) OR EXISTS ( SELECT * FROM catalog_sales, date_dim WHERE c.c_customer_sk = cs_ship_customer_sk AND cs_sold_date_sk = d_date_sk AND d_year = 2002 AND d_moy BETWEEN 1 AND 1 + 3 )) GROUP BY cd_gender, cd_marital_status, cd_education_status, cd_purchase_estimate, cd_credit_rating, cd_dep_count, cd_dep_employed_count, cd_dep_college_count ORDER BY cd_gender, cd_marital_status, cd_education_status, cd_purchase_estimate, cd_credit_rating, cd_dep_count, cd_dep_employed_count, cd_dep_college_count LIMIT 100;