| ==== |
| ---- QUERY: TPCDS-Q62 |
| select |
| substr(w_warehouse_name,1,20) |
| ,sm_type |
| ,web_name |
| ,sum(case when (ws_ship_date_sk - ws_sold_date_sk <= 30 ) then 1 else 0 end) as "30 days" |
| ,sum(case when (ws_ship_date_sk - ws_sold_date_sk > 30) and |
| (ws_ship_date_sk - ws_sold_date_sk <= 60) then 1 else 0 end ) as "31-60 days" |
| ,sum(case when (ws_ship_date_sk - ws_sold_date_sk > 60) and |
| (ws_ship_date_sk - ws_sold_date_sk <= 90) then 1 else 0 end) as "61-90 days" |
| ,sum(case when (ws_ship_date_sk - ws_sold_date_sk > 90) and |
| (ws_ship_date_sk - ws_sold_date_sk <= 120) then 1 else 0 end) as "91-120 days" |
| ,sum(case when (ws_ship_date_sk - ws_sold_date_sk > 120) then 1 else 0 end) as ">120 days" |
| from |
| web_sales |
| ,warehouse |
| ,ship_mode |
| ,web_site |
| ,date_dim |
| where |
| d_month_seq between 1200 and 1200 + 11 |
| and ws_ship_date_sk = d_date_sk |
| and ws_warehouse_sk = w_warehouse_sk |
| and ws_ship_mode_sk = sm_ship_mode_sk |
| and ws_web_site_sk = web_site_sk |
| group by |
| substr(w_warehouse_name,1,20) |
| ,sm_type |
| ,web_name |
| order by substr(w_warehouse_name,1,20) |
| ,sm_type |
| ,web_name |
| limit 100; |
| ---- RESULTS |
| 'Bad cards must make.','EXPRESS','site_0',251,248,268,277,0 |
| 'Bad cards must make.','EXPRESS','site_1',262,304,290,267,0 |
| 'Bad cards must make.','EXPRESS','site_2',287,290,293,286,0 |
| 'Bad cards must make.','EXPRESS','site_3',283,260,267,290,0 |
| 'Bad cards must make.','EXPRESS','site_4',281,310,309,321,0 |
| 'Bad cards must make.','LIBRARY','site_0',254,265,226,231,0 |
| 'Bad cards must make.','LIBRARY','site_1',198,231,211,201,0 |
| 'Bad cards must make.','LIBRARY','site_2',181,172,190,176,0 |
| 'Bad cards must make.','LIBRARY','site_3',196,214,173,226,0 |
| 'Bad cards must make.','LIBRARY','site_4',241,241,247,236,0 |
| 'Bad cards must make.','NEXT DAY','site_0',259,255,279,260,0 |
| 'Bad cards must make.','NEXT DAY','site_1',287,334,295,300,0 |
| 'Bad cards must make.','NEXT DAY','site_2',269,309,306,272,0 |
| 'Bad cards must make.','NEXT DAY','site_3',281,322,308,277,0 |
| 'Bad cards must make.','NEXT DAY','site_4',273,295,275,281,0 |
| 'Bad cards must make.','OVERNIGHT','site_0',221,216,201,213,0 |
| 'Bad cards must make.','OVERNIGHT','site_1',250,228,257,235,0 |
| 'Bad cards must make.','OVERNIGHT','site_2',255,223,230,238,0 |
| 'Bad cards must make.','OVERNIGHT','site_3',195,217,199,211,0 |
| 'Bad cards must make.','OVERNIGHT','site_4',175,165,169,164,0 |
| 'Bad cards must make.','REGULAR','site_0',176,239,220,233,0 |
| 'Bad cards must make.','REGULAR','site_1',187,163,200,198,0 |
| 'Bad cards must make.','REGULAR','site_2',190,231,195,208,0 |
| 'Bad cards must make.','REGULAR','site_3',227,241,231,252,0 |
| 'Bad cards must make.','REGULAR','site_4',258,245,260,245,0 |
| 'Bad cards must make.','TWO DAY','site_0',222,235,235,223,0 |
| 'Bad cards must make.','TWO DAY','site_1',264,248,237,227,0 |
| 'Bad cards must make.','TWO DAY','site_2',181,230,231,207,0 |
| 'Bad cards must make.','TWO DAY','site_3',182,158,209,185,0 |
| 'Bad cards must make.','TWO DAY','site_4',203,218,198,221,0 |
| 'Conventional childr','EXPRESS','site_0',311,308,336,287,0 |
| 'Conventional childr','EXPRESS','site_1',250,269,273,260,0 |
| 'Conventional childr','EXPRESS','site_2',255,280,235,276,0 |
| 'Conventional childr','EXPRESS','site_3',269,292,303,278,0 |
| 'Conventional childr','EXPRESS','site_4',275,324,268,330,0 |
| 'Conventional childr','LIBRARY','site_0',212,211,225,250,0 |
| 'Conventional childr','LIBRARY','site_1',218,249,244,204,0 |
| 'Conventional childr','LIBRARY','site_2',203,215,222,205,0 |
| 'Conventional childr','LIBRARY','site_3',163,181,180,193,0 |
| 'Conventional childr','LIBRARY','site_4',189,208,207,191,0 |
| 'Conventional childr','NEXT DAY','site_0',253,269,285,258,0 |
| 'Conventional childr','NEXT DAY','site_1',289,244,254,269,0 |
| 'Conventional childr','NEXT DAY','site_2',341,290,310,315,0 |
| 'Conventional childr','NEXT DAY','site_3',309,294,328,270,0 |
| 'Conventional childr','NEXT DAY','site_4',312,311,275,291,0 |
| 'Conventional childr','OVERNIGHT','site_0',180,193,167,211,0 |
| 'Conventional childr','OVERNIGHT','site_1',196,210,238,194,0 |
| 'Conventional childr','OVERNIGHT','site_2',218,236,287,220,0 |
| 'Conventional childr','OVERNIGHT','site_3',235,253,228,246,0 |
| 'Conventional childr','OVERNIGHT','site_4',211,199,201,201,0 |
| 'Conventional childr','REGULAR','site_0',209,262,232,207,0 |
| 'Conventional childr','REGULAR','site_1',214,218,222,227,0 |
| 'Conventional childr','REGULAR','site_2',166,193,184,183,0 |
| 'Conventional childr','REGULAR','site_3',195,191,221,219,0 |
| 'Conventional childr','REGULAR','site_4',248,236,244,256,0 |
| 'Conventional childr','TWO DAY','site_0',192,225,232,225,0 |
| 'Conventional childr','TWO DAY','site_1',248,239,233,251,0 |
| 'Conventional childr','TWO DAY','site_2',230,214,237,201,0 |
| 'Conventional childr','TWO DAY','site_3',236,190,190,222,0 |
| 'Conventional childr','TWO DAY','site_4',197,159,202,176,0 |
| 'Doors canno','EXPRESS','site_0',303,312,304,319,0 |
| 'Doors canno','EXPRESS','site_1',321,278,313,297,0 |
| 'Doors canno','EXPRESS','site_2',267,277,265,257,0 |
| 'Doors canno','EXPRESS','site_3',263,245,258,285,0 |
| 'Doors canno','EXPRESS','site_4',282,313,319,323,0 |
| 'Doors canno','LIBRARY','site_0',201,210,201,200,0 |
| 'Doors canno','LIBRARY','site_1',239,217,208,206,0 |
| 'Doors canno','LIBRARY','site_2',229,233,244,241,0 |
| 'Doors canno','LIBRARY','site_3',216,198,182,222,0 |
| 'Doors canno','LIBRARY','site_4',176,163,170,216,0 |
| 'Doors canno','NEXT DAY','site_0',289,287,289,311,0 |
| 'Doors canno','NEXT DAY','site_1',253,293,256,274,0 |
| 'Doors canno','NEXT DAY','site_2',275,242,265,261,0 |
| 'Doors canno','NEXT DAY','site_3',347,299,296,278,0 |
| 'Doors canno','NEXT DAY','site_4',280,300,325,334,0 |
| 'Doors canno','OVERNIGHT','site_0',235,232,202,221,0 |
| 'Doors canno','OVERNIGHT','site_1',189,177,179,174,0 |
| 'Doors canno','OVERNIGHT','site_2',230,186,228,207,0 |
| 'Doors canno','OVERNIGHT','site_3',231,251,228,242,0 |
| 'Doors canno','OVERNIGHT','site_4',226,247,237,227,0 |
| 'Doors canno','REGULAR','site_0',235,224,237,214,0 |
| 'Doors canno','REGULAR','site_1',203,252,226,247,0 |
| 'Doors canno','REGULAR','site_2',227,193,204,222,0 |
| 'Doors canno','REGULAR','site_3',157,176,169,189,0 |
| 'Doors canno','REGULAR','site_4',232,200,217,199,0 |
| 'Doors canno','TWO DAY','site_0',190,165,199,200,0 |
| 'Doors canno','TWO DAY','site_1',226,224,209,196,0 |
| 'Doors canno','TWO DAY','site_2',229,236,220,235,0 |
| 'Doors canno','TWO DAY','site_3',242,197,248,238,0 |
| 'Doors canno','TWO DAY','site_4',204,213,230,221,0 |
| 'Important issues liv','EXPRESS','site_0',240,250,239,260,0 |
| 'Important issues liv','EXPRESS','site_1',291,287,283,311,0 |
| 'Important issues liv','EXPRESS','site_2',274,312,300,293,0 |
| 'Important issues liv','EXPRESS','site_3',282,298,307,285,0 |
| 'Important issues liv','EXPRESS','site_4',285,271,268,290,0 |
| 'Important issues liv','LIBRARY','site_0',218,230,209,201,0 |
| 'Important issues liv','LIBRARY','site_1',165,160,153,182,0 |
| 'Important issues liv','LIBRARY','site_2',213,209,234,222,0 |
| 'Important issues liv','LIBRARY','site_3',237,258,219,238,0 |
| 'Important issues liv','LIBRARY','site_4',224,243,277,237,0 |
| ---- TYPES |
| STRING, STRING, STRING, BIGINT, BIGINT, BIGINT, BIGINT, BIGINT |
| ==== |