resources/tpcds-modifiedQueries/q68.sql

0001 -- start query 68 in stream 0 using template query68.tpl
0002 -- changed to match exact same partitions in original query
0003 select
0004   c_last_name,
0005   c_first_name,
0006   ca_city,
0007   bought_city,
0008   ss_ticket_number,
0009   extended_price,
0010   extended_tax,
0011   list_price
0012 from
0013   (select
0014     ss_ticket_number,
0015     ss_customer_sk,
0016     ca_city bought_city,
0017     sum(ss_ext_sales_price) extended_price,
0018     sum(ss_ext_list_price) list_price,
0019     sum(ss_ext_tax) extended_tax
0020   from
0021     store_sales,
0022     date_dim,
0023     store,
0024     household_demographics,
0025     customer_address
0026   where
0027     store_sales.ss_sold_date_sk = date_dim.d_date_sk
0028     and store_sales.ss_store_sk = store.s_store_sk
0029     and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk
0030     and store_sales.ss_addr_sk = customer_address.ca_address_sk
0031     and date_dim.d_dom between 1 and 2
0032     and (household_demographics.hd_dep_count = 5
0033       or household_demographics.hd_vehicle_count = 3)
0034     and date_dim.d_year in (1999, 1999 + 1, 1999 + 2)
0035     and store.s_city in ('Midway', 'Fairview')
0036     -- partition key filter
0037     and ss_sold_date_sk in (2451180, 2451181, 2451211, 2451212, 2451239, 2451240, 2451270, 2451271, 2451300, 2451301, 2451331,
0038                              2451332, 2451361, 2451362, 2451392, 2451393, 2451423, 2451424, 2451453, 2451454, 2451484, 2451485,
0039                              2451514, 2451515, 2451545, 2451546, 2451576, 2451577, 2451605, 2451606, 2451636, 2451637, 2451666,
0040                              2451667, 2451697, 2451698, 2451727, 2451728, 2451758, 2451759, 2451789, 2451790, 2451819, 2451820,
0041                              2451850, 2451851, 2451880, 2451881, 2451911, 2451912, 2451942, 2451943, 2451970, 2451971, 2452001,
0042                              2452002, 2452031, 2452032, 2452062, 2452063, 2452092, 2452093, 2452123, 2452124, 2452154, 2452155,
0043                              2452184, 2452185, 2452215, 2452216, 2452245, 2452246)
0044     --and ss_sold_date_sk between 2451180 and 2451269 -- partition key filter (3 months)
0045     --and d_date between '1999-01-01' and '1999-03-31'
0046   group by
0047     ss_ticket_number,
0048     ss_customer_sk,
0049     ss_addr_sk,
0050     ca_city
0051   ) dn,
0052   customer,
0053   customer_address current_addr
0054 where
0055   ss_customer_sk = c_customer_sk
0056   and customer.c_current_addr_sk = current_addr.ca_address_sk
0057   and current_addr.ca_city <> bought_city
0058 order by
0059   c_last_name,
0060   ss_ticket_number
0061 limit 100
0062 -- end query 68 in stream 0 using template query68.tpl