Back to home page

OSCL-LXR

 
 

    


0001 -- start query 73 in stream 0 using template query73.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 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     -- partition key filter
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     --and ss_sold_date_sk between 2451180 and 2451269 -- partition key filter (3 months)
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 -- end query 73 in stream 0 using template query73.tpl