0001 SELECT
0002 c_last_name,
0003 c_first_name,
0004 c_salutation,
0005 c_preferred_cust_flag,
0006 ss_ticket_number,
0007 cnt
0008 FROM
0009 (SELECT
0010 ss_ticket_number,
0011 ss_customer_sk,
0012 count(*) cnt
0013 FROM store_sales, date_dim, store, household_demographics
0014 WHERE store_sales.ss_sold_date_sk = date_dim.d_date_sk
0015 AND store_sales.ss_store_sk = store.s_store_sk
0016 AND store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
0017 AND (date_dim.d_dom BETWEEN 1 AND 3 OR date_dim.d_dom BETWEEN 25 AND 28)
0018 AND (household_demographics.hd_buy_potential = '>10000' OR
0019 household_demographics.hd_buy_potential = 'unknown')
0020 AND household_demographics.hd_vehicle_count > 0
0021 AND (CASE WHEN household_demographics.hd_vehicle_count > 0
0022 THEN household_demographics.hd_dep_count / household_demographics.hd_vehicle_count
0023 ELSE NULL
0024 END) > 1.2
0025 AND date_dim.d_year IN (1999, 1999 + 1, 1999 + 2)
0026 AND store.s_county IN
0027 ('Williamson County', 'Williamson County', 'Williamson County', 'Williamson County',
0028 'Williamson County', 'Williamson County', 'Williamson County', 'Williamson County')
0029 GROUP BY ss_ticket_number, ss_customer_sk) dn, customer
0030 WHERE ss_customer_sk = c_customer_sk
0031 AND cnt BETWEEN 15 AND 20
0032 ORDER BY c_last_name, c_first_name, c_salutation, c_preferred_cust_flag DESC
