0001
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 2
0024 and (household_demographics.hd_buy_potential = '>10000'
0025 or household_demographics.hd_buy_potential = 'Unknown')
0026 and household_demographics.hd_vehicle_count > 0
0027 and case when household_demographics.hd_vehicle_count > 0 then household_demographics.hd_dep_count / household_demographics.hd_vehicle_count else null end > 1
0028 and date_dim.d_year in (1998, 1998 + 1, 1998 + 2)
0029 and store.s_county in ('Fairfield County','Ziebach County','Bronx County','Barrow County')
0030
0031 and ss_sold_date_sk in (2450815, 2450816, 2450846, 2450847, 2450874, 2450875, 2450905, 2450906, 2450935, 2450936, 2450966, 2450967,
0032 2450996, 2450997, 2451027, 2451028, 2451058, 2451059, 2451088, 2451089, 2451119, 2451120, 2451149,
0033 2451150, 2451180, 2451181, 2451211, 2451212, 2451239, 2451240, 2451270, 2451271, 2451300, 2451301,
0034 2451331, 2451332, 2451361, 2451362, 2451392, 2451393, 2451423, 2451424, 2451453, 2451454, 2451484,
0035 2451485, 2451514, 2451515, 2451545, 2451546, 2451576, 2451577, 2451605, 2451606, 2451636, 2451637,
0036 2451666, 2451667, 2451697, 2451698, 2451727, 2451728, 2451758, 2451759, 2451789, 2451790, 2451819,
0037 2451820, 2451850, 2451851, 2451880, 2451881)
0038
0039 group by
0040 ss_ticket_number,
0041 ss_customer_sk
0042 ) dj,
0043 customer
0044 where
0045 ss_customer_sk = c_customer_sk
0046 and cnt between 1 and 5
0047 order by
0048 cnt desc
0049