| ==== |
| ---- QUERY: TPCDS-Q72 |
| select i_item_desc |
| ,w_warehouse_name |
| ,d1.d_week_seq |
| ,sum(case when p_promo_sk is null then 1 else 0 end) no_promo |
| ,sum(case when p_promo_sk is not null then 1 else 0 end) promo |
| ,count(*) total_cnt |
| from catalog_sales |
| join inventory on (cs_item_sk = inv_item_sk) |
| join warehouse on (w_warehouse_sk=inv_warehouse_sk) |
| join item on (i_item_sk = cs_item_sk) |
| join customer_demographics on (cs_bill_cdemo_sk = cd_demo_sk) |
| join household_demographics on (cs_bill_hdemo_sk = hd_demo_sk) |
| join date_dim d1 on (cs_sold_date_sk = d1.d_date_sk) |
| join date_dim d2 on (inv_date_sk = d2.d_date_sk) |
| join date_dim d3 on (cs_ship_date_sk = d3.d_date_sk) |
| left outer join promotion on (cs_promo_sk=p_promo_sk) |
| left outer join catalog_returns on (cr_item_sk = cs_item_sk and cr_order_number = cs_order_number) |
| where d1.d_week_seq = d2.d_week_seq |
| and inv_quantity_on_hand < cs_quantity |
| and cast(d3.d_date as timestamp) > (cast(d1.d_date as timestamp) + interval 5 days) |
| and hd_buy_potential = '>10000' |
| and d1.d_year = 1999 |
| and cd_marital_status = 'D' |
| group by i_item_desc,w_warehouse_name,d1.d_week_seq |
| order by total_cnt desc, i_item_desc, w_warehouse_name, d_week_seq |
| limit 100; |
| ---- RESULTS |
| 'Best possible ages tell together new, st','Conventional childr',5213,0,2,2 |
| 'Closed, good condition','Doors canno',5169,0,2,2 |
| 'Departments make once again police. Very acceptable results call still extended, known ends; relationships shoot strangely. Acids shall discharge in order ethnic, ric','NULL',5168,0,2,2 |
| 'Futures should enjoy able galleries. Late blue tickets pass longer urgently dead types. Shoulders will see rigidly institutions. Other con','Conventional childr',5210,0,2,2 |
| 'Good, open studies dream more; industrial, social organisations could understand recently quick tall theories. Lines must answer functions; subtle factor','Important issues liv',5214,0,2,2 |
| 'Hard sudden aspects shall not commemorate about a functions. Western, british cases see here churches. Stairs a','Doors canno',5212,0,2,2 |
| 'High, essential groups should not weigh more other years; there different papers could announce; large departments c','Important issues liv',5198,0,2,2 |
| 'Just young partie','Important issues liv',5213,0,2,2 |
| 'Levels undermine unfortunately efficient weeks','NULL',5210,0,2,2 |
| 'New, full rises drive however. Legs retain often in a women. Remaining, parliamentary miles may help nevertheless rather full pupils. Glad crimes might','Bad cards must make.',5217,0,2,2 |
| 'Provisions go too. Sad others contain italian branches. Keys k','Bad cards must make.',5213,0,2,2 |
| 'Provisions go too. Sad others contain italian branches. Keys k','Doors canno',5213,0,2,2 |
| 'Shortly desperate nat','Conventional childr',5210,0,2,2 |
| 'True tears continue currently pale, close men. Soon medical numbers might not take other, ','Important issues liv',5199,0,2,2 |
| 'White employees name; figures feed sure. Speeches can achieve. Extremely final seats may exist too all religious studies. Medite','Bad cards must make.',5168,0,2,2 |
| 'Words use up a documents. Collections may','Conventional childr',5168,0,2,2 |
| 'Words use up a documents. Collections may','Important issues liv',5168,0,2,2 |
| 'A little average flames ought to break old, unique men. Things select often red, economic others. Hands will lift sufficiently; german, proper sections worry perhaps for the po','NULL',5212,0,1,1 |
| 'Abilities would not require almost; local','Bad cards must make.',5197,0,1,1 |
| 'Able','Conventional childr',5177,0,1,1 |
| 'Able hands help however inevitable policemen. Far true matters will not forgive never hands. Absolutely french events stop now. Well able procedures unde','Conventional childr',5195,0,1,1 |
| 'Able hands help however inevitable policemen. Far true matters will not forgive never hands. Absolutely french events stop now. Well able procedures unde','Doors canno',5195,0,1,1 |
| 'Able, alternative police shall not give so other complaints. There complex ','NULL',5212,0,1,1 |
| 'Able, long mammals can want new, serious years. Questions would not cope again mainly unable contributions. Less responsible shelves lose records; leading, similar ','Important issues liv',5170,0,1,1 |
| 'Able, main parties think really. Resources arrive only independent, old representations. Small, double advantages ','Doors canno',5213,0,1,1 |
| 'Able, main parties think really. Resources arrive only independent, old representations. Small, double advantages ','Important issues liv',5180,0,1,1 |
| 'About international concentrations could avoid then alone apparent activities; inadequate, mediterranean days get eve','Important issues liv',5215,0,1,1 |
| 'About natural economie','Bad cards must make.',5202,0,1,1 |
| 'About other levels should proceed certainly fine, severe facts.','Important issues liv',5210,0,1,1 |
| 'About working feelings could produce only types. Electoral, new visitors will not make more afraid, large tr','NULL',5197,0,1,1 |
| 'Absolutely','Bad cards must make.',5203,0,1,1 |
| 'Absolutely','Doors canno',5203,0,1,1 |
| 'Absolutely ','NULL',5206,0,1,1 |
| 'Absolutely front men turn spatial hours. Good, free sales used to marry outside appropriate ships. Noble men sa','NULL',5207,0,1,1 |
| 'Absolutely old payments will b','Doors canno',5216,0,1,1 |
| 'Accused men cannot increase e','Important issues liv',5207,0,1,1 |
| 'Acids grab below previous standards. Ever large metals will come on a articles. Underlying stories protect at last. Reasonable directions believe rather due to a','Bad cards must make.',5169,0,1,1 |
| 'Active plants need necessary, widespread roads. Best back visits hold regularly fresh friend','Conventional childr',5209,0,1,1 |
| 'Active plants need necessary, widespread roads. Best back visits hold regularly fresh friend','NULL',5209,0,1,1 |
| 'Active windows shall not find small, relig','Conventional childr',5203,0,1,1 |
| 'Actively different proceedings light yet so similar houses. Good circumstances shall not take only levels. Then moral pounds will clean very only national organisations. ','NULL',5183,0,1,1 |
| 'Activities say. Right lips resort current techniques. Regional, possible daughters might not present changes; students can notice ridiculous, l','Conventional childr',5192,0,1,1 |
| 'Acts','Bad cards must make.',5218,0,1,1 |
| 'Acts','Conventional childr',5218,0,1,1 |
| 'Actual, grey hands giv','NULL',5214,0,1,1 |
| 'Acute, important performances afford. New, nuclear men used to assess again small results.','NULL',5187,0,1,1 |
| 'Added, similar grounds spend also concrete terms. Fellow, mass','Important issues liv',5206,0,1,1 |
| 'Additional, terrible characters shall examine. Ago lexical conditions get into a weeks. Barely trying results perform still hot men. Great kinds end also committees. Police should live only on the ','Doors canno',5216,0,1,1 |
| 'Additional, terrible characters shall examine. Ago lexical conditions get into a weeks. Barely trying results perform still hot men. Great kinds end also committees. Police should live only on the ','Important issues liv',5214,0,1,1 |
| 'Adequate things reassure unknown legs. Old, possible bishops shall locate else during a companies; bitter, alone ','Bad cards must make.',5206,0,1,1 |
| 'Adequately unemployed aspects ought to keep on a years. Years get somewhere sometimes late examples; laws must shape determined stones. Recently real decisions may cost now other female thousands. ','Conventional childr',5214,0,1,1 |
| 'Adequately unemployed aspects ought to keep on a years. Years get somewhere sometimes late examples; laws must shape determined stones. Recently real decisions may cost now other female thousands. ','Important issues liv',5214,0,1,1 |
| 'Adults throw close recent women. Orange, guilty libraries let earnings. Initiatives ought to walk. Simple, successful states might work eventually full orders. Formerly very ','Bad cards must make.',5206,0,1,1 |
| 'Adults throw close recent women. Orange, guilty libraries let earnings. Initiatives ought to walk. Simple, successful states might work eventually full orders. Formerly very ','NULL',5206,0,1,1 |
| 'Advanced, certain fields miss electronically for the books. Open measures match therefore s','Doors canno',5196,0,1,1 |
| 'Advanced, certain fields miss electronically for the books. Open measures match therefore s','Important issues liv',5210,0,1,1 |
| 'Advantages go small. Organisers could make of course like a problems; probably reasonable humans shall attract categories. Agencies will enable much heavy matters. Stair','Doors canno',5186,0,1,1 |
| 'Advantages go small. Organisers could make of course like a problems; probably reasonable humans shall attract categories. Agencies will enable much heavy matters. Stair','Important issues liv',5186,0,1,1 |
| 'Advantages go small. Organisers could make of course like a problems; probably reasonable humans shall attract categories. Agencies will enable much heavy matters. Stair','NULL',5206,0,1,1 |
| 'Afraid years suspend much building','NULL',5171,0,1,1 |
| 'Afraid, grey officers mean costly institutions. Societi','Conventional childr',5199,0,1,1 |
| 'Afraid, old meals will get chronic, strong applicants. Arms could look with a needs. Hence wor','Important issues liv',5210,0,1,1 |
| 'Afraid, southern problems need according to a dec','Conventional childr',5207,0,1,1 |
| 'Afraid, southern problems need according to a dec','Important issues liv',5207,0,1,1 |
| 'Again available bags breathe good circumstances. Thus final cases must ','Bad cards must make.',5214,0,1,1 |
| 'Again available bags breathe good circumstances. Thus final cases must ','Conventional childr',5218,0,1,1 |
| 'Again judicial colours may blame fully british strange groups. Rules shall cover probably participants. W','NULL',5214,0,1,1 |
| 'Again small deaths could flou','Bad cards must make.',5175,0,1,1 |
| 'Agencies shall not consider false in a others. Obviously interesting authorities come anyway men. Small, ','Important issues liv',5206,0,1,1 |
| 'Agents see companies. Weekly clergy might not enable always mere studies. Men throw possible relations. Then static rights wr','Conventional childr',5209,0,1,1 |
| 'Ago foreign writings leave; even considerable artists let fully then minimal workers. Clear ministers keep. Specifically dry men increase central tests. Living, alternative meanings ought','Important issues liv',5180,0,1,1 |
| 'Ago regional objects finish courts. Large, serio','Bad cards must make.',5187,0,1,1 |
| 'Agricultural, difficult engines marry according to the things; instances shall not go however quietly statutory images. Still sharp patients work no doubt producers. Magazines ','Doors canno',5210,0,1,1 |
| 'Agricultural, selective groups follow much worthwhile panels. Fully sim','Bad cards must make.',5207,0,1,1 |
| 'Aims play already','Doors canno',5198,0,1,1 |
| 'Al','NULL',5167,0,1,1 |
| 'All ','Bad cards must make.',5175,0,1,1 |
| 'All ','Important issues liv',5210,0,1,1 |
| 'All numerous reasons explain upper teachers; necessary, inte','Bad cards must make.',5181,0,1,1 |
| 'All realistic employees should attempt all only expert parties. Complete days cannot come as possible rules. Normal candidates would not pay there improved, o','Doors canno',5204,0,1,1 |
| 'All right deliberate difficulties wait still between a seats; final, actual jobs may mee','Bad cards must make.',5212,0,1,1 |
| 'All right deliberate difficulties wait still between a seats; final, actual jobs may mee','Conventional childr',5174,0,1,1 |
| 'All right used men must demand. Visual companies take entirely inhabitants; forward common hands hear here local customers. So traditional questions shal','Doors canno',5188,0,1,1 |
| 'Alm','Important issues liv',5204,0,1,1 |
| 'Almost armed animals will maintain always pure, professional days; differe','Conventional childr',5175,0,1,1 |
| 'Almost armed animals will maintain always pure, professional days; differe','Doors canno',5175,0,1,1 |
| 'Almost armed animals will maintain always pure, professional days; differe','Important issues liv',5175,0,1,1 |
| 'Almost low provisions suggest to','Conventional childr',5211,0,1,1 |
| 'Almost separate f','Bad cards must make.',5215,0,1,1 |
| 'Almost separate f','NULL',5215,0,1,1 |
| 'Alone days must undertake children; pages will face cases. Days fit especially black standards. Certain, involved numbers may not intend high, ','Conventional childr',5176,0,1,1 |
| 'Alone relevant nights pretend so complete years. Currently new unions make horizontally bills. Most political troops could give most possible, australian elements; flowers shall recall most pop','Important issues liv',5212,0,1,1 |
| 'Alone working-class dates open from a issues. Most european concessions will not tell personal areas; central, poor officials might not act ','Doors canno',5197,0,1,1 |
| 'Alone, small conditions get either likely companies. Inner, long-term patients make hot rebels. Procedures see fi','Important issues liv',5215,0,1,1 |
| 'Already european mothers ought to impose big ever fixed parents. Dominant groups say even. Here basic weeks set as winners. Modern, young prayers release very environ','Doors canno',5215,0,1,1 |
| 'Also f','Doors canno',5192,0,1,1 |
| 'Also good subjects cannot clean then then difficult gaps. Good, sorry methods divert perhaps. Whit','Bad cards must make.',5211,0,1,1 |
| 'Also public waves will not bomb yesterday methods. Eyes ','NULL',5183,0,1,1 |
| 'Also real details used to travel for the articles. A bit anonymous factors aim there early, clean standards. Normal grounds weaken in a times; significant wheels cannot help black studies. Right l','Important issues liv',5199,0,1,1 |
| 'Also silent nurses find also fully mental priorities. Savings shall bring naturally silent e','Conventional childr',5168,0,1,1 |
| ---- TYPES |
| STRING, STRING, INT, BIGINT, BIGINT, BIGINT |
| ==== |