resources/tpcds-modifiedQueries/q34.sql

0001 -- start query 34 in stream 0 using template query34.tpl
0002 select
0003   c_last_name,
0004   c_first_name,
0005   c_salutation,
0006   c_preferred_cust_flag,
0007   ss_ticket_number,
0008   cnt
0009 from
0010   (select
0011     ss_ticket_number,
0012     ss_customer_sk,
0013     count(*) cnt
0014   from
0015     store_sales,
0016     date_dim,
0017     store,
0018     household_demographics
0019   where
0020     store_sales.ss_sold_date_sk = date_dim.d_date_sk
0021     and store_sales.ss_store_sk = store.s_store_sk
0022     and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
0023     and (date_dim.d_dom between 1 and 3
0024       or date_dim.d_dom between 25 and 28)
0025     and (household_demographics.hd_buy_potential = '>10000'
0026       or household_demographics.hd_buy_potential = 'Unknown')
0027     and household_demographics.hd_vehicle_count > 0
0028     and (case when household_demographics.hd_vehicle_count > 0 then household_demographics.hd_dep_count / household_demographics.hd_vehicle_count else null end) > 1.2
0029     and date_dim.d_year in (1998, 1998 + 1, 1998 + 2)
0030     and store.s_county in ('Saginaw County', 'Sumner County', 'Appanoose County', 'Daviess County', 'Fairfield County', 'Raleigh County', 'Ziebach County', 'Williamson County')
0031     and ss_sold_date_sk between 2450816 and 2451910 -- partition key filter
0032   group by
0033     ss_ticket_number,
0034     ss_customer_sk
0035   ) dn,
0036   customer
0037 where
0038   ss_customer_sk = c_customer_sk
0039   and cnt between 15 and 20
0040 order by
0041   c_last_name,
0042   c_first_name,
0043   c_salutation,
0044   c_preferred_cust_flag desc
0045 -- end query 34 in stream 0 using template query34.tpl