| ==== |
| ---- QUERY: TPCDS-Q99 |
| select |
| substr(w_warehouse_name,1,20) |
| ,sm_type |
| ,cc_name |
| ,sum(case when (cs_ship_date_sk - cs_sold_date_sk <= 30 ) then 1 else 0 end) as "30 days" |
| ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 30) and |
| (cs_ship_date_sk - cs_sold_date_sk <= 60) then 1 else 0 end ) as "31-60 days" |
| ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 60) and |
| (cs_ship_date_sk - cs_sold_date_sk <= 90) then 1 else 0 end) as "61-90 days" |
| ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 90) and |
| (cs_ship_date_sk - cs_sold_date_sk <= 120) then 1 else 0 end) as "91-120 days" |
| ,sum(case when (cs_ship_date_sk - cs_sold_date_sk > 120) then 1 else 0 end) as ">120 days" |
| from |
| catalog_sales |
| ,warehouse |
| ,ship_mode |
| ,call_center |
| ,date_dim |
| where |
| d_month_seq between 1200 and 1200 + 11 |
| and cs_ship_date_sk = d_date_sk |
| and cs_warehouse_sk = w_warehouse_sk |
| and cs_ship_mode_sk = sm_ship_mode_sk |
| and cs_call_center_sk = cc_call_center_sk |
| group by |
| substr(w_warehouse_name,1,20) |
| ,sm_type |
| ,cc_name |
| order by substr(w_warehouse_name,1,20) |
| ,sm_type |
| ,cc_name |
| limit 100; |
| ---- RESULTS |
| 'Bad cards must make.','EXPRESS','Mid Atlantic',1270,1318,1281,0,0 |
| 'Bad cards must make.','EXPRESS','NY Metro',1226,1287,1282,0,0 |
| 'Bad cards must make.','EXPRESS','North Midwest',1208,1242,1294,0,0 |
| 'Bad cards must make.','LIBRARY','Mid Atlantic',962,976,1009,0,0 |
| 'Bad cards must make.','LIBRARY','NY Metro',978,984,999,0,0 |
| 'Bad cards must make.','LIBRARY','North Midwest',898,959,958,0,0 |
| 'Bad cards must make.','NEXT DAY','Mid Atlantic',1225,1328,1327,0,0 |
| 'Bad cards must make.','NEXT DAY','NY Metro',1262,1325,1246,0,0 |
| 'Bad cards must make.','NEXT DAY','North Midwest',1227,1300,1276,0,0 |
| 'Bad cards must make.','OVERNIGHT','Mid Atlantic',956,935,990,0,0 |
| 'Bad cards must make.','OVERNIGHT','NY Metro',982,930,993,0,0 |
| 'Bad cards must make.','OVERNIGHT','North Midwest',907,990,955,0,0 |
| 'Bad cards must make.','REGULAR','Mid Atlantic',928,974,1005,0,0 |
| 'Bad cards must make.','REGULAR','NY Metro',942,1009,948,0,0 |
| 'Bad cards must make.','REGULAR','North Midwest',921,968,925,0,0 |
| 'Bad cards must make.','TWO DAY','Mid Atlantic',954,971,979,0,0 |
| 'Bad cards must make.','TWO DAY','NY Metro',947,1013,952,0,0 |
| 'Bad cards must make.','TWO DAY','North Midwest',904,930,947,0,0 |
| 'Conventional childr','EXPRESS','Mid Atlantic',1214,1296,1243,0,0 |
| 'Conventional childr','EXPRESS','NY Metro',1284,1331,1277,0,0 |
| 'Conventional childr','EXPRESS','North Midwest',1180,1287,1226,0,0 |
| 'Conventional childr','LIBRARY','Mid Atlantic',990,1008,962,0,0 |
| 'Conventional childr','LIBRARY','NY Metro',983,976,981,0,0 |
| 'Conventional childr','LIBRARY','North Midwest',925,892,953,0,0 |
| 'Conventional childr','NEXT DAY','Mid Atlantic',1284,1315,1274,0,0 |
| 'Conventional childr','NEXT DAY','NY Metro',1288,1280,1284,0,0 |
| 'Conventional childr','NEXT DAY','North Midwest',1249,1278,1308,0,0 |
| 'Conventional childr','OVERNIGHT','Mid Atlantic',897,973,926,0,0 |
| 'Conventional childr','OVERNIGHT','NY Metro',958,963,897,0,0 |
| 'Conventional childr','OVERNIGHT','North Midwest',942,934,937,0,0 |
| 'Conventional childr','REGULAR','Mid Atlantic',941,1007,945,0,0 |
| 'Conventional childr','REGULAR','NY Metro',949,928,987,0,0 |
| 'Conventional childr','REGULAR','North Midwest',920,927,925,0,0 |
| 'Conventional childr','TWO DAY','Mid Atlantic',926,979,1047,0,0 |
| 'Conventional childr','TWO DAY','NY Metro',925,1007,962,0,0 |
| 'Conventional childr','TWO DAY','North Midwest',879,980,971,0,0 |
| 'Doors canno','EXPRESS','Mid Atlantic',1240,1245,1267,0,0 |
| 'Doors canno','EXPRESS','NY Metro',1234,1270,1349,0,0 |
| 'Doors canno','EXPRESS','North Midwest',1185,1238,1197,0,0 |
| 'Doors canno','LIBRARY','Mid Atlantic',873,974,948,0,0 |
| 'Doors canno','LIBRARY','NY Metro',987,1005,990,0,0 |
| 'Doors canno','LIBRARY','North Midwest',947,952,1025,0,0 |
| 'Doors canno','NEXT DAY','Mid Atlantic',1264,1276,1185,0,0 |
| 'Doors canno','NEXT DAY','NY Metro',1263,1337,1310,0,0 |
| 'Doors canno','NEXT DAY','North Midwest',1202,1247,1278,0,0 |
| 'Doors canno','OVERNIGHT','Mid Atlantic',912,888,934,0,0 |
| 'Doors canno','OVERNIGHT','NY Metro',942,1003,959,0,0 |
| 'Doors canno','OVERNIGHT','North Midwest',896,903,911,0,0 |
| 'Doors canno','REGULAR','Mid Atlantic',888,953,974,0,0 |
| 'Doors canno','REGULAR','NY Metro',971,1028,946,0,0 |
| 'Doors canno','REGULAR','North Midwest',937,974,914,0,0 |
| 'Doors canno','TWO DAY','Mid Atlantic',974,898,985,0,0 |
| 'Doors canno','TWO DAY','NY Metro',982,1017,997,0,0 |
| 'Doors canno','TWO DAY','North Midwest',985,937,959,0,0 |
| 'Important issues liv','EXPRESS','Mid Atlantic',1322,1329,1293,0,0 |
| 'Important issues liv','EXPRESS','NY Metro',1301,1300,1239,0,0 |
| 'Important issues liv','EXPRESS','North Midwest',1266,1228,1285,0,0 |
| 'Important issues liv','LIBRARY','Mid Atlantic',988,997,925,0,0 |
| 'Important issues liv','LIBRARY','NY Metro',947,963,988,0,0 |
| 'Important issues liv','LIBRARY','North Midwest',937,972,964,0,0 |
| 'Important issues liv','NEXT DAY','Mid Atlantic',1221,1268,1217,0,0 |
| 'Important issues liv','NEXT DAY','NY Metro',1281,1311,1310,0,0 |
| 'Important issues liv','NEXT DAY','North Midwest',1219,1214,1327,0,0 |
| 'Important issues liv','OVERNIGHT','Mid Atlantic',929,1018,991,0,0 |
| 'Important issues liv','OVERNIGHT','NY Metro',950,965,931,0,0 |
| 'Important issues liv','OVERNIGHT','North Midwest',936,989,932,0,0 |
| 'Important issues liv','REGULAR','Mid Atlantic',961,995,949,0,0 |
| 'Important issues liv','REGULAR','NY Metro',972,934,1018,0,0 |
| 'Important issues liv','REGULAR','North Midwest',905,947,941,0,0 |
| 'Important issues liv','TWO DAY','Mid Atlantic',954,982,944,0,0 |
| 'Important issues liv','TWO DAY','NY Metro',844,972,1006,0,0 |
| 'Important issues liv','TWO DAY','North Midwest',914,969,960,0,0 |
| 'NULL','EXPRESS','Mid Atlantic',1223,1314,1257,0,0 |
| 'NULL','EXPRESS','NY Metro',1274,1296,1286,0,0 |
| 'NULL','EXPRESS','North Midwest',1159,1329,1276,0,0 |
| 'NULL','LIBRARY','Mid Atlantic',941,978,948,0,0 |
| 'NULL','LIBRARY','NY Metro',886,1001,1009,0,0 |
| 'NULL','LIBRARY','North Midwest',917,943,991,0,0 |
| 'NULL','NEXT DAY','Mid Atlantic',1304,1264,1349,0,0 |
| 'NULL','NEXT DAY','NY Metro',1271,1251,1291,0,0 |
| 'NULL','NEXT DAY','North Midwest',1199,1233,1273,0,0 |
| 'NULL','OVERNIGHT','Mid Atlantic',965,989,967,0,0 |
| 'NULL','OVERNIGHT','NY Metro',979,993,1039,0,0 |
| 'NULL','OVERNIGHT','North Midwest',946,1016,905,0,0 |
| 'NULL','REGULAR','Mid Atlantic',933,994,997,0,0 |
| 'NULL','REGULAR','NY Metro',961,1022,1034,0,0 |
| 'NULL','REGULAR','North Midwest',893,921,949,0,0 |
| 'NULL','TWO DAY','Mid Atlantic',972,968,972,0,0 |
| 'NULL','TWO DAY','NY Metro',926,974,1004,0,0 |
| 'NULL','TWO DAY','North Midwest',941,921,981,0,0 |
| ---- TYPES |
| STRING, STRING, STRING, BIGINT, BIGINT, BIGINT, BIGINT, BIGINT |
| ==== |
