github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/xform/testdata/external/tpch (about) 1 import file=tpch_schema 2 ---- 3 4 import file=tpch_stats 5 ---- 6 7 # -------------------------------------------------- 8 # Q1 9 # Pricing Summary Report 10 # Reports the amount of business that was billed, shipped, and returned. 11 # 12 # Provides a summary pricing report for all lineitems shipped as of a given 13 # date. The date is within 60 - 120 days of the greatest ship date contained in 14 # the database. The query lists totals for extended price, discounted extended 15 # price, discounted extended price plus tax, average quantity, average extended 16 # price, and average discount. These aggregates are grouped by RETURNFLAG and 17 # LINESTATUS, and listed in ascending order of RETURNFLAG and LINESTATUS. A 18 # count of the number of lineitems in each group is included. 19 # -------------------------------------------------- 20 opt 21 SELECT 22 l_returnflag, 23 l_linestatus, 24 sum(l_quantity) AS sum_qty, 25 sum(l_extendedprice) AS sum_base_price, 26 sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price, 27 sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge, 28 avg(l_quantity) AS avg_qty, 29 avg(l_extendedprice) AS avg_price, 30 avg(l_discount) AS avg_disc, 31 count(*) AS count_order 32 FROM 33 lineitem 34 WHERE 35 l_shipdate <= DATE '1998-12-01' - INTERVAL '90' DAY 36 GROUP BY 37 l_returnflag, 38 l_linestatus 39 ORDER BY 40 l_returnflag, 41 l_linestatus; 42 ---- 43 sort 44 ├── columns: l_returnflag:9!null l_linestatus:10!null sum_qty:17!null sum_base_price:18!null sum_disc_price:20!null sum_charge:22!null avg_qty:23!null avg_price:24!null avg_disc:25!null count_order:26!null 45 ├── key: (9,10) 46 ├── fd: (9,10)-->(17,18,20,22-26) 47 ├── ordering: +9,+10 48 └── group-by 49 ├── columns: l_returnflag:9!null l_linestatus:10!null sum:17!null sum:18!null sum:20!null sum:22!null avg:23!null avg:24!null avg:25!null count_rows:26!null 50 ├── grouping columns: l_returnflag:9!null l_linestatus:10!null 51 ├── key: (9,10) 52 ├── fd: (9,10)-->(17,18,20,22-26) 53 ├── project 54 │ ├── columns: column19:19!null column21:21!null l_quantity:5!null l_extendedprice:6!null l_discount:7!null l_returnflag:9!null l_linestatus:10!null 55 │ ├── select 56 │ │ ├── columns: l_quantity:5!null l_extendedprice:6!null l_discount:7!null l_tax:8!null l_returnflag:9!null l_linestatus:10!null l_shipdate:11!null 57 │ │ ├── scan lineitem 58 │ │ │ └── columns: l_quantity:5!null l_extendedprice:6!null l_discount:7!null l_tax:8!null l_returnflag:9!null l_linestatus:10!null l_shipdate:11!null 59 │ │ └── filters 60 │ │ └── l_shipdate:11 <= '1998-09-02' [outer=(11), constraints=(/11: (/NULL - /'1998-09-02']; tight)] 61 │ └── projections 62 │ ├── l_extendedprice:6 * (1.0 - l_discount:7) [as=column19:19, outer=(6,7)] 63 │ └── (l_extendedprice:6 * (1.0 - l_discount:7)) * (l_tax:8 + 1.0) [as=column21:21, outer=(6-8)] 64 └── aggregations 65 ├── sum [as=sum:17, outer=(5)] 66 │ └── l_quantity:5 67 ├── sum [as=sum:18, outer=(6)] 68 │ └── l_extendedprice:6 69 ├── sum [as=sum:20, outer=(19)] 70 │ └── column19:19 71 ├── sum [as=sum:22, outer=(21)] 72 │ └── column21:21 73 ├── avg [as=avg:23, outer=(5)] 74 │ └── l_quantity:5 75 ├── avg [as=avg:24, outer=(6)] 76 │ └── l_extendedprice:6 77 ├── avg [as=avg:25, outer=(7)] 78 │ └── l_discount:7 79 └── count-rows [as=count_rows:26] 80 81 # -------------------------------------------------- 82 # Q2 83 # Minimum Cost Supplier 84 # Finds which supplier should be selected to place an order for a given part in 85 # a given region. 86 # 87 # Finds, in a given region, for each part of a certain type and size, the 88 # supplier who can supply it at minimum cost. If several suppliers in that 89 # region offer the desired part type and size at the same (minimum) cost, the 90 # query lists the parts from suppliers with the 100 highest account balances. 91 # For each supplier, the query lists the supplier's account balance, name and 92 # nation; the part's number and manufacturer; the supplier's address, phone 93 # number and comment information. 94 # 95 # TODO: 96 # 1. Join ordering 97 # 2. Push down equivalent column comparisons 98 # -------------------------------------------------- 99 opt 100 SELECT 101 s_acctbal, 102 s_name, 103 n_name, 104 p_partkey, 105 p_mfgr, 106 s_address, 107 s_phone, 108 s_comment 109 FROM 110 part, 111 supplier, 112 partsupp, 113 nation, 114 region 115 WHERE 116 p_partkey = ps_partkey 117 AND s_suppkey = ps_suppkey 118 AND p_size = 15 119 AND p_type LIKE '%BRASS' 120 AND s_nationkey = n_nationkey 121 AND n_regionkey = r_regionkey 122 AND r_name = 'EUROPE' 123 AND ps_supplycost = ( 124 SELECT 125 min(ps_supplycost) 126 FROM 127 partsupp, 128 supplier, 129 nation, 130 region 131 WHERE 132 p_partkey = ps_partkey 133 AND s_suppkey = ps_suppkey 134 AND s_nationkey = n_nationkey 135 AND n_regionkey = r_regionkey 136 AND r_name = 'EUROPE' 137 ) 138 ORDER BY 139 s_acctbal DESC, 140 n_name, 141 s_name, 142 p_partkey 143 LIMIT 100; 144 ---- 145 project 146 ├── columns: s_acctbal:15!null s_name:11!null n_name:23!null p_partkey:1!null p_mfgr:3!null s_address:12!null s_phone:14!null s_comment:16!null 147 ├── cardinality: [0 - 100] 148 ├── fd: (1)-->(3) 149 ├── ordering: -15,+23,+11,+1 150 └── limit 151 ├── columns: p_partkey:1!null p_mfgr:3!null s_name:11!null s_address:12!null s_phone:14!null s_acctbal:15!null s_comment:16!null ps_partkey:17!null ps_suppkey:18!null ps_supplycost:20!null n_name:23!null min:48!null 152 ├── internal-ordering: -15,+23,+11,+(1|17) 153 ├── cardinality: [0 - 100] 154 ├── key: (17,18) 155 ├── fd: (1)-->(3), (17,18)-->(1,3,11,12,14-16,20,23,48), (1)==(17), (17)==(1), (18)-->(11,12,14-16,23), (20)==(48), (48)==(20) 156 ├── ordering: -15,+23,+11,+(1|17) [actual: -15,+23,+11,+1] 157 ├── sort 158 │ ├── columns: p_partkey:1!null p_mfgr:3!null s_name:11!null s_address:12!null s_phone:14!null s_acctbal:15!null s_comment:16!null ps_partkey:17!null ps_suppkey:18!null ps_supplycost:20!null n_name:23!null min:48!null 159 │ ├── key: (17,18) 160 │ ├── fd: (1)-->(3), (17,18)-->(1,3,11,12,14-16,20,23,48), (1)==(17), (17)==(1), (18)-->(11,12,14-16,23), (20)==(48), (48)==(20) 161 │ ├── ordering: -15,+23,+11,+(1|17) [actual: -15,+23,+11,+1] 162 │ ├── limit hint: 100.00 163 │ └── select 164 │ ├── columns: p_partkey:1!null p_mfgr:3!null s_name:11!null s_address:12!null s_phone:14!null s_acctbal:15!null s_comment:16!null ps_partkey:17!null ps_suppkey:18!null ps_supplycost:20!null n_name:23!null min:48!null 165 │ ├── key: (17,18) 166 │ ├── fd: (1)-->(3), (17,18)-->(1,3,11,12,14-16,20,23,48), (1)==(17), (17)==(1), (18)-->(11,12,14-16,23), (20)==(48), (48)==(20) 167 │ ├── group-by 168 │ │ ├── columns: p_partkey:1!null p_mfgr:3!null s_name:11!null s_address:12!null s_phone:14!null s_acctbal:15!null s_comment:16!null ps_partkey:17!null ps_suppkey:18!null ps_supplycost:20!null n_name:23!null min:48!null 169 │ │ ├── grouping columns: ps_partkey:17!null ps_suppkey:18!null 170 │ │ ├── key: (17,18) 171 │ │ ├── fd: (1)-->(3), (17,18)-->(1,3,11,12,14-16,20,23,48), (1)==(17), (17)==(1), (18)-->(11,12,14-16,23) 172 │ │ ├── inner-join (hash) 173 │ │ │ ├── columns: p_partkey:1!null p_mfgr:3!null p_type:5!null p_size:6!null s_suppkey:10!null s_name:11!null s_address:12!null s_nationkey:13!null s_phone:14!null s_acctbal:15!null s_comment:16!null ps_partkey:17!null ps_suppkey:18!null ps_supplycost:20!null n_nationkey:22!null n_name:23!null n_regionkey:24!null r_regionkey:26!null r_name:27!null ps_partkey:29!null ps_suppkey:30!null ps_supplycost:32!null s_suppkey:34!null s_nationkey:37!null n_nationkey:41!null n_regionkey:43!null r_regionkey:45!null r_name:46!null 174 │ │ │ ├── key: (18,29,34) 175 │ │ │ ├── fd: ()-->(6,27,46), (1)-->(3,5), (10)-->(11-16), (17,18)-->(20), (22)-->(23,24), (24)==(26), (26)==(24), (10)==(18), (18)==(10), (13)==(22), (22)==(13), (1)==(17,29), (17)==(1,29), (29,30)-->(32), (34)-->(37), (41)-->(43), (43)==(45), (45)==(43), (37)==(41), (41)==(37), (30)==(34), (34)==(30), (29)==(1,17) 176 │ │ │ ├── inner-join (hash) 177 │ │ │ │ ├── columns: ps_partkey:29!null ps_suppkey:30!null ps_supplycost:32!null s_suppkey:34!null s_nationkey:37!null n_nationkey:41!null n_regionkey:43!null r_regionkey:45!null r_name:46!null 178 │ │ │ │ ├── key: (29,34) 179 │ │ │ │ ├── fd: ()-->(46), (29,30)-->(32), (34)-->(37), (41)-->(43), (43)==(45), (45)==(43), (37)==(41), (41)==(37), (30)==(34), (34)==(30) 180 │ │ │ │ ├── scan partsupp 181 │ │ │ │ │ ├── columns: ps_partkey:29!null ps_suppkey:30!null ps_supplycost:32!null 182 │ │ │ │ │ ├── key: (29,30) 183 │ │ │ │ │ └── fd: (29,30)-->(32) 184 │ │ │ │ ├── inner-join (lookup supplier@s_nk) 185 │ │ │ │ │ ├── columns: s_suppkey:34!null s_nationkey:37!null n_nationkey:41!null n_regionkey:43!null r_regionkey:45!null r_name:46!null 186 │ │ │ │ │ ├── key columns: [41] = [37] 187 │ │ │ │ │ ├── key: (34) 188 │ │ │ │ │ ├── fd: ()-->(46), (34)-->(37), (41)-->(43), (43)==(45), (45)==(43), (37)==(41), (41)==(37) 189 │ │ │ │ │ ├── inner-join (merge) 190 │ │ │ │ │ │ ├── columns: n_nationkey:41!null n_regionkey:43!null r_regionkey:45!null r_name:46!null 191 │ │ │ │ │ │ ├── left ordering: +43 192 │ │ │ │ │ │ ├── right ordering: +45 193 │ │ │ │ │ │ ├── key: (41) 194 │ │ │ │ │ │ ├── fd: ()-->(46), (41)-->(43), (43)==(45), (45)==(43) 195 │ │ │ │ │ │ ├── scan nation@n_rk 196 │ │ │ │ │ │ │ ├── columns: n_nationkey:41!null n_regionkey:43!null 197 │ │ │ │ │ │ │ ├── key: (41) 198 │ │ │ │ │ │ │ ├── fd: (41)-->(43) 199 │ │ │ │ │ │ │ └── ordering: +43 200 │ │ │ │ │ │ ├── select 201 │ │ │ │ │ │ │ ├── columns: r_regionkey:45!null r_name:46!null 202 │ │ │ │ │ │ │ ├── key: (45) 203 │ │ │ │ │ │ │ ├── fd: ()-->(46) 204 │ │ │ │ │ │ │ ├── ordering: +45 opt(46) [actual: +45] 205 │ │ │ │ │ │ │ ├── scan region 206 │ │ │ │ │ │ │ │ ├── columns: r_regionkey:45!null r_name:46!null 207 │ │ │ │ │ │ │ │ ├── key: (45) 208 │ │ │ │ │ │ │ │ ├── fd: (45)-->(46) 209 │ │ │ │ │ │ │ │ └── ordering: +45 opt(46) [actual: +45] 210 │ │ │ │ │ │ │ └── filters 211 │ │ │ │ │ │ │ └── r_name:46 = 'EUROPE' [outer=(46), constraints=(/46: [/'EUROPE' - /'EUROPE']; tight), fd=()-->(46)] 212 │ │ │ │ │ │ └── filters (true) 213 │ │ │ │ │ └── filters (true) 214 │ │ │ │ └── filters 215 │ │ │ │ └── s_suppkey:34 = ps_suppkey:30 [outer=(30,34), constraints=(/30: (/NULL - ]; /34: (/NULL - ]), fd=(30)==(34), (34)==(30)] 216 │ │ │ ├── inner-join (hash) 217 │ │ │ │ ├── columns: p_partkey:1!null p_mfgr:3!null p_type:5!null p_size:6!null s_suppkey:10!null s_name:11!null s_address:12!null s_nationkey:13!null s_phone:14!null s_acctbal:15!null s_comment:16!null ps_partkey:17!null ps_suppkey:18!null ps_supplycost:20!null n_nationkey:22!null n_name:23!null n_regionkey:24!null r_regionkey:26!null r_name:27!null 218 │ │ │ │ ├── key: (17,18) 219 │ │ │ │ ├── fd: ()-->(6,27), (1)-->(3,5), (10)-->(11-16), (17,18)-->(20), (22)-->(23,24), (24)==(26), (26)==(24), (10)==(18), (18)==(10), (13)==(22), (22)==(13), (1)==(17), (17)==(1) 220 │ │ │ │ ├── inner-join (hash) 221 │ │ │ │ │ ├── columns: s_suppkey:10!null s_name:11!null s_address:12!null s_nationkey:13!null s_phone:14!null s_acctbal:15!null s_comment:16!null ps_partkey:17!null ps_suppkey:18!null ps_supplycost:20!null n_nationkey:22!null n_name:23!null n_regionkey:24!null r_regionkey:26!null r_name:27!null 222 │ │ │ │ │ ├── key: (17,18) 223 │ │ │ │ │ ├── fd: ()-->(27), (10)-->(11-16), (17,18)-->(20), (22)-->(23,24), (24)==(26), (26)==(24), (10)==(18), (18)==(10), (13)==(22), (22)==(13) 224 │ │ │ │ │ ├── scan partsupp 225 │ │ │ │ │ │ ├── columns: ps_partkey:17!null ps_suppkey:18!null ps_supplycost:20!null 226 │ │ │ │ │ │ ├── key: (17,18) 227 │ │ │ │ │ │ └── fd: (17,18)-->(20) 228 │ │ │ │ │ ├── inner-join (hash) 229 │ │ │ │ │ │ ├── columns: s_suppkey:10!null s_name:11!null s_address:12!null s_nationkey:13!null s_phone:14!null s_acctbal:15!null s_comment:16!null n_nationkey:22!null n_name:23!null n_regionkey:24!null r_regionkey:26!null r_name:27!null 230 │ │ │ │ │ │ ├── key: (10) 231 │ │ │ │ │ │ ├── fd: ()-->(27), (22)-->(23,24), (24)==(26), (26)==(24), (10)-->(11-16), (13)==(22), (22)==(13) 232 │ │ │ │ │ │ ├── scan supplier 233 │ │ │ │ │ │ │ ├── columns: s_suppkey:10!null s_name:11!null s_address:12!null s_nationkey:13!null s_phone:14!null s_acctbal:15!null s_comment:16!null 234 │ │ │ │ │ │ │ ├── key: (10) 235 │ │ │ │ │ │ │ └── fd: (10)-->(11-16) 236 │ │ │ │ │ │ ├── inner-join (hash) 237 │ │ │ │ │ │ │ ├── columns: n_nationkey:22!null n_name:23!null n_regionkey:24!null r_regionkey:26!null r_name:27!null 238 │ │ │ │ │ │ │ ├── key: (22) 239 │ │ │ │ │ │ │ ├── fd: ()-->(27), (22)-->(23,24), (24)==(26), (26)==(24) 240 │ │ │ │ │ │ │ ├── scan nation 241 │ │ │ │ │ │ │ │ ├── columns: n_nationkey:22!null n_name:23!null n_regionkey:24!null 242 │ │ │ │ │ │ │ │ ├── key: (22) 243 │ │ │ │ │ │ │ │ └── fd: (22)-->(23,24) 244 │ │ │ │ │ │ │ ├── select 245 │ │ │ │ │ │ │ │ ├── columns: r_regionkey:26!null r_name:27!null 246 │ │ │ │ │ │ │ │ ├── key: (26) 247 │ │ │ │ │ │ │ │ ├── fd: ()-->(27) 248 │ │ │ │ │ │ │ │ ├── scan region 249 │ │ │ │ │ │ │ │ │ ├── columns: r_regionkey:26!null r_name:27!null 250 │ │ │ │ │ │ │ │ │ ├── key: (26) 251 │ │ │ │ │ │ │ │ │ └── fd: (26)-->(27) 252 │ │ │ │ │ │ │ │ └── filters 253 │ │ │ │ │ │ │ │ └── r_name:27 = 'EUROPE' [outer=(27), constraints=(/27: [/'EUROPE' - /'EUROPE']; tight), fd=()-->(27)] 254 │ │ │ │ │ │ │ └── filters 255 │ │ │ │ │ │ │ └── n_regionkey:24 = r_regionkey:26 [outer=(24,26), constraints=(/24: (/NULL - ]; /26: (/NULL - ]), fd=(24)==(26), (26)==(24)] 256 │ │ │ │ │ │ └── filters 257 │ │ │ │ │ │ └── s_nationkey:13 = n_nationkey:22 [outer=(13,22), constraints=(/13: (/NULL - ]; /22: (/NULL - ]), fd=(13)==(22), (22)==(13)] 258 │ │ │ │ │ └── filters 259 │ │ │ │ │ └── s_suppkey:10 = ps_suppkey:18 [outer=(10,18), constraints=(/10: (/NULL - ]; /18: (/NULL - ]), fd=(10)==(18), (18)==(10)] 260 │ │ │ │ ├── select 261 │ │ │ │ │ ├── columns: p_partkey:1!null p_mfgr:3!null p_type:5!null p_size:6!null 262 │ │ │ │ │ ├── key: (1) 263 │ │ │ │ │ ├── fd: ()-->(6), (1)-->(3,5) 264 │ │ │ │ │ ├── scan part 265 │ │ │ │ │ │ ├── columns: p_partkey:1!null p_mfgr:3!null p_type:5!null p_size:6!null 266 │ │ │ │ │ │ ├── key: (1) 267 │ │ │ │ │ │ └── fd: (1)-->(3,5,6) 268 │ │ │ │ │ └── filters 269 │ │ │ │ │ ├── p_size:6 = 15 [outer=(6), constraints=(/6: [/15 - /15]; tight), fd=()-->(6)] 270 │ │ │ │ │ └── p_type:5 LIKE '%BRASS' [outer=(5), constraints=(/5: (/NULL - ])] 271 │ │ │ │ └── filters 272 │ │ │ │ └── p_partkey:1 = ps_partkey:17 [outer=(1,17), constraints=(/1: (/NULL - ]; /17: (/NULL - ]), fd=(1)==(17), (17)==(1)] 273 │ │ │ └── filters 274 │ │ │ └── p_partkey:1 = ps_partkey:29 [outer=(1,29), constraints=(/1: (/NULL - ]; /29: (/NULL - ]), fd=(1)==(29), (29)==(1)] 275 │ │ └── aggregations 276 │ │ ├── min [as=min:48, outer=(32)] 277 │ │ │ └── ps_supplycost:32 278 │ │ ├── const-agg [as=s_name:11, outer=(11)] 279 │ │ │ └── s_name:11 280 │ │ ├── const-agg [as=s_address:12, outer=(12)] 281 │ │ │ └── s_address:12 282 │ │ ├── const-agg [as=s_phone:14, outer=(14)] 283 │ │ │ └── s_phone:14 284 │ │ ├── const-agg [as=s_acctbal:15, outer=(15)] 285 │ │ │ └── s_acctbal:15 286 │ │ ├── const-agg [as=s_comment:16, outer=(16)] 287 │ │ │ └── s_comment:16 288 │ │ ├── const-agg [as=ps_supplycost:20, outer=(20)] 289 │ │ │ └── ps_supplycost:20 290 │ │ ├── const-agg [as=n_name:23, outer=(23)] 291 │ │ │ └── n_name:23 292 │ │ ├── const-agg [as=p_mfgr:3, outer=(3)] 293 │ │ │ └── p_mfgr:3 294 │ │ └── const-agg [as=p_partkey:1, outer=(1)] 295 │ │ └── p_partkey:1 296 │ └── filters 297 │ └── ps_supplycost:20 = min:48 [outer=(20,48), constraints=(/20: (/NULL - ]; /48: (/NULL - ]), fd=(20)==(48), (48)==(20)] 298 └── 100 299 300 # -------------------------------------------------- 301 # Q3 302 # Shipping Priority 303 # Retrieves the 10 unshipped orders with the highest value. 304 # 305 # Retrieves the shipping priority and potential revenue, defined as the sum of 306 # l_extendedprice * (1-l_discount), of the orders having the largest revenue 307 # among those that had not been shipped as of a given date. Orders are listed in 308 # decreasing order of revenue. If more than 10 unshipped orders exist, only the 309 # 10 orders with the largest revenue are listed. 310 # -------------------------------------------------- 311 opt 312 SELECT 313 l_orderkey, 314 sum(l_extendedprice * (1 - l_discount)) AS revenue, 315 o_orderdate, 316 o_shippriority 317 FROM 318 customer, 319 orders, 320 lineitem 321 WHERE 322 c_mktsegment = 'BUILDING' 323 AND c_custkey = o_custkey 324 AND l_orderkey = o_orderkey 325 AND o_orderDATE < DATE '1995-03-15' 326 AND l_shipdate > DATE '1995-03-15' 327 GROUP BY 328 l_orderkey, 329 o_orderdate, 330 o_shippriority 331 ORDER BY 332 revenue DESC, 333 o_orderdate 334 LIMIT 10; 335 ---- 336 limit 337 ├── columns: l_orderkey:18!null revenue:35!null o_orderdate:13!null o_shippriority:16!null 338 ├── internal-ordering: -35,+13 339 ├── cardinality: [0 - 10] 340 ├── key: (18) 341 ├── fd: (18)-->(13,16,35) 342 ├── ordering: -35,+13 343 ├── sort 344 │ ├── columns: o_orderdate:13!null o_shippriority:16!null l_orderkey:18!null sum:35!null 345 │ ├── key: (18) 346 │ ├── fd: (18)-->(13,16,35) 347 │ ├── ordering: -35,+13 348 │ ├── limit hint: 10.00 349 │ └── group-by 350 │ ├── columns: o_orderdate:13!null o_shippriority:16!null l_orderkey:18!null sum:35!null 351 │ ├── grouping columns: l_orderkey:18!null 352 │ ├── key: (18) 353 │ ├── fd: (18)-->(13,16,35) 354 │ ├── project 355 │ │ ├── columns: column34:34!null o_orderdate:13!null o_shippriority:16!null l_orderkey:18!null 356 │ │ ├── fd: (18)-->(13,16) 357 │ │ ├── inner-join (lookup lineitem) 358 │ │ │ ├── columns: c_custkey:1!null c_mktsegment:7!null o_orderkey:9!null o_custkey:10!null o_orderdate:13!null o_shippriority:16!null l_orderkey:18!null l_extendedprice:23!null l_discount:24!null l_shipdate:28!null 359 │ │ │ ├── key columns: [9] = [18] 360 │ │ │ ├── fd: ()-->(7), (9)-->(10,13,16), (9)==(18), (18)==(9), (1)==(10), (10)==(1) 361 │ │ │ ├── inner-join (hash) 362 │ │ │ │ ├── columns: c_custkey:1!null c_mktsegment:7!null o_orderkey:9!null o_custkey:10!null o_orderdate:13!null o_shippriority:16!null 363 │ │ │ │ ├── key: (9) 364 │ │ │ │ ├── fd: ()-->(7), (9)-->(10,13,16), (1)==(10), (10)==(1) 365 │ │ │ │ ├── select 366 │ │ │ │ │ ├── columns: o_orderkey:9!null o_custkey:10!null o_orderdate:13!null o_shippriority:16!null 367 │ │ │ │ │ ├── key: (9) 368 │ │ │ │ │ ├── fd: (9)-->(10,13,16) 369 │ │ │ │ │ ├── scan orders 370 │ │ │ │ │ │ ├── columns: o_orderkey:9!null o_custkey:10!null o_orderdate:13!null o_shippriority:16!null 371 │ │ │ │ │ │ ├── key: (9) 372 │ │ │ │ │ │ └── fd: (9)-->(10,13,16) 373 │ │ │ │ │ └── filters 374 │ │ │ │ │ └── o_orderdate:13 < '1995-03-15' [outer=(13), constraints=(/13: (/NULL - /'1995-03-14']; tight)] 375 │ │ │ │ ├── select 376 │ │ │ │ │ ├── columns: c_custkey:1!null c_mktsegment:7!null 377 │ │ │ │ │ ├── key: (1) 378 │ │ │ │ │ ├── fd: ()-->(7) 379 │ │ │ │ │ ├── scan customer 380 │ │ │ │ │ │ ├── columns: c_custkey:1!null c_mktsegment:7!null 381 │ │ │ │ │ │ ├── key: (1) 382 │ │ │ │ │ │ └── fd: (1)-->(7) 383 │ │ │ │ │ └── filters 384 │ │ │ │ │ └── c_mktsegment:7 = 'BUILDING' [outer=(7), constraints=(/7: [/'BUILDING' - /'BUILDING']; tight), fd=()-->(7)] 385 │ │ │ │ └── filters 386 │ │ │ │ └── c_custkey:1 = o_custkey:10 [outer=(1,10), constraints=(/1: (/NULL - ]; /10: (/NULL - ]), fd=(1)==(10), (10)==(1)] 387 │ │ │ └── filters 388 │ │ │ └── l_shipdate:28 > '1995-03-15' [outer=(28), constraints=(/28: [/'1995-03-16' - ]; tight)] 389 │ │ └── projections 390 │ │ └── l_extendedprice:23 * (1.0 - l_discount:24) [as=column34:34, outer=(23,24)] 391 │ └── aggregations 392 │ ├── sum [as=sum:35, outer=(34)] 393 │ │ └── column34:34 394 │ ├── const-agg [as=o_orderdate:13, outer=(13)] 395 │ │ └── o_orderdate:13 396 │ └── const-agg [as=o_shippriority:16, outer=(16)] 397 │ └── o_shippriority:16 398 └── 10 399 400 # -------------------------------------------------- 401 # Q4 402 # Order Priority Checking 403 # Determines how well the order priority system is working and gives an 404 # assessment of customer satisfaction. 405 # 406 # Counts the number of orders ordered in a given quarter of a given year in 407 # which at least one lineitem was received by the customer later than its 408 # committed date. The query lists the count of such orders for each order 409 # priority sorted in ascending priority order. 410 # -------------------------------------------------- 411 opt 412 SELECT 413 o_orderpriority, 414 count(*) AS order_count 415 FROM 416 orders 417 WHERE 418 o_orderdate >= DATE '1993-07-01' 419 AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH 420 AND EXISTS ( 421 SELECT 422 * 423 FROM 424 lineitem 425 WHERE 426 l_orderkey = o_orderkey 427 AND l_commitDATE < l_receiptdate 428 ) 429 GROUP BY 430 o_orderpriority 431 ORDER BY 432 o_orderpriority; 433 ---- 434 sort 435 ├── columns: o_orderpriority:6!null order_count:26!null 436 ├── key: (6) 437 ├── fd: (6)-->(26) 438 ├── ordering: +6 439 └── group-by 440 ├── columns: o_orderpriority:6!null count_rows:26!null 441 ├── grouping columns: o_orderpriority:6!null 442 ├── key: (6) 443 ├── fd: (6)-->(26) 444 ├── semi-join (hash) 445 │ ├── columns: o_orderkey:1!null o_orderdate:5!null o_orderpriority:6!null 446 │ ├── key: (1) 447 │ ├── fd: (1)-->(5,6) 448 │ ├── index-join orders 449 │ │ ├── columns: o_orderkey:1!null o_orderdate:5!null o_orderpriority:6!null 450 │ │ ├── key: (1) 451 │ │ ├── fd: (1)-->(5,6) 452 │ │ └── scan orders@o_od 453 │ │ ├── columns: o_orderkey:1!null o_orderdate:5!null 454 │ │ ├── constraint: /5/1: [/'1993-07-01' - /'1993-09-30'] 455 │ │ ├── key: (1) 456 │ │ └── fd: (1)-->(5) 457 │ ├── select 458 │ │ ├── columns: l_orderkey:10!null l_commitdate:21!null l_receiptdate:22!null 459 │ │ ├── scan lineitem 460 │ │ │ └── columns: l_orderkey:10!null l_commitdate:21!null l_receiptdate:22!null 461 │ │ └── filters 462 │ │ └── l_commitdate:21 < l_receiptdate:22 [outer=(21,22), constraints=(/21: (/NULL - ]; /22: (/NULL - ])] 463 │ └── filters 464 │ └── l_orderkey:10 = o_orderkey:1 [outer=(1,10), constraints=(/1: (/NULL - ]; /10: (/NULL - ]), fd=(1)==(10), (10)==(1)] 465 └── aggregations 466 └── count-rows [as=count_rows:26] 467 468 # -------------------------------------------------- 469 # Q5 470 # Local Supplier Volume 471 # Lists the revenue volume done through local suppliers. 472 # 473 # Lists for each nation in a region the revenue volume that resulted from 474 # lineitem transactions in which the customer ordering parts and the supplier 475 # filling them were both within that nation. The query is run in order to 476 # determine whether to institute local distribution centers in a given region. 477 # The query considers only parts ordered in a given year. The query displays the 478 # nations and revenue volume in descending order by revenue. Revenue volume for 479 # all qualifying lineitems in a particular nation is defined as 480 # sum(l_extendedprice * (1 - l_discount)). 481 # 482 # TODO: 483 # 1. Join ordering 484 # -------------------------------------------------- 485 opt 486 SELECT 487 n_name, 488 sum(l_extendedprice * (1 - l_discount)) AS revenue 489 FROM 490 customer, 491 orders, 492 lineitem, 493 supplier, 494 nation, 495 region 496 WHERE 497 c_custkey = o_custkey 498 AND l_orderkey = o_orderkey 499 AND l_suppkey = s_suppkey 500 AND c_nationkey = s_nationkey 501 AND s_nationkey = n_nationkey 502 AND n_regionkey = r_regionkey 503 AND r_name = 'ASIA' 504 AND o_orderDATE >= DATE '1994-01-01' 505 AND o_orderDATE < DATE '1994-01-01' + INTERVAL '1' YEAR 506 GROUP BY 507 n_name 508 ORDER BY 509 revenue DESC; 510 ---- 511 sort 512 ├── columns: n_name:42!null revenue:49!null 513 ├── key: (42) 514 ├── fd: (42)-->(49) 515 ├── ordering: -49 516 └── group-by 517 ├── columns: n_name:42!null sum:49!null 518 ├── grouping columns: n_name:42!null 519 ├── key: (42) 520 ├── fd: (42)-->(49) 521 ├── project 522 │ ├── columns: column48:48!null n_name:42!null 523 │ ├── inner-join (hash) 524 │ │ ├── columns: c_custkey:1!null c_nationkey:4!null o_orderkey:9!null o_custkey:10!null o_orderdate:13!null l_orderkey:18!null l_suppkey:20!null l_extendedprice:23!null l_discount:24!null s_suppkey:34!null s_nationkey:37!null n_nationkey:41!null n_name:42!null n_regionkey:43!null r_regionkey:45!null r_name:46!null 525 │ │ ├── fd: ()-->(46), (1)-->(4), (9)-->(10,13), (34)-->(37), (41)-->(42,43), (43)==(45), (45)==(43), (37)==(4,41), (41)==(4,37), (20)==(34), (34)==(20), (9)==(18), (18)==(9), (1)==(10), (10)==(1), (4)==(37,41) 526 │ │ ├── inner-join (hash) 527 │ │ │ ├── columns: o_orderkey:9!null o_custkey:10!null o_orderdate:13!null l_orderkey:18!null l_suppkey:20!null l_extendedprice:23!null l_discount:24!null s_suppkey:34!null s_nationkey:37!null n_nationkey:41!null n_name:42!null n_regionkey:43!null r_regionkey:45!null r_name:46!null 528 │ │ │ ├── fd: ()-->(46), (9)-->(10,13), (34)-->(37), (41)-->(42,43), (43)==(45), (45)==(43), (37)==(41), (41)==(37), (20)==(34), (34)==(20), (9)==(18), (18)==(9) 529 │ │ │ ├── inner-join (hash) 530 │ │ │ │ ├── columns: l_orderkey:18!null l_suppkey:20!null l_extendedprice:23!null l_discount:24!null s_suppkey:34!null s_nationkey:37!null n_nationkey:41!null n_name:42!null n_regionkey:43!null r_regionkey:45!null r_name:46!null 531 │ │ │ │ ├── fd: ()-->(46), (34)-->(37), (41)-->(42,43), (43)==(45), (45)==(43), (37)==(41), (41)==(37), (20)==(34), (34)==(20) 532 │ │ │ │ ├── scan lineitem 533 │ │ │ │ │ └── columns: l_orderkey:18!null l_suppkey:20!null l_extendedprice:23!null l_discount:24!null 534 │ │ │ │ ├── inner-join (lookup supplier@s_nk) 535 │ │ │ │ │ ├── columns: s_suppkey:34!null s_nationkey:37!null n_nationkey:41!null n_name:42!null n_regionkey:43!null r_regionkey:45!null r_name:46!null 536 │ │ │ │ │ ├── key columns: [41] = [37] 537 │ │ │ │ │ ├── key: (34) 538 │ │ │ │ │ ├── fd: ()-->(46), (34)-->(37), (41)-->(42,43), (43)==(45), (45)==(43), (37)==(41), (41)==(37) 539 │ │ │ │ │ ├── inner-join (hash) 540 │ │ │ │ │ │ ├── columns: n_nationkey:41!null n_name:42!null n_regionkey:43!null r_regionkey:45!null r_name:46!null 541 │ │ │ │ │ │ ├── key: (41) 542 │ │ │ │ │ │ ├── fd: ()-->(46), (41)-->(42,43), (43)==(45), (45)==(43) 543 │ │ │ │ │ │ ├── scan nation 544 │ │ │ │ │ │ │ ├── columns: n_nationkey:41!null n_name:42!null n_regionkey:43!null 545 │ │ │ │ │ │ │ ├── key: (41) 546 │ │ │ │ │ │ │ └── fd: (41)-->(42,43) 547 │ │ │ │ │ │ ├── select 548 │ │ │ │ │ │ │ ├── columns: r_regionkey:45!null r_name:46!null 549 │ │ │ │ │ │ │ ├── key: (45) 550 │ │ │ │ │ │ │ ├── fd: ()-->(46) 551 │ │ │ │ │ │ │ ├── scan region 552 │ │ │ │ │ │ │ │ ├── columns: r_regionkey:45!null r_name:46!null 553 │ │ │ │ │ │ │ │ ├── key: (45) 554 │ │ │ │ │ │ │ │ └── fd: (45)-->(46) 555 │ │ │ │ │ │ │ └── filters 556 │ │ │ │ │ │ │ └── r_name:46 = 'ASIA' [outer=(46), constraints=(/46: [/'ASIA' - /'ASIA']; tight), fd=()-->(46)] 557 │ │ │ │ │ │ └── filters 558 │ │ │ │ │ │ └── n_regionkey:43 = r_regionkey:45 [outer=(43,45), constraints=(/43: (/NULL - ]; /45: (/NULL - ]), fd=(43)==(45), (45)==(43)] 559 │ │ │ │ │ └── filters (true) 560 │ │ │ │ └── filters 561 │ │ │ │ └── l_suppkey:20 = s_suppkey:34 [outer=(20,34), constraints=(/20: (/NULL - ]; /34: (/NULL - ]), fd=(20)==(34), (34)==(20)] 562 │ │ │ ├── index-join orders 563 │ │ │ │ ├── columns: o_orderkey:9!null o_custkey:10!null o_orderdate:13!null 564 │ │ │ │ ├── key: (9) 565 │ │ │ │ ├── fd: (9)-->(10,13) 566 │ │ │ │ └── scan orders@o_od 567 │ │ │ │ ├── columns: o_orderkey:9!null o_orderdate:13!null 568 │ │ │ │ ├── constraint: /13/9: [/'1994-01-01' - /'1994-12-31'] 569 │ │ │ │ ├── key: (9) 570 │ │ │ │ └── fd: (9)-->(13) 571 │ │ │ └── filters 572 │ │ │ └── l_orderkey:18 = o_orderkey:9 [outer=(9,18), constraints=(/9: (/NULL - ]; /18: (/NULL - ]), fd=(9)==(18), (18)==(9)] 573 │ │ ├── scan customer@c_nk 574 │ │ │ ├── columns: c_custkey:1!null c_nationkey:4!null 575 │ │ │ ├── key: (1) 576 │ │ │ └── fd: (1)-->(4) 577 │ │ └── filters 578 │ │ ├── c_custkey:1 = o_custkey:10 [outer=(1,10), constraints=(/1: (/NULL - ]; /10: (/NULL - ]), fd=(1)==(10), (10)==(1)] 579 │ │ └── c_nationkey:4 = s_nationkey:37 [outer=(4,37), constraints=(/4: (/NULL - ]; /37: (/NULL - ]), fd=(4)==(37), (37)==(4)] 580 │ └── projections 581 │ └── l_extendedprice:23 * (1.0 - l_discount:24) [as=column48:48, outer=(23,24)] 582 └── aggregations 583 └── sum [as=sum:49, outer=(48)] 584 └── column48:48 585 586 # -------------------------------------------------- 587 # Q6 588 # Forecasting Revenue Change 589 # Quantifies the amount of revenue increase that would have resulted from 590 # eliminating certain companywide discounts in a given percentage range in a 591 # given year. Asking this type of "what if" query can be used to look for ways 592 # to increase revenues. 593 # 594 # Considers all the lineitems shipped in a given year with discounts between 595 # DISCOUNT-0.01 and DISCOUNT+0.01. The query lists the amount by which the total 596 # revenue would have increased if these discounts had been eliminated for 597 # lineitems with l_quantity less than quantity. Note that the potential revenue 598 # increase is equal to the sum of [l_extendedprice * l_discount] for all 599 # lineitems with discounts and quantities in the qualifying range. 600 # -------------------------------------------------- 601 opt 602 SELECT 603 sum(l_extendedprice * l_discount) AS revenue 604 FROM 605 lineitem 606 WHERE 607 l_shipdate >= DATE '1994-01-01' 608 AND l_shipdate < DATE '1994-01-01' + INTERVAL '1' YEAR 609 AND l_discount BETWEEN 0.06 - 0.01 AND 0.06 + 0.01 610 AND l_quantity < 24; 611 ---- 612 scalar-group-by 613 ├── columns: revenue:18 614 ├── cardinality: [1 - 1] 615 ├── key: () 616 ├── fd: ()-->(18) 617 ├── project 618 │ ├── columns: column17:17!null 619 │ ├── select 620 │ │ ├── columns: l_quantity:5!null l_extendedprice:6!null l_discount:7!null l_shipdate:11!null 621 │ │ ├── index-join lineitem 622 │ │ │ ├── columns: l_quantity:5!null l_extendedprice:6!null l_discount:7!null l_shipdate:11!null 623 │ │ │ └── scan lineitem@l_sd 624 │ │ │ ├── columns: l_orderkey:1!null l_linenumber:4!null l_shipdate:11!null 625 │ │ │ ├── constraint: /11/1/4: [/'1994-01-01' - /'1994-12-31'] 626 │ │ │ ├── key: (1,4) 627 │ │ │ └── fd: (1,4)-->(11) 628 │ │ └── filters 629 │ │ ├── (l_discount:7 >= 0.05) AND (l_discount:7 <= 0.07) [outer=(7), constraints=(/7: [/0.05 - /0.07]; tight)] 630 │ │ └── l_quantity:5 < 24.0 [outer=(5), constraints=(/5: (/NULL - /23.999999999999996]; tight)] 631 │ └── projections 632 │ └── l_extendedprice:6 * l_discount:7 [as=column17:17, outer=(6,7)] 633 └── aggregations 634 └── sum [as=sum:18, outer=(17)] 635 └── column17:17 636 637 # -------------------------------------------------- 638 # Q7 639 # Volume Shipping 640 # Determines the value of goods shipped between certain nations to help in the 641 # re-negotiation of shipping contracts. 642 # 643 # Finds, for two given nations, the gross discounted revenues derived from 644 # lineitems in which parts were shipped from a supplier in either nation to a 645 # customer in the other nation during 1995 and 1996. The query lists the 646 # supplier nation, the customer nation, the year, and the revenue from shipments 647 # that took place in that year. The query orders the answer by Supplier nation, 648 # Customer nation, and year (all ascending). 649 # 650 # TODO: 651 # 1. Join ordering 652 # -------------------------------------------------- 653 opt 654 SELECT 655 supp_nation, 656 cust_nation, 657 l_year, sum(volume) AS revenue 658 FROM ( 659 SELECT 660 n1.n_name AS supp_nation, 661 n2.n_name AS cust_nation, 662 extract(year FROM l_shipdate) AS l_year, 663 l_extendedprice * (1 - l_discount) AS volume 664 FROM 665 supplier, 666 lineitem, 667 orders, 668 customer, 669 nation n1, 670 nation n2 671 WHERE 672 s_suppkey = l_suppkey 673 AND o_orderkey = l_orderkey 674 AND c_custkey = o_custkey 675 AND s_nationkey = n1.n_nationkey 676 AND c_nationkey = n2.n_nationkey 677 AND ( 678 (n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY') 679 or (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE') 680 ) 681 AND l_shipdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31' 682 ) AS shipping 683 GROUP BY 684 supp_nation, 685 cust_nation, 686 l_year 687 ORDER BY 688 supp_nation, 689 cust_nation, 690 l_year; 691 ---- 692 sort 693 ├── columns: supp_nation:42!null cust_nation:46!null l_year:49 revenue:51!null 694 ├── immutable 695 ├── key: (42,46,49) 696 ├── fd: (42,46,49)-->(51) 697 ├── ordering: +42,+46,+49 698 └── group-by 699 ├── columns: n1.n_name:42!null n2.n_name:46!null l_year:49 sum:51!null 700 ├── grouping columns: n1.n_name:42!null n2.n_name:46!null l_year:49 701 ├── immutable 702 ├── key: (42,46,49) 703 ├── fd: (42,46,49)-->(51) 704 ├── project 705 │ ├── columns: l_year:49 volume:50!null n1.n_name:42!null n2.n_name:46!null 706 │ ├── immutable 707 │ ├── inner-join (hash) 708 │ │ ├── columns: s_suppkey:1!null s_nationkey:4!null l_orderkey:8!null l_suppkey:10!null l_extendedprice:13!null l_discount:14!null l_shipdate:18!null o_orderkey:24!null o_custkey:25!null c_custkey:33!null c_nationkey:36!null n1.n_nationkey:41!null n1.n_name:42!null n2.n_nationkey:45!null n2.n_name:46!null 709 │ │ ├── fd: (1)-->(4), (24)-->(25), (33)-->(36), (41)-->(42), (45)-->(46), (36)==(45), (45)==(36), (25)==(33), (33)==(25), (8)==(24), (24)==(8), (1)==(10), (10)==(1), (4)==(41), (41)==(4) 710 │ │ ├── inner-join (lookup lineitem) 711 │ │ │ ├── columns: l_orderkey:8!null l_suppkey:10!null l_extendedprice:13!null l_discount:14!null l_shipdate:18!null o_orderkey:24!null o_custkey:25!null c_custkey:33!null c_nationkey:36!null n1.n_nationkey:41!null n1.n_name:42!null n2.n_nationkey:45!null n2.n_name:46!null 712 │ │ │ ├── key columns: [24] = [8] 713 │ │ │ ├── fd: (24)-->(25), (33)-->(36), (41)-->(42), (45)-->(46), (36)==(45), (45)==(36), (25)==(33), (33)==(25), (8)==(24), (24)==(8) 714 │ │ │ ├── inner-join (lookup orders@o_ck) 715 │ │ │ │ ├── columns: o_orderkey:24!null o_custkey:25!null c_custkey:33!null c_nationkey:36!null n1.n_nationkey:41!null n1.n_name:42!null n2.n_nationkey:45!null n2.n_name:46!null 716 │ │ │ │ ├── key columns: [33] = [25] 717 │ │ │ │ ├── key: (24,41) 718 │ │ │ │ ├── fd: (24)-->(25), (33)-->(36), (41)-->(42), (45)-->(46), (36)==(45), (45)==(36), (25)==(33), (33)==(25) 719 │ │ │ │ ├── inner-join (lookup customer@c_nk) 720 │ │ │ │ │ ├── columns: c_custkey:33!null c_nationkey:36!null n1.n_nationkey:41!null n1.n_name:42!null n2.n_nationkey:45!null n2.n_name:46!null 721 │ │ │ │ │ ├── key columns: [45] = [36] 722 │ │ │ │ │ ├── key: (33,41) 723 │ │ │ │ │ ├── fd: (33)-->(36), (41)-->(42), (45)-->(46), (36)==(45), (45)==(36) 724 │ │ │ │ │ ├── inner-join (cross) 725 │ │ │ │ │ │ ├── columns: n1.n_nationkey:41!null n1.n_name:42!null n2.n_nationkey:45!null n2.n_name:46!null 726 │ │ │ │ │ │ ├── key: (41,45) 727 │ │ │ │ │ │ ├── fd: (41)-->(42), (45)-->(46) 728 │ │ │ │ │ │ ├── scan n1 729 │ │ │ │ │ │ │ ├── columns: n1.n_nationkey:41!null n1.n_name:42!null 730 │ │ │ │ │ │ │ ├── key: (41) 731 │ │ │ │ │ │ │ └── fd: (41)-->(42) 732 │ │ │ │ │ │ ├── scan n2 733 │ │ │ │ │ │ │ ├── columns: n2.n_nationkey:45!null n2.n_name:46!null 734 │ │ │ │ │ │ │ ├── key: (45) 735 │ │ │ │ │ │ │ └── fd: (45)-->(46) 736 │ │ │ │ │ │ └── filters 737 │ │ │ │ │ │ └── ((n1.n_name:42 = 'FRANCE') AND (n2.n_name:46 = 'GERMANY')) OR ((n1.n_name:42 = 'GERMANY') AND (n2.n_name:46 = 'FRANCE')) [outer=(42,46), constraints=(/42: [/'FRANCE' - /'FRANCE'] [/'GERMANY' - /'GERMANY']; /46: [/'FRANCE' - /'FRANCE'] [/'GERMANY' - /'GERMANY'])] 738 │ │ │ │ │ └── filters (true) 739 │ │ │ │ └── filters (true) 740 │ │ │ └── filters 741 │ │ │ └── (l_shipdate:18 >= '1995-01-01') AND (l_shipdate:18 <= '1996-12-31') [outer=(18), constraints=(/18: [/'1995-01-01' - /'1996-12-31']; tight)] 742 │ │ ├── scan supplier@s_nk 743 │ │ │ ├── columns: s_suppkey:1!null s_nationkey:4!null 744 │ │ │ ├── key: (1) 745 │ │ │ └── fd: (1)-->(4) 746 │ │ └── filters 747 │ │ ├── s_suppkey:1 = l_suppkey:10 [outer=(1,10), constraints=(/1: (/NULL - ]; /10: (/NULL - ]), fd=(1)==(10), (10)==(1)] 748 │ │ └── s_nationkey:4 = n1.n_nationkey:41 [outer=(4,41), constraints=(/4: (/NULL - ]; /41: (/NULL - ]), fd=(4)==(41), (41)==(4)] 749 │ └── projections 750 │ ├── extract('year', l_shipdate:18) [as=l_year:49, outer=(18), immutable] 751 │ └── l_extendedprice:13 * (1.0 - l_discount:14) [as=volume:50, outer=(13,14)] 752 └── aggregations 753 └── sum [as=sum:51, outer=(50)] 754 └── volume:50 755 756 # -------------------------------------------------- 757 # Q8 758 # National Market Share 759 # Determines how the market share of a given nation within a given region has 760 # changed over two years for a given part type. 761 # 762 # The market share for a given nation within a given region is defined as the 763 # fraction of the revenue, the sum of [l_extendedprice * (1-l_discount)], from 764 # the products of a specified type in that region that was supplied by suppliers 765 # from the given nation. The query determines this for the years 1995 and 1996 766 # presented in this order. 767 # 768 # TODO: 769 # 1. Join ordering 770 # 2. Push down equivalent column comparisons 771 # -------------------------------------------------- 772 opt 773 SELECT 774 o_year, 775 sum(CASE 776 WHEN nation = 'BRAZIL' 777 THEN volume 778 ELSE 0 779 END) / sum(volume) AS mkt_share 780 FROM ( 781 SELECT 782 extract(year FROM o_orderdate) AS o_year, 783 l_extendedprice * (1 - l_discount) AS volume, 784 n2.n_name AS nation 785 FROM 786 part, 787 supplier, 788 lineitem, 789 orders, 790 customer, 791 nation n1, 792 nation n2, 793 region 794 WHERE 795 p_partkey = l_partkey 796 AND s_suppkey = l_suppkey 797 AND l_orderkey = o_orderkey 798 AND o_custkey = c_custkey 799 AND c_nationkey = n1.n_nationkey 800 AND n1.n_regionkey = r_regionkey 801 AND r_name = 'AMERICA' 802 AND s_nationkey = n2.n_nationkey 803 AND o_orderdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31' 804 AND p_type = 'ECONOMY ANODIZED STEEL' 805 ) AS all_nations 806 GROUP BY 807 o_year 808 ORDER BY 809 o_year; 810 ---- 811 sort 812 ├── columns: o_year:61 mkt_share:66!null 813 ├── immutable, side-effects 814 ├── key: (61) 815 ├── fd: (61)-->(66) 816 ├── ordering: +61 817 └── project 818 ├── columns: mkt_share:66!null o_year:61 819 ├── immutable, side-effects 820 ├── key: (61) 821 ├── fd: (61)-->(66) 822 ├── group-by 823 │ ├── columns: o_year:61 sum:64!null sum:65!null 824 │ ├── grouping columns: o_year:61 825 │ ├── immutable 826 │ ├── key: (61) 827 │ ├── fd: (61)-->(64,65) 828 │ ├── project 829 │ │ ├── columns: column63:63!null o_year:61 volume:62!null 830 │ │ ├── immutable 831 │ │ ├── project 832 │ │ │ ├── columns: o_year:61 volume:62!null n2.n_name:55!null 833 │ │ │ ├── immutable 834 │ │ │ ├── inner-join (hash) 835 │ │ │ │ ├── columns: p_partkey:1!null p_type:5!null s_suppkey:10!null s_nationkey:13!null l_orderkey:17!null l_partkey:18!null l_suppkey:19!null l_extendedprice:22!null l_discount:23!null o_orderkey:33!null o_custkey:34!null o_orderdate:37!null c_custkey:42!null c_nationkey:45!null n1.n_nationkey:50!null n1.n_regionkey:52!null n2.n_nationkey:54!null n2.n_name:55!null r_regionkey:58!null r_name:59!null 836 │ │ │ │ ├── fd: ()-->(5,59), (10)-->(13), (33)-->(34,37), (42)-->(45), (50)-->(52), (54)-->(55), (52)==(58), (58)==(52), (45)==(50), (50)==(45), (34)==(42), (42)==(34), (17)==(33), (33)==(17), (10)==(19), (19)==(10), (13)==(54), (54)==(13), (1)==(18), (18)==(1) 837 │ │ │ │ ├── inner-join (hash) 838 │ │ │ │ │ ├── columns: s_suppkey:10!null s_nationkey:13!null l_orderkey:17!null l_partkey:18!null l_suppkey:19!null l_extendedprice:22!null l_discount:23!null o_orderkey:33!null o_custkey:34!null o_orderdate:37!null c_custkey:42!null c_nationkey:45!null n1.n_nationkey:50!null n1.n_regionkey:52!null n2.n_nationkey:54!null n2.n_name:55!null r_regionkey:58!null r_name:59!null 839 │ │ │ │ │ ├── fd: ()-->(59), (10)-->(13), (33)-->(34,37), (42)-->(45), (50)-->(52), (54)-->(55), (52)==(58), (58)==(52), (45)==(50), (50)==(45), (34)==(42), (42)==(34), (17)==(33), (33)==(17), (10)==(19), (19)==(10), (13)==(54), (54)==(13) 840 │ │ │ │ │ ├── inner-join (hash) 841 │ │ │ │ │ │ ├── columns: l_orderkey:17!null l_partkey:18!null l_suppkey:19!null l_extendedprice:22!null l_discount:23!null o_orderkey:33!null o_custkey:34!null o_orderdate:37!null c_custkey:42!null c_nationkey:45!null n1.n_nationkey:50!null n1.n_regionkey:52!null n2.n_nationkey:54!null n2.n_name:55!null r_regionkey:58!null r_name:59!null 842 │ │ │ │ │ │ ├── fd: ()-->(59), (33)-->(34,37), (42)-->(45), (50)-->(52), (54)-->(55), (52)==(58), (58)==(52), (45)==(50), (50)==(45), (34)==(42), (42)==(34), (17)==(33), (33)==(17) 843 │ │ │ │ │ │ ├── scan lineitem 844 │ │ │ │ │ │ │ └── columns: l_orderkey:17!null l_partkey:18!null l_suppkey:19!null l_extendedprice:22!null l_discount:23!null 845 │ │ │ │ │ │ ├── inner-join (hash) 846 │ │ │ │ │ │ │ ├── columns: o_orderkey:33!null o_custkey:34!null o_orderdate:37!null c_custkey:42!null c_nationkey:45!null n1.n_nationkey:50!null n1.n_regionkey:52!null n2.n_nationkey:54!null n2.n_name:55!null r_regionkey:58!null r_name:59!null 847 │ │ │ │ │ │ │ ├── key: (33,54) 848 │ │ │ │ │ │ │ ├── fd: ()-->(59), (33)-->(34,37), (42)-->(45), (50)-->(52), (54)-->(55), (52)==(58), (58)==(52), (45)==(50), (50)==(45), (34)==(42), (42)==(34) 849 │ │ │ │ │ │ │ ├── inner-join (cross) 850 │ │ │ │ │ │ │ │ ├── columns: c_custkey:42!null c_nationkey:45!null n1.n_nationkey:50!null n1.n_regionkey:52!null n2.n_nationkey:54!null n2.n_name:55!null r_regionkey:58!null r_name:59!null 851 │ │ │ │ │ │ │ │ ├── key: (42,54) 852 │ │ │ │ │ │ │ │ ├── fd: ()-->(59), (42)-->(45), (50)-->(52), (54)-->(55), (52)==(58), (58)==(52), (45)==(50), (50)==(45) 853 │ │ │ │ │ │ │ │ ├── inner-join (lookup customer@c_nk) 854 │ │ │ │ │ │ │ │ │ ├── columns: c_custkey:42!null c_nationkey:45!null n1.n_nationkey:50!null n1.n_regionkey:52!null r_regionkey:58!null r_name:59!null 855 │ │ │ │ │ │ │ │ │ ├── key columns: [50] = [45] 856 │ │ │ │ │ │ │ │ │ ├── key: (42) 857 │ │ │ │ │ │ │ │ │ ├── fd: ()-->(59), (50)-->(52), (52)==(58), (58)==(52), (42)-->(45), (45)==(50), (50)==(45) 858 │ │ │ │ │ │ │ │ │ ├── inner-join (merge) 859 │ │ │ │ │ │ │ │ │ │ ├── columns: n1.n_nationkey:50!null n1.n_regionkey:52!null r_regionkey:58!null r_name:59!null 860 │ │ │ │ │ │ │ │ │ │ ├── left ordering: +58 861 │ │ │ │ │ │ │ │ │ │ ├── right ordering: +52 862 │ │ │ │ │ │ │ │ │ │ ├── key: (50) 863 │ │ │ │ │ │ │ │ │ │ ├── fd: ()-->(59), (50)-->(52), (52)==(58), (58)==(52) 864 │ │ │ │ │ │ │ │ │ │ ├── select 865 │ │ │ │ │ │ │ │ │ │ │ ├── columns: r_regionkey:58!null r_name:59!null 866 │ │ │ │ │ │ │ │ │ │ │ ├── key: (58) 867 │ │ │ │ │ │ │ │ │ │ │ ├── fd: ()-->(59) 868 │ │ │ │ │ │ │ │ │ │ │ ├── ordering: +58 opt(59) [actual: +58] 869 │ │ │ │ │ │ │ │ │ │ │ ├── scan region 870 │ │ │ │ │ │ │ │ │ │ │ │ ├── columns: r_regionkey:58!null r_name:59!null 871 │ │ │ │ │ │ │ │ │ │ │ │ ├── key: (58) 872 │ │ │ │ │ │ │ │ │ │ │ │ ├── fd: (58)-->(59) 873 │ │ │ │ │ │ │ │ │ │ │ │ └── ordering: +58 opt(59) [actual: +58] 874 │ │ │ │ │ │ │ │ │ │ │ └── filters 875 │ │ │ │ │ │ │ │ │ │ │ └── r_name:59 = 'AMERICA' [outer=(59), constraints=(/59: [/'AMERICA' - /'AMERICA']; tight), fd=()-->(59)] 876 │ │ │ │ │ │ │ │ │ │ ├── scan n1@n_rk 877 │ │ │ │ │ │ │ │ │ │ │ ├── columns: n1.n_nationkey:50!null n1.n_regionkey:52!null 878 │ │ │ │ │ │ │ │ │ │ │ ├── key: (50) 879 │ │ │ │ │ │ │ │ │ │ │ ├── fd: (50)-->(52) 880 │ │ │ │ │ │ │ │ │ │ │ └── ordering: +52 881 │ │ │ │ │ │ │ │ │ │ └── filters (true) 882 │ │ │ │ │ │ │ │ │ └── filters (true) 883 │ │ │ │ │ │ │ │ ├── scan n2 884 │ │ │ │ │ │ │ │ │ ├── columns: n2.n_nationkey:54!null n2.n_name:55!null 885 │ │ │ │ │ │ │ │ │ ├── key: (54) 886 │ │ │ │ │ │ │ │ │ └── fd: (54)-->(55) 887 │ │ │ │ │ │ │ │ └── filters (true) 888 │ │ │ │ │ │ │ ├── select 889 │ │ │ │ │ │ │ │ ├── columns: o_orderkey:33!null o_custkey:34!null o_orderdate:37!null 890 │ │ │ │ │ │ │ │ ├── key: (33) 891 │ │ │ │ │ │ │ │ ├── fd: (33)-->(34,37) 892 │ │ │ │ │ │ │ │ ├── scan orders 893 │ │ │ │ │ │ │ │ │ ├── columns: o_orderkey:33!null o_custkey:34!null o_orderdate:37!null 894 │ │ │ │ │ │ │ │ │ ├── key: (33) 895 │ │ │ │ │ │ │ │ │ └── fd: (33)-->(34,37) 896 │ │ │ │ │ │ │ │ └── filters 897 │ │ │ │ │ │ │ │ └── (o_orderdate:37 >= '1995-01-01') AND (o_orderdate:37 <= '1996-12-31') [outer=(37), constraints=(/37: [/'1995-01-01' - /'1996-12-31']; tight)] 898 │ │ │ │ │ │ │ └── filters 899 │ │ │ │ │ │ │ └── o_custkey:34 = c_custkey:42 [outer=(34,42), constraints=(/34: (/NULL - ]; /42: (/NULL - ]), fd=(34)==(42), (42)==(34)] 900 │ │ │ │ │ │ └── filters 901 │ │ │ │ │ │ └── l_orderkey:17 = o_orderkey:33 [outer=(17,33), constraints=(/17: (/NULL - ]; /33: (/NULL - ]), fd=(17)==(33), (33)==(17)] 902 │ │ │ │ │ ├── scan supplier@s_nk 903 │ │ │ │ │ │ ├── columns: s_suppkey:10!null s_nationkey:13!null 904 │ │ │ │ │ │ ├── key: (10) 905 │ │ │ │ │ │ └── fd: (10)-->(13) 906 │ │ │ │ │ └── filters 907 │ │ │ │ │ ├── s_suppkey:10 = l_suppkey:19 [outer=(10,19), constraints=(/10: (/NULL - ]; /19: (/NULL - ]), fd=(10)==(19), (19)==(10)] 908 │ │ │ │ │ └── s_nationkey:13 = n2.n_nationkey:54 [outer=(13,54), constraints=(/13: (/NULL - ]; /54: (/NULL - ]), fd=(13)==(54), (54)==(13)] 909 │ │ │ │ ├── select 910 │ │ │ │ │ ├── columns: p_partkey:1!null p_type:5!null 911 │ │ │ │ │ ├── key: (1) 912 │ │ │ │ │ ├── fd: ()-->(5) 913 │ │ │ │ │ ├── scan part 914 │ │ │ │ │ │ ├── columns: p_partkey:1!null p_type:5!null 915 │ │ │ │ │ │ ├── key: (1) 916 │ │ │ │ │ │ └── fd: (1)-->(5) 917 │ │ │ │ │ └── filters 918 │ │ │ │ │ └── p_type:5 = 'ECONOMY ANODIZED STEEL' [outer=(5), constraints=(/5: [/'ECONOMY ANODIZED STEEL' - /'ECONOMY ANODIZED STEEL']; tight), fd=()-->(5)] 919 │ │ │ │ └── filters 920 │ │ │ │ └── p_partkey:1 = l_partkey:18 [outer=(1,18), constraints=(/1: (/NULL - ]; /18: (/NULL - ]), fd=(1)==(18), (18)==(1)] 921 │ │ │ └── projections 922 │ │ │ ├── extract('year', o_orderdate:37) [as=o_year:61, outer=(37), immutable] 923 │ │ │ └── l_extendedprice:22 * (1.0 - l_discount:23) [as=volume:62, outer=(22,23)] 924 │ │ └── projections 925 │ │ └── CASE WHEN n2.n_name:55 = 'BRAZIL' THEN volume:62 ELSE 0.0 END [as=column63:63, outer=(55,62)] 926 │ └── aggregations 927 │ ├── sum [as=sum:64, outer=(63)] 928 │ │ └── column63:63 929 │ └── sum [as=sum:65, outer=(62)] 930 │ └── volume:62 931 └── projections 932 └── sum:64 / sum:65 [as=mkt_share:66, outer=(64,65), immutable, side-effects] 933 934 # -------------------------------------------------- 935 # Q9 936 # Product Type Profit Measure 937 # Determines how much profit is made on a given line of parts, broken out by 938 # supplier nation and year. 939 # 940 # Finds, for each nation and each year, the profit for all parts ordered in that 941 # year that contain a specified substring in their names and that were filled by 942 # a supplier in that nation. The profit is defined as the sum of: 943 # 944 # [(l_extendedprice*(1-l_discount)) - (ps_supplycost * l_quantity)] 945 # 946 # for all lineitems describing parts in the specified line. The query lists the 947 # nations in ascending alphabetical order and, for each nation, the year and 948 # profit in descending order by year (most recent first). 949 # 950 # TODO: 951 # 1. Join ordering 952 # 2. Push down equivalent column comparisons 953 # -------------------------------------------------- 954 opt 955 SELECT 956 nation, 957 o_year, 958 sum(amount) AS sum_profit 959 FROM ( 960 SELECT 961 n_name AS nation, 962 extract(year FROM o_orderdate) AS o_year, 963 l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount 964 FROM 965 part, 966 supplier, 967 lineitem, 968 partsupp, 969 orders, 970 nation 971 WHERE 972 s_suppkey = l_suppkey 973 AND ps_suppkey = l_suppkey 974 AND ps_partkey = l_partkey 975 AND p_partkey = l_partkey 976 AND o_orderkey = l_orderkey 977 AND s_nationkey = n_nationkey 978 AND p_name LIKE '%green%' 979 ) AS profit 980 GROUP BY 981 nation, 982 o_year 983 ORDER BY 984 nation, 985 o_year DESC; 986 ---- 987 sort 988 ├── columns: nation:48!null o_year:51 sum_profit:53!null 989 ├── immutable 990 ├── key: (48,51) 991 ├── fd: (48,51)-->(53) 992 ├── ordering: +48,-51 993 └── group-by 994 ├── columns: n_name:48!null o_year:51 sum:53!null 995 ├── grouping columns: n_name:48!null o_year:51 996 ├── immutable 997 ├── key: (48,51) 998 ├── fd: (48,51)-->(53) 999 ├── project 1000 │ ├── columns: o_year:51 amount:52!null n_name:48!null 1001 │ ├── immutable 1002 │ ├── inner-join (lookup part) 1003 │ │ ├── columns: p_partkey:1!null p_name:2!null s_suppkey:10!null s_nationkey:13!null l_orderkey:17!null l_partkey:18!null l_suppkey:19!null l_quantity:21!null l_extendedprice:22!null l_discount:23!null ps_partkey:33!null ps_suppkey:34!null ps_supplycost:36!null o_orderkey:38!null o_orderdate:42!null n_nationkey:47!null n_name:48!null 1004 │ │ ├── key columns: [18] = [1] 1005 │ │ ├── lookup columns are key 1006 │ │ ├── fd: (1)-->(2), (10)-->(13), (33,34)-->(36), (38)-->(42), (47)-->(48), (19)==(10,34), (34)==(10,19), (18)==(1,33), (33)==(1,18), (17)==(38), (38)==(17), (10)==(19,34), (13)==(47), (47)==(13), (1)==(18,33) 1007 │ │ ├── inner-join (hash) 1008 │ │ │ ├── columns: s_suppkey:10!null s_nationkey:13!null l_orderkey:17!null l_partkey:18!null l_suppkey:19!null l_quantity:21!null l_extendedprice:22!null l_discount:23!null ps_partkey:33!null ps_suppkey:34!null ps_supplycost:36!null o_orderkey:38!null o_orderdate:42!null n_nationkey:47!null n_name:48!null 1009 │ │ │ ├── fd: (10)-->(13), (33,34)-->(36), (38)-->(42), (47)-->(48), (19)==(10,34), (34)==(10,19), (18)==(33), (33)==(18), (17)==(38), (38)==(17), (10)==(19,34), (13)==(47), (47)==(13) 1010 │ │ │ ├── inner-join (cross) 1011 │ │ │ │ ├── columns: l_orderkey:17!null l_partkey:18!null l_suppkey:19!null l_quantity:21!null l_extendedprice:22!null l_discount:23!null ps_partkey:33!null ps_suppkey:34!null ps_supplycost:36!null o_orderkey:38!null o_orderdate:42!null n_nationkey:47!null n_name:48!null 1012 │ │ │ │ ├── fd: (33,34)-->(36), (38)-->(42), (47)-->(48), (19)==(34), (34)==(19), (18)==(33), (33)==(18), (17)==(38), (38)==(17) 1013 │ │ │ │ ├── inner-join (lookup orders) 1014 │ │ │ │ │ ├── columns: l_orderkey:17!null l_partkey:18!null l_suppkey:19!null l_quantity:21!null l_extendedprice:22!null l_discount:23!null ps_partkey:33!null ps_suppkey:34!null ps_supplycost:36!null o_orderkey:38!null o_orderdate:42!null 1015 │ │ │ │ │ ├── key columns: [17] = [38] 1016 │ │ │ │ │ ├── lookup columns are key 1017 │ │ │ │ │ ├── fd: (38)-->(42), (33,34)-->(36), (19)==(34), (34)==(19), (18)==(33), (33)==(18), (17)==(38), (38)==(17) 1018 │ │ │ │ │ ├── inner-join (hash) 1019 │ │ │ │ │ │ ├── columns: l_orderkey:17!null l_partkey:18!null l_suppkey:19!null l_quantity:21!null l_extendedprice:22!null l_discount:23!null ps_partkey:33!null ps_suppkey:34!null ps_supplycost:36!null 1020 │ │ │ │ │ │ ├── fd: (33,34)-->(36), (19)==(34), (34)==(19), (18)==(33), (33)==(18) 1021 │ │ │ │ │ │ ├── scan lineitem 1022 │ │ │ │ │ │ │ └── columns: l_orderkey:17!null l_partkey:18!null l_suppkey:19!null l_quantity:21!null l_extendedprice:22!null l_discount:23!null 1023 │ │ │ │ │ │ ├── scan partsupp 1024 │ │ │ │ │ │ │ ├── columns: ps_partkey:33!null ps_suppkey:34!null ps_supplycost:36!null 1025 │ │ │ │ │ │ │ ├── key: (33,34) 1026 │ │ │ │ │ │ │ └── fd: (33,34)-->(36) 1027 │ │ │ │ │ │ └── filters 1028 │ │ │ │ │ │ ├── ps_suppkey:34 = l_suppkey:19 [outer=(19,34), constraints=(/19: (/NULL - ]; /34: (/NULL - ]), fd=(19)==(34), (34)==(19)] 1029 │ │ │ │ │ │ └── ps_partkey:33 = l_partkey:18 [outer=(18,33), constraints=(/18: (/NULL - ]; /33: (/NULL - ]), fd=(18)==(33), (33)==(18)] 1030 │ │ │ │ │ └── filters (true) 1031 │ │ │ │ ├── scan nation 1032 │ │ │ │ │ ├── columns: n_nationkey:47!null n_name:48!null 1033 │ │ │ │ │ ├── key: (47) 1034 │ │ │ │ │ └── fd: (47)-->(48) 1035 │ │ │ │ └── filters (true) 1036 │ │ │ ├── scan supplier@s_nk 1037 │ │ │ │ ├── columns: s_suppkey:10!null s_nationkey:13!null 1038 │ │ │ │ ├── key: (10) 1039 │ │ │ │ └── fd: (10)-->(13) 1040 │ │ │ └── filters 1041 │ │ │ ├── s_suppkey:10 = l_suppkey:19 [outer=(10,19), constraints=(/10: (/NULL - ]; /19: (/NULL - ]), fd=(10)==(19), (19)==(10)] 1042 │ │ │ └── s_nationkey:13 = n_nationkey:47 [outer=(13,47), constraints=(/13: (/NULL - ]; /47: (/NULL - ]), fd=(13)==(47), (47)==(13)] 1043 │ │ └── filters 1044 │ │ └── p_name:2 LIKE '%green%' [outer=(2), constraints=(/2: (/NULL - ])] 1045 │ └── projections 1046 │ ├── extract('year', o_orderdate:42) [as=o_year:51, outer=(42), immutable] 1047 │ └── (l_extendedprice:22 * (1.0 - l_discount:23)) - (ps_supplycost:36 * l_quantity:21) [as=amount:52, outer=(21-23,36)] 1048 └── aggregations 1049 └── sum [as=sum:53, outer=(52)] 1050 └── amount:52 1051 1052 # -------------------------------------------------- 1053 # Q10 1054 # Returned Item Reporting 1055 # Identifies customers who might be having problems with the parts that are 1056 # shipped to them. 1057 # 1058 # Finds the top 20 customers, in terms of their effect on lost revenue for a 1059 # given quarter, who have returned parts. The query considers only parts that 1060 # were ordered in the specified quarter. The query lists the customer's name, 1061 # address, nation, phone number, account balance, comment information and 1062 # revenue lost. The customers are listed in descending order of lost revenue. 1063 # Revenue lost is defined as sum(l_extendedprice*(1-l_discount)) for all 1064 # qualifying lineitems. 1065 # -------------------------------------------------- 1066 opt 1067 SELECT 1068 c_custkey, 1069 c_name, 1070 sum(l_extendedprice * (1 - l_discount)) AS revenue, 1071 c_acctbal, 1072 n_name, 1073 c_address, 1074 c_phone, 1075 c_comment 1076 FROM 1077 customer, 1078 orders, 1079 lineitem, 1080 nation 1081 WHERE 1082 c_custkey = o_custkey 1083 AND l_orderkey = o_orderkey 1084 AND o_orderDATE >= DATE '1993-10-01' 1085 AND o_orderDATE < DATE '1993-10-01' + INTERVAL '3' MONTH 1086 AND l_returnflag = 'R' 1087 AND c_nationkey = n_nationkey 1088 GROUP BY 1089 c_custkey, 1090 c_name, 1091 c_acctbal, 1092 c_phone, 1093 n_name, 1094 c_address, 1095 c_comment 1096 ORDER BY 1097 revenue DESC 1098 LIMIT 20; 1099 ---- 1100 limit 1101 ├── columns: c_custkey:1!null c_name:2!null revenue:39!null c_acctbal:6!null n_name:35!null c_address:3!null c_phone:5!null c_comment:8!null 1102 ├── internal-ordering: -39 1103 ├── cardinality: [0 - 20] 1104 ├── key: (1) 1105 ├── fd: (1)-->(2,3,5,6,8,35,39) 1106 ├── ordering: -39 1107 ├── sort 1108 │ ├── columns: c_custkey:1!null c_name:2!null c_address:3!null c_phone:5!null c_acctbal:6!null c_comment:8!null n_name:35!null sum:39!null 1109 │ ├── key: (1) 1110 │ ├── fd: (1)-->(2,3,5,6,8,35,39) 1111 │ ├── ordering: -39 1112 │ ├── limit hint: 20.00 1113 │ └── group-by 1114 │ ├── columns: c_custkey:1!null c_name:2!null c_address:3!null c_phone:5!null c_acctbal:6!null c_comment:8!null n_name:35!null sum:39!null 1115 │ ├── grouping columns: c_custkey:1!null 1116 │ ├── key: (1) 1117 │ ├── fd: (1)-->(2,3,5,6,8,35,39) 1118 │ ├── project 1119 │ │ ├── columns: column38:38!null c_custkey:1!null c_name:2!null c_address:3!null c_phone:5!null c_acctbal:6!null c_comment:8!null n_name:35!null 1120 │ │ ├── fd: (1)-->(2,3,5,6,8,35) 1121 │ │ ├── inner-join (hash) 1122 │ │ │ ├── columns: c_custkey:1!null c_name:2!null c_address:3!null c_nationkey:4!null c_phone:5!null c_acctbal:6!null c_comment:8!null o_orderkey:9!null o_custkey:10!null o_orderdate:13!null l_orderkey:18!null l_extendedprice:23!null l_discount:24!null l_returnflag:26!null n_nationkey:34!null n_name:35!null 1123 │ │ │ ├── fd: ()-->(26), (1)-->(2-6,8), (9)-->(10,13), (34)-->(35), (9)==(18), (18)==(9), (1)==(10), (10)==(1), (4)==(34), (34)==(4) 1124 │ │ │ ├── inner-join (hash) 1125 │ │ │ │ ├── columns: c_custkey:1!null c_name:2!null c_address:3!null c_nationkey:4!null c_phone:5!null c_acctbal:6!null c_comment:8!null o_orderkey:9!null o_custkey:10!null o_orderdate:13!null l_orderkey:18!null l_extendedprice:23!null l_discount:24!null l_returnflag:26!null 1126 │ │ │ │ ├── fd: ()-->(26), (9)-->(10,13), (9)==(18), (18)==(9), (1)-->(2-6,8), (1)==(10), (10)==(1) 1127 │ │ │ │ ├── scan customer 1128 │ │ │ │ │ ├── columns: c_custkey:1!null c_name:2!null c_address:3!null c_nationkey:4!null c_phone:5!null c_acctbal:6!null c_comment:8!null 1129 │ │ │ │ │ ├── key: (1) 1130 │ │ │ │ │ └── fd: (1)-->(2-6,8) 1131 │ │ │ │ ├── inner-join (lookup lineitem) 1132 │ │ │ │ │ ├── columns: o_orderkey:9!null o_custkey:10!null o_orderdate:13!null l_orderkey:18!null l_extendedprice:23!null l_discount:24!null l_returnflag:26!null 1133 │ │ │ │ │ ├── key columns: [9] = [18] 1134 │ │ │ │ │ ├── fd: ()-->(26), (9)-->(10,13), (9)==(18), (18)==(9) 1135 │ │ │ │ │ ├── index-join orders 1136 │ │ │ │ │ │ ├── columns: o_orderkey:9!null o_custkey:10!null o_orderdate:13!null 1137 │ │ │ │ │ │ ├── key: (9) 1138 │ │ │ │ │ │ ├── fd: (9)-->(10,13) 1139 │ │ │ │ │ │ └── scan orders@o_od 1140 │ │ │ │ │ │ ├── columns: o_orderkey:9!null o_orderdate:13!null 1141 │ │ │ │ │ │ ├── constraint: /13/9: [/'1993-10-01' - /'1993-12-31'] 1142 │ │ │ │ │ │ ├── key: (9) 1143 │ │ │ │ │ │ └── fd: (9)-->(13) 1144 │ │ │ │ │ └── filters 1145 │ │ │ │ │ └── l_returnflag:26 = 'R' [outer=(26), constraints=(/26: [/'R' - /'R']; tight), fd=()-->(26)] 1146 │ │ │ │ └── filters 1147 │ │ │ │ └── c_custkey:1 = o_custkey:10 [outer=(1,10), constraints=(/1: (/NULL - ]; /10: (/NULL - ]), fd=(1)==(10), (10)==(1)] 1148 │ │ │ ├── scan nation 1149 │ │ │ │ ├── columns: n_nationkey:34!null n_name:35!null 1150 │ │ │ │ ├── key: (34) 1151 │ │ │ │ └── fd: (34)-->(35) 1152 │ │ │ └── filters 1153 │ │ │ └── c_nationkey:4 = n_nationkey:34 [outer=(4,34), constraints=(/4: (/NULL - ]; /34: (/NULL - ]), fd=(4)==(34), (34)==(4)] 1154 │ │ └── projections 1155 │ │ └── l_extendedprice:23 * (1.0 - l_discount:24) [as=column38:38, outer=(23,24)] 1156 │ └── aggregations 1157 │ ├── sum [as=sum:39, outer=(38)] 1158 │ │ └── column38:38 1159 │ ├── const-agg [as=c_name:2, outer=(2)] 1160 │ │ └── c_name:2 1161 │ ├── const-agg [as=c_address:3, outer=(3)] 1162 │ │ └── c_address:3 1163 │ ├── const-agg [as=c_phone:5, outer=(5)] 1164 │ │ └── c_phone:5 1165 │ ├── const-agg [as=c_acctbal:6, outer=(6)] 1166 │ │ └── c_acctbal:6 1167 │ ├── const-agg [as=c_comment:8, outer=(8)] 1168 │ │ └── c_comment:8 1169 │ └── const-agg [as=n_name:35, outer=(35)] 1170 │ └── n_name:35 1171 └── 20 1172 1173 # -------------------------------------------------- 1174 # Q11 1175 # Important Stock Identification 1176 # Finds the most important subset of suppliers' stock in a given nation. 1177 # 1178 # Finds, from scanning the available stock of suppliers in a given nation, all 1179 # the parts that represent a significant percentage of the total value of all 1180 # available parts. The query displays the part number and the value of those 1181 # parts in descending order of value. 1182 # -------------------------------------------------- 1183 opt 1184 SELECT 1185 ps_partkey, 1186 sum(ps_supplycost * ps_availqty::float) AS value 1187 FROM 1188 partsupp, 1189 supplier, 1190 nation 1191 WHERE 1192 ps_suppkey = s_suppkey 1193 AND s_nationkey = n_nationkey 1194 AND n_name = 'GERMANY' 1195 GROUP BY 1196 ps_partkey HAVING 1197 sum(ps_supplycost * ps_availqty::float) > ( 1198 SELECT 1199 sum(ps_supplycost * ps_availqty::float) * 0.0001 1200 FROM 1201 partsupp, 1202 supplier, 1203 nation 1204 WHERE 1205 ps_suppkey = s_suppkey 1206 AND s_nationkey = n_nationkey 1207 AND n_name = 'GERMANY' 1208 ) 1209 ORDER BY 1210 value DESC; 1211 ---- 1212 sort 1213 ├── columns: ps_partkey:1!null value:18!null 1214 ├── key: (1) 1215 ├── fd: (1)-->(18) 1216 ├── ordering: -18 1217 └── select 1218 ├── columns: ps_partkey:1!null sum:18!null 1219 ├── key: (1) 1220 ├── fd: (1)-->(18) 1221 ├── group-by 1222 │ ├── columns: ps_partkey:1!null sum:18!null 1223 │ ├── grouping columns: ps_partkey:1!null 1224 │ ├── key: (1) 1225 │ ├── fd: (1)-->(18) 1226 │ ├── project 1227 │ │ ├── columns: column17:17!null ps_partkey:1!null 1228 │ │ ├── inner-join (lookup partsupp) 1229 │ │ │ ├── columns: ps_partkey:1!null ps_suppkey:2!null ps_availqty:3!null ps_supplycost:4!null s_suppkey:6!null s_nationkey:9!null n_nationkey:13!null n_name:14!null 1230 │ │ │ ├── key columns: [1 2] = [1 2] 1231 │ │ │ ├── lookup columns are key 1232 │ │ │ ├── key: (1,6) 1233 │ │ │ ├── fd: ()-->(14), (1,2)-->(3,4), (6)-->(9), (9)==(13), (13)==(9), (2)==(6), (6)==(2) 1234 │ │ │ ├── inner-join (lookup partsupp@ps_sk) 1235 │ │ │ │ ├── columns: ps_partkey:1!null ps_suppkey:2!null s_suppkey:6!null s_nationkey:9!null n_nationkey:13!null n_name:14!null 1236 │ │ │ │ ├── key columns: [6] = [2] 1237 │ │ │ │ ├── key: (1,6) 1238 │ │ │ │ ├── fd: ()-->(14), (6)-->(9), (9)==(13), (13)==(9), (2)==(6), (6)==(2) 1239 │ │ │ │ ├── inner-join (lookup supplier@s_nk) 1240 │ │ │ │ │ ├── columns: s_suppkey:6!null s_nationkey:9!null n_nationkey:13!null n_name:14!null 1241 │ │ │ │ │ ├── key columns: [13] = [9] 1242 │ │ │ │ │ ├── key: (6) 1243 │ │ │ │ │ ├── fd: ()-->(14), (6)-->(9), (9)==(13), (13)==(9) 1244 │ │ │ │ │ ├── select 1245 │ │ │ │ │ │ ├── columns: n_nationkey:13!null n_name:14!null 1246 │ │ │ │ │ │ ├── key: (13) 1247 │ │ │ │ │ │ ├── fd: ()-->(14) 1248 │ │ │ │ │ │ ├── scan nation 1249 │ │ │ │ │ │ │ ├── columns: n_nationkey:13!null n_name:14!null 1250 │ │ │ │ │ │ │ ├── key: (13) 1251 │ │ │ │ │ │ │ └── fd: (13)-->(14) 1252 │ │ │ │ │ │ └── filters 1253 │ │ │ │ │ │ └── n_name:14 = 'GERMANY' [outer=(14), constraints=(/14: [/'GERMANY' - /'GERMANY']; tight), fd=()-->(14)] 1254 │ │ │ │ │ └── filters (true) 1255 │ │ │ │ └── filters (true) 1256 │ │ │ └── filters (true) 1257 │ │ └── projections 1258 │ │ └── ps_supplycost:4 * ps_availqty:3::FLOAT8 [as=column17:17, outer=(3,4)] 1259 │ └── aggregations 1260 │ └── sum [as=sum:18, outer=(17)] 1261 │ └── column17:17 1262 └── filters 1263 └── gt [outer=(18), subquery, constraints=(/18: (/NULL - ])] 1264 ├── sum:18 1265 └── subquery 1266 └── project 1267 ├── columns: "?column?":37 1268 ├── cardinality: [1 - 1] 1269 ├── key: () 1270 ├── fd: ()-->(37) 1271 ├── scalar-group-by 1272 │ ├── columns: sum:36 1273 │ ├── cardinality: [1 - 1] 1274 │ ├── key: () 1275 │ ├── fd: ()-->(36) 1276 │ ├── project 1277 │ │ ├── columns: column35:35!null 1278 │ │ ├── inner-join (lookup partsupp) 1279 │ │ │ ├── columns: ps_suppkey:20!null ps_availqty:21!null ps_supplycost:22!null s_suppkey:24!null s_nationkey:27!null n_nationkey:31!null n_name:32!null 1280 │ │ │ ├── key columns: [19 20] = [19 20] 1281 │ │ │ ├── lookup columns are key 1282 │ │ │ ├── fd: ()-->(32), (24)-->(27), (27)==(31), (31)==(27), (20)==(24), (24)==(20) 1283 │ │ │ ├── inner-join (lookup partsupp@ps_sk) 1284 │ │ │ │ ├── columns: ps_partkey:19!null ps_suppkey:20!null s_suppkey:24!null s_nationkey:27!null n_nationkey:31!null n_name:32!null 1285 │ │ │ │ ├── key columns: [24] = [20] 1286 │ │ │ │ ├── key: (19,24) 1287 │ │ │ │ ├── fd: ()-->(32), (24)-->(27), (27)==(31), (31)==(27), (20)==(24), (24)==(20) 1288 │ │ │ │ ├── inner-join (lookup supplier@s_nk) 1289 │ │ │ │ │ ├── columns: s_suppkey:24!null s_nationkey:27!null n_nationkey:31!null n_name:32!null 1290 │ │ │ │ │ ├── key columns: [31] = [27] 1291 │ │ │ │ │ ├── key: (24) 1292 │ │ │ │ │ ├── fd: ()-->(32), (24)-->(27), (27)==(31), (31)==(27) 1293 │ │ │ │ │ ├── select 1294 │ │ │ │ │ │ ├── columns: n_nationkey:31!null n_name:32!null 1295 │ │ │ │ │ │ ├── key: (31) 1296 │ │ │ │ │ │ ├── fd: ()-->(32) 1297 │ │ │ │ │ │ ├── scan nation 1298 │ │ │ │ │ │ │ ├── columns: n_nationkey:31!null n_name:32!null 1299 │ │ │ │ │ │ │ ├── key: (31) 1300 │ │ │ │ │ │ │ └── fd: (31)-->(32) 1301 │ │ │ │ │ │ └── filters 1302 │ │ │ │ │ │ └── n_name:32 = 'GERMANY' [outer=(32), constraints=(/32: [/'GERMANY' - /'GERMANY']; tight), fd=()-->(32)] 1303 │ │ │ │ │ └── filters (true) 1304 │ │ │ │ └── filters (true) 1305 │ │ │ └── filters (true) 1306 │ │ └── projections 1307 │ │ └── ps_supplycost:22 * ps_availqty:21::FLOAT8 [as=column35:35, outer=(21,22)] 1308 │ └── aggregations 1309 │ └── sum [as=sum:36, outer=(35)] 1310 │ └── column35:35 1311 └── projections 1312 └── sum:36 * 0.0001 [as="?column?":37, outer=(36)] 1313 1314 # -------------------------------------------------- 1315 # Q12 1316 # Shipping Modes and Order Priority 1317 # Determines whether selecting less expensive modes of shipping is negatively 1318 # affecting the critical-priority orders by causing more parts to be received by 1319 # customers after the committed date. 1320 # 1321 # Counts, by ship mode, for lineitems actually received by customers in a given 1322 # year, the number of lineitems belonging to orders for which the l_receiptdate 1323 # exceeds the l_commitdate for two different specified ship modes. Only 1324 # lineitems that were actually shipped before the l_commitdate are considered. 1325 # The late lineitems are partitioned into two groups, those with priority URGENT 1326 # or HIGH, and those with a priority other than URGENT or HIGH. 1327 # -------------------------------------------------- 1328 opt 1329 SELECT 1330 l_shipmode, 1331 sum(CASE 1332 WHEN o_orderpriority = '1-URGENT' 1333 OR o_orderpriority = '2-HIGH' 1334 THEN 1 1335 ELSE 0 1336 END) AS high_line_count, 1337 sum(CASE 1338 WHEN o_orderpriority <> '1-URGENT' 1339 AND o_orderpriority <> '2-HIGH' 1340 THEN 1 1341 ELSE 0 1342 END) AS low_line_count 1343 FROM 1344 orders, 1345 lineitem 1346 WHERE 1347 o_orderkey = l_orderkey 1348 AND l_shipmode IN ('MAIL', 'SHIP') 1349 AND l_commitdate < l_receiptdate 1350 AND l_shipdate < l_commitdate 1351 AND l_receiptdate >= DATE '1994-01-01' 1352 AND l_receiptdate < DATE '1994-01-01' + INTERVAL '1' YEAR 1353 GROUP BY 1354 l_shipmode 1355 ORDER BY 1356 l_shipmode; 1357 ---- 1358 sort 1359 ├── columns: l_shipmode:24!null high_line_count:27!null low_line_count:29!null 1360 ├── key: (24) 1361 ├── fd: (24)-->(27,29) 1362 ├── ordering: +24 1363 └── group-by 1364 ├── columns: l_shipmode:24!null sum:27!null sum:29!null 1365 ├── grouping columns: l_shipmode:24!null 1366 ├── key: (24) 1367 ├── fd: (24)-->(27,29) 1368 ├── project 1369 │ ├── columns: column26:26!null column28:28!null l_shipmode:24!null 1370 │ ├── inner-join (lookup orders) 1371 │ │ ├── columns: o_orderkey:1!null o_orderpriority:6!null l_orderkey:10!null l_shipdate:20!null l_commitdate:21!null l_receiptdate:22!null l_shipmode:24!null 1372 │ │ ├── key columns: [10] = [1] 1373 │ │ ├── lookup columns are key 1374 │ │ ├── fd: (1)-->(6), (1)==(10), (10)==(1) 1375 │ │ ├── select 1376 │ │ │ ├── columns: l_orderkey:10!null l_shipdate:20!null l_commitdate:21!null l_receiptdate:22!null l_shipmode:24!null 1377 │ │ │ ├── index-join lineitem 1378 │ │ │ │ ├── columns: l_orderkey:10!null l_shipdate:20!null l_commitdate:21!null l_receiptdate:22!null l_shipmode:24!null 1379 │ │ │ │ └── scan lineitem@l_rd 1380 │ │ │ │ ├── columns: l_orderkey:10!null l_linenumber:13!null l_receiptdate:22!null 1381 │ │ │ │ ├── constraint: /22/10/13: [/'1994-01-01' - /'1994-12-31'] 1382 │ │ │ │ ├── key: (10,13) 1383 │ │ │ │ └── fd: (10,13)-->(22) 1384 │ │ │ └── filters 1385 │ │ │ ├── l_shipmode:24 IN ('MAIL', 'SHIP') [outer=(24), constraints=(/24: [/'MAIL' - /'MAIL'] [/'SHIP' - /'SHIP']; tight)] 1386 │ │ │ ├── l_commitdate:21 < l_receiptdate:22 [outer=(21,22), constraints=(/21: (/NULL - ]; /22: (/NULL - ])] 1387 │ │ │ └── l_shipdate:20 < l_commitdate:21 [outer=(20,21), constraints=(/20: (/NULL - ]; /21: (/NULL - ])] 1388 │ │ └── filters (true) 1389 │ └── projections 1390 │ ├── CASE WHEN (o_orderpriority:6 = '1-URGENT') OR (o_orderpriority:6 = '2-HIGH') THEN 1 ELSE 0 END [as=column26:26, outer=(6)] 1391 │ └── CASE WHEN (o_orderpriority:6 != '1-URGENT') AND (o_orderpriority:6 != '2-HIGH') THEN 1 ELSE 0 END [as=column28:28, outer=(6)] 1392 └── aggregations 1393 ├── sum [as=sum:27, outer=(26)] 1394 │ └── column26:26 1395 └── sum [as=sum:29, outer=(28)] 1396 └── column28:28 1397 1398 # -------------------------------------------------- 1399 # Q13 1400 # Customer Distribution 1401 # Seeks relationships between customers and the size of their orders. 1402 # 1403 # Determines the distribution of customers by the number of orders they have 1404 # made, including customers who have no record of orders, past or present. It 1405 # counts and reports how many customers have no orders, how many have 1, 2, 3, 1406 # etc. A check is made to ensure that the orders counted do not fall into one of 1407 # several special categories of orders. Special categories are identified in the 1408 # order comment column by looking for a particular pattern. 1409 # -------------------------------------------------- 1410 opt 1411 SELECT 1412 c_count, count(*) AS custdist 1413 FROM ( 1414 SELECT 1415 c_custkey, 1416 count(o_orderkey) 1417 FROM 1418 customer LEFT OUTER JOIN orders ON 1419 c_custkey = o_custkey 1420 AND o_comment NOT LIKE '%special%requests%' 1421 GROUP BY 1422 c_custkey 1423 ) AS c_orders (c_custkey, c_count) 1424 GROUP BY 1425 c_count 1426 ORDER BY 1427 custdist DESC, 1428 c_count DESC; 1429 ---- 1430 sort 1431 ├── columns: c_count:18!null custdist:19!null 1432 ├── key: (18) 1433 ├── fd: (18)-->(19) 1434 ├── ordering: -19,-18 1435 └── group-by 1436 ├── columns: count:18!null count_rows:19!null 1437 ├── grouping columns: count:18!null 1438 ├── key: (18) 1439 ├── fd: (18)-->(19) 1440 ├── group-by 1441 │ ├── columns: c_custkey:1!null count:18!null 1442 │ ├── grouping columns: c_custkey:1!null 1443 │ ├── key: (1) 1444 │ ├── fd: (1)-->(18) 1445 │ ├── right-join (hash) 1446 │ │ ├── columns: c_custkey:1!null o_orderkey:9 o_custkey:10 o_comment:17 1447 │ │ ├── key: (1,9) 1448 │ │ ├── fd: (9)-->(10,17) 1449 │ │ ├── select 1450 │ │ │ ├── columns: o_orderkey:9!null o_custkey:10!null o_comment:17!null 1451 │ │ │ ├── key: (9) 1452 │ │ │ ├── fd: (9)-->(10,17) 1453 │ │ │ ├── scan orders 1454 │ │ │ │ ├── columns: o_orderkey:9!null o_custkey:10!null o_comment:17!null 1455 │ │ │ │ ├── key: (9) 1456 │ │ │ │ └── fd: (9)-->(10,17) 1457 │ │ │ └── filters 1458 │ │ │ └── o_comment:17 NOT LIKE '%special%requests%' [outer=(17), constraints=(/17: (/NULL - ])] 1459 │ │ ├── scan customer@c_nk 1460 │ │ │ ├── columns: c_custkey:1!null 1461 │ │ │ └── key: (1) 1462 │ │ └── filters 1463 │ │ └── c_custkey:1 = o_custkey:10 [outer=(1,10), constraints=(/1: (/NULL - ]; /10: (/NULL - ]), fd=(1)==(10), (10)==(1)] 1464 │ └── aggregations 1465 │ └── count [as=count:18, outer=(9)] 1466 │ └── o_orderkey:9 1467 └── aggregations 1468 └── count-rows [as=count_rows:19] 1469 1470 # -------------------------------------------------- 1471 # Q14 1472 # Promotion Effect 1473 # Monitors the market response to a promotion such as TV advertisements or a 1474 # special campaign. 1475 # 1476 # Determines what percentage of the revenue in a given year and month was 1477 # derived from promotional parts. The query considers only parts actually 1478 # shipped in that month and gives the percentage. Revenue is defined as 1479 # (l_extendedprice * (1-l_discount)). 1480 # -------------------------------------------------- 1481 opt 1482 SELECT 1483 100.00 * sum(CASE 1484 WHEN p_type LIKE 'PROMO%' 1485 THEN l_extendedprice * (1 - l_discount) 1486 ELSE 0 1487 END) / sum(l_extendedprice * (1 - l_discount)) AS promo_revenue 1488 FROM 1489 lineitem, 1490 part 1491 WHERE 1492 l_partkey = p_partkey 1493 AND l_shipdate >= DATE '1995-09-01' 1494 AND l_shipdate < DATE '1995-09-01' + INTERVAL '1' MONTH; 1495 ---- 1496 project 1497 ├── columns: promo_revenue:30 1498 ├── cardinality: [1 - 1] 1499 ├── immutable, side-effects 1500 ├── key: () 1501 ├── fd: ()-->(30) 1502 ├── scalar-group-by 1503 │ ├── columns: sum:27 sum:29 1504 │ ├── cardinality: [1 - 1] 1505 │ ├── key: () 1506 │ ├── fd: ()-->(27,29) 1507 │ ├── project 1508 │ │ ├── columns: column26:26!null column28:28!null 1509 │ │ ├── inner-join (hash) 1510 │ │ │ ├── columns: l_partkey:2!null l_extendedprice:6!null l_discount:7!null l_shipdate:11!null p_partkey:17!null p_type:21!null 1511 │ │ │ ├── fd: (17)-->(21), (2)==(17), (17)==(2) 1512 │ │ │ ├── scan part 1513 │ │ │ │ ├── columns: p_partkey:17!null p_type:21!null 1514 │ │ │ │ ├── key: (17) 1515 │ │ │ │ └── fd: (17)-->(21) 1516 │ │ │ ├── index-join lineitem 1517 │ │ │ │ ├── columns: l_partkey:2!null l_extendedprice:6!null l_discount:7!null l_shipdate:11!null 1518 │ │ │ │ └── scan lineitem@l_sd 1519 │ │ │ │ ├── columns: l_orderkey:1!null l_linenumber:4!null l_shipdate:11!null 1520 │ │ │ │ ├── constraint: /11/1/4: [/'1995-09-01' - /'1995-09-30'] 1521 │ │ │ │ ├── key: (1,4) 1522 │ │ │ │ └── fd: (1,4)-->(11) 1523 │ │ │ └── filters 1524 │ │ │ └── l_partkey:2 = p_partkey:17 [outer=(2,17), constraints=(/2: (/NULL - ]; /17: (/NULL - ]), fd=(2)==(17), (17)==(2)] 1525 │ │ └── projections 1526 │ │ ├── CASE WHEN p_type:21 LIKE 'PROMO%' THEN l_extendedprice:6 * (1.0 - l_discount:7) ELSE 0.0 END [as=column26:26, outer=(6,7,21)] 1527 │ │ └── l_extendedprice:6 * (1.0 - l_discount:7) [as=column28:28, outer=(6,7)] 1528 │ └── aggregations 1529 │ ├── sum [as=sum:27, outer=(26)] 1530 │ │ └── column26:26 1531 │ └── sum [as=sum:29, outer=(28)] 1532 │ └── column28:28 1533 └── projections 1534 └── (sum:27 * 100.0) / sum:29 [as=promo_revenue:30, outer=(27,29), immutable, side-effects] 1535 1536 # -------------------------------------------------- 1537 # Q15 1538 # Top Supplier 1539 # Determines the top supplier so it can be rewarded, given more business, or 1540 # identified for special recognition. 1541 # 1542 # Finds the supplier who contributed the most to the overall revenue for parts 1543 # shipped during a given quarter of a given year. In case of a tie, the query 1544 # lists all suppliers whose contribution was equal to the maximum, presented in 1545 # supplier number order. 1546 # -------------------------------------------------- 1547 exec-ddl 1548 CREATE VIEW revenue0 (supplier_no, total_revenue) AS 1549 SELECT 1550 l_suppkey, 1551 sum(l_extendedprice * (1 - l_discount)) 1552 FROM 1553 lineitem 1554 WHERE 1555 l_shipdate >= DATE '1996-01-01' 1556 AND l_shipdate < DATE '1996-01-01' + INTERVAL '3' MONTH 1557 GROUP BY 1558 l_suppkey; 1559 ---- 1560 1561 opt 1562 SELECT 1563 s_suppkey, 1564 s_name, 1565 s_address, 1566 s_phone, 1567 total_revenue 1568 FROM 1569 supplier, 1570 revenue0 1571 WHERE 1572 s_suppkey = supplier_no 1573 AND total_revenue = ( 1574 SELECT 1575 max(total_revenue) 1576 FROM 1577 revenue0 1578 ) 1579 ORDER BY 1580 s_suppkey; 1581 ---- 1582 project 1583 ├── columns: s_suppkey:1!null s_name:2!null s_address:3!null s_phone:5!null total_revenue:25!null 1584 ├── key: (1) 1585 ├── fd: (1)-->(2,3,5,25) 1586 ├── ordering: +1 1587 └── inner-join (merge) 1588 ├── columns: s_suppkey:1!null s_name:2!null s_address:3!null s_phone:5!null l_suppkey:10!null sum:25!null 1589 ├── left ordering: +1 1590 ├── right ordering: +10 1591 ├── key: (10) 1592 ├── fd: (1)-->(2,3,5), (10)-->(25), (1)==(10), (10)==(1) 1593 ├── ordering: +(1|10) [actual: +1] 1594 ├── scan supplier 1595 │ ├── columns: s_suppkey:1!null s_name:2!null s_address:3!null s_phone:5!null 1596 │ ├── key: (1) 1597 │ ├── fd: (1)-->(2,3,5) 1598 │ └── ordering: +1 1599 ├── sort 1600 │ ├── columns: l_suppkey:10!null sum:25!null 1601 │ ├── key: (10) 1602 │ ├── fd: (10)-->(25) 1603 │ ├── ordering: +10 1604 │ └── select 1605 │ ├── columns: l_suppkey:10!null sum:25!null 1606 │ ├── key: (10) 1607 │ ├── fd: (10)-->(25) 1608 │ ├── group-by 1609 │ │ ├── columns: l_suppkey:10!null sum:25!null 1610 │ │ ├── grouping columns: l_suppkey:10!null 1611 │ │ ├── key: (10) 1612 │ │ ├── fd: (10)-->(25) 1613 │ │ ├── project 1614 │ │ │ ├── columns: column24:24!null l_suppkey:10!null 1615 │ │ │ ├── index-join lineitem 1616 │ │ │ │ ├── columns: l_suppkey:10!null l_extendedprice:13!null l_discount:14!null l_shipdate:18!null 1617 │ │ │ │ └── scan lineitem@l_sd 1618 │ │ │ │ ├── columns: l_orderkey:8!null l_linenumber:11!null l_shipdate:18!null 1619 │ │ │ │ ├── constraint: /18/8/11: [/'1996-01-01' - /'1996-03-31'] 1620 │ │ │ │ ├── key: (8,11) 1621 │ │ │ │ └── fd: (8,11)-->(18) 1622 │ │ │ └── projections 1623 │ │ │ └── l_extendedprice:13 * (1.0 - l_discount:14) [as=column24:24, outer=(13,14)] 1624 │ │ └── aggregations 1625 │ │ └── sum [as=sum:25, outer=(24)] 1626 │ │ └── column24:24 1627 │ └── filters 1628 │ └── eq [outer=(25), subquery, constraints=(/25: (/NULL - ])] 1629 │ ├── sum:25 1630 │ └── subquery 1631 │ └── scalar-group-by 1632 │ ├── columns: max:44 1633 │ ├── cardinality: [1 - 1] 1634 │ ├── key: () 1635 │ ├── fd: ()-->(44) 1636 │ ├── group-by 1637 │ │ ├── columns: l_suppkey:28!null sum:43!null 1638 │ │ ├── grouping columns: l_suppkey:28!null 1639 │ │ ├── key: (28) 1640 │ │ ├── fd: (28)-->(43) 1641 │ │ ├── project 1642 │ │ │ ├── columns: column42:42!null l_suppkey:28!null 1643 │ │ │ ├── index-join lineitem 1644 │ │ │ │ ├── columns: l_suppkey:28!null l_extendedprice:31!null l_discount:32!null l_shipdate:36!null 1645 │ │ │ │ └── scan lineitem@l_sd 1646 │ │ │ │ ├── columns: l_orderkey:26!null l_linenumber:29!null l_shipdate:36!null 1647 │ │ │ │ ├── constraint: /36/26/29: [/'1996-01-01' - /'1996-03-31'] 1648 │ │ │ │ ├── key: (26,29) 1649 │ │ │ │ └── fd: (26,29)-->(36) 1650 │ │ │ └── projections 1651 │ │ │ └── l_extendedprice:31 * (1.0 - l_discount:32) [as=column42:42, outer=(31,32)] 1652 │ │ └── aggregations 1653 │ │ └── sum [as=sum:43, outer=(42)] 1654 │ │ └── column42:42 1655 │ └── aggregations 1656 │ └── max [as=max:44, outer=(43)] 1657 │ └── sum:43 1658 └── filters (true) 1659 1660 # -------------------------------------------------- 1661 # Q16 1662 # Parts/Supplier Relationship 1663 # Finds out how many suppliers can supply parts with given attributes. It might 1664 # be used, for example, to determine whether there is a sufficient number of 1665 # suppliers for heavily ordered parts. 1666 # 1667 # Counts the number of suppliers who can supply parts that satisfy a particular 1668 # customer's requirements. The customer is interested in parts of eight 1669 # different sizes as long as they are not of a given type, not of a given brand, 1670 # and not from a supplier who has had complaints registered at the Better 1671 # Business Bureau. Results must be presented in descending count and ascending 1672 # brand, type, and size. 1673 # -------------------------------------------------- 1674 opt 1675 SELECT 1676 p_brand, 1677 p_type, 1678 p_size, 1679 count(DISTINCT ps_suppkey) AS supplier_cnt 1680 FROM 1681 partsupp, 1682 part 1683 WHERE 1684 p_partkey = ps_partkey 1685 AND p_brand <> 'Brand#45' 1686 AND p_type NOT LIKE 'MEDIUM POLISHED %' 1687 AND p_size IN (49, 14, 23, 45, 19, 3, 36, 9) 1688 AND ps_suppkey NOT IN ( 1689 SELECT 1690 s_suppkey 1691 FROM 1692 supplier 1693 WHERE 1694 s_comment LIKE '%Customer%Complaints%' 1695 ) 1696 GROUP BY 1697 p_brand, 1698 p_type, 1699 p_size 1700 ORDER BY 1701 supplier_cnt DESC, 1702 p_brand, 1703 p_type, 1704 p_size; 1705 ---- 1706 sort 1707 ├── columns: p_brand:9!null p_type:10!null p_size:11!null supplier_cnt:22!null 1708 ├── key: (9-11) 1709 ├── fd: (9-11)-->(22) 1710 ├── ordering: -22,+9,+10,+11 1711 └── group-by 1712 ├── columns: p_brand:9!null p_type:10!null p_size:11!null count:22!null 1713 ├── grouping columns: p_brand:9!null p_type:10!null p_size:11!null 1714 ├── key: (9-11) 1715 ├── fd: (9-11)-->(22) 1716 ├── inner-join (hash) 1717 │ ├── columns: ps_partkey:1!null ps_suppkey:2!null p_partkey:6!null p_brand:9!null p_type:10!null p_size:11!null 1718 │ ├── key: (2,6) 1719 │ ├── fd: (6)-->(9-11), (1)==(6), (6)==(1) 1720 │ ├── anti-join (merge) 1721 │ │ ├── columns: ps_partkey:1!null ps_suppkey:2!null 1722 │ │ ├── left ordering: +2 1723 │ │ ├── right ordering: +15 1724 │ │ ├── key: (1,2) 1725 │ │ ├── scan partsupp@ps_sk 1726 │ │ │ ├── columns: ps_partkey:1!null ps_suppkey:2!null 1727 │ │ │ ├── key: (1,2) 1728 │ │ │ └── ordering: +2 1729 │ │ ├── select 1730 │ │ │ ├── columns: s_suppkey:15!null s_comment:21!null 1731 │ │ │ ├── key: (15) 1732 │ │ │ ├── fd: (15)-->(21) 1733 │ │ │ ├── ordering: +15 1734 │ │ │ ├── scan supplier 1735 │ │ │ │ ├── columns: s_suppkey:15!null s_comment:21!null 1736 │ │ │ │ ├── key: (15) 1737 │ │ │ │ ├── fd: (15)-->(21) 1738 │ │ │ │ └── ordering: +15 1739 │ │ │ └── filters 1740 │ │ │ └── s_comment:21 LIKE '%Customer%Complaints%' [outer=(21), constraints=(/21: (/NULL - ])] 1741 │ │ └── filters (true) 1742 │ ├── select 1743 │ │ ├── columns: p_partkey:6!null p_brand:9!null p_type:10!null p_size:11!null 1744 │ │ ├── key: (6) 1745 │ │ ├── fd: (6)-->(9-11) 1746 │ │ ├── scan part 1747 │ │ │ ├── columns: p_partkey:6!null p_brand:9!null p_type:10!null p_size:11!null 1748 │ │ │ ├── key: (6) 1749 │ │ │ └── fd: (6)-->(9-11) 1750 │ │ └── filters 1751 │ │ ├── p_brand:9 != 'Brand#45' [outer=(9), constraints=(/9: (/NULL - /'Brand#45') [/e'Brand#45\x00' - ]; tight)] 1752 │ │ ├── p_type:10 NOT LIKE 'MEDIUM POLISHED %' [outer=(10), constraints=(/10: (/NULL - ])] 1753 │ │ └── p_size:11 IN (3, 9, 14, 19, 23, 36, 45, 49) [outer=(11), constraints=(/11: [/3 - /3] [/9 - /9] [/14 - /14] [/19 - /19] [/23 - /23] [/36 - /36] [/45 - /45] [/49 - /49]; tight)] 1754 │ └── filters 1755 │ └── p_partkey:6 = ps_partkey:1 [outer=(1,6), constraints=(/1: (/NULL - ]; /6: (/NULL - ]), fd=(1)==(6), (6)==(1)] 1756 └── aggregations 1757 └── agg-distinct [as=count:22, outer=(2)] 1758 └── count 1759 └── ps_suppkey:2 1760 1761 # -------------------------------------------------- 1762 # Q17 1763 # Small-Quantity-Order Revenue 1764 # Determines how much average yearly revenue would be lost if orders were no 1765 # longer filled for small quantities of certain parts. This may reduce overhead 1766 # expenses by concentrating sales on larger shipments. 1767 # 1768 # Considers parts of a given brand and with a given container type and 1769 # determines the average lineitem quantity of such parts ordered for all orders 1770 # (past and pending) in the 7-year database. What would be the average yearly 1771 # gross (undiscounted) loss in revenue if orders for these parts with a quantity 1772 # of less than 20% of this average were no longer taken? 1773 # -------------------------------------------------- 1774 opt 1775 SELECT 1776 sum(l_extendedprice) / 7.0 AS avg_yearly 1777 FROM 1778 lineitem, 1779 part 1780 WHERE 1781 p_partkey = l_partkey 1782 AND p_brand = 'Brand#23' 1783 AND p_container = 'MED BOX' 1784 AND l_quantity < ( 1785 SELECT 1786 0.2 * avg(l_quantity) 1787 FROM 1788 lineitem 1789 WHERE 1790 l_partkey = p_partkey 1791 ); 1792 ---- 1793 project 1794 ├── columns: avg_yearly:45 1795 ├── cardinality: [1 - 1] 1796 ├── key: () 1797 ├── fd: ()-->(45) 1798 ├── scalar-group-by 1799 │ ├── columns: sum:44 1800 │ ├── cardinality: [1 - 1] 1801 │ ├── key: () 1802 │ ├── fd: ()-->(44) 1803 │ ├── inner-join (lookup lineitem) 1804 │ │ ├── columns: l_partkey:2!null l_quantity:5!null l_extendedprice:6!null p_partkey:17!null "?column?":43!null 1805 │ │ ├── key columns: [1 4] = [1 4] 1806 │ │ ├── lookup columns are key 1807 │ │ ├── fd: (17)-->(43), (2)==(17), (17)==(2) 1808 │ │ ├── inner-join (lookup lineitem@l_pk) 1809 │ │ │ ├── columns: l_orderkey:1!null l_partkey:2!null l_linenumber:4!null p_partkey:17!null "?column?":43 1810 │ │ │ ├── key columns: [17] = [2] 1811 │ │ │ ├── key: (1,4) 1812 │ │ │ ├── fd: (17)-->(43), (1,4)-->(2), (2)==(17), (17)==(2) 1813 │ │ │ ├── project 1814 │ │ │ │ ├── columns: "?column?":43 p_partkey:17!null 1815 │ │ │ │ ├── key: (17) 1816 │ │ │ │ ├── fd: (17)-->(43) 1817 │ │ │ │ ├── group-by 1818 │ │ │ │ │ ├── columns: p_partkey:17!null avg:42 1819 │ │ │ │ │ ├── grouping columns: p_partkey:17!null 1820 │ │ │ │ │ ├── internal-ordering: +17 opt(20,23) 1821 │ │ │ │ │ ├── key: (17) 1822 │ │ │ │ │ ├── fd: (17)-->(42) 1823 │ │ │ │ │ ├── left-join (lookup lineitem) 1824 │ │ │ │ │ │ ├── columns: p_partkey:17!null p_brand:20!null p_container:23!null l_partkey:27 l_quantity:30 1825 │ │ │ │ │ │ ├── key columns: [26 29] = [26 29] 1826 │ │ │ │ │ │ ├── lookup columns are key 1827 │ │ │ │ │ │ ├── fd: ()-->(20,23) 1828 │ │ │ │ │ │ ├── ordering: +17 opt(20,23) [actual: +17] 1829 │ │ │ │ │ │ ├── left-join (lookup lineitem@l_pk) 1830 │ │ │ │ │ │ │ ├── columns: p_partkey:17!null p_brand:20!null p_container:23!null l_orderkey:26 l_partkey:27 l_linenumber:29 1831 │ │ │ │ │ │ │ ├── key columns: [17] = [27] 1832 │ │ │ │ │ │ │ ├── key: (17,26,29) 1833 │ │ │ │ │ │ │ ├── fd: ()-->(20,23), (26,29)-->(27) 1834 │ │ │ │ │ │ │ ├── ordering: +17 opt(20,23) [actual: +17] 1835 │ │ │ │ │ │ │ ├── select 1836 │ │ │ │ │ │ │ │ ├── columns: p_partkey:17!null p_brand:20!null p_container:23!null 1837 │ │ │ │ │ │ │ │ ├── key: (17) 1838 │ │ │ │ │ │ │ │ ├── fd: ()-->(20,23) 1839 │ │ │ │ │ │ │ │ ├── ordering: +17 opt(20,23) [actual: +17] 1840 │ │ │ │ │ │ │ │ ├── scan part 1841 │ │ │ │ │ │ │ │ │ ├── columns: p_partkey:17!null p_brand:20!null p_container:23!null 1842 │ │ │ │ │ │ │ │ │ ├── key: (17) 1843 │ │ │ │ │ │ │ │ │ ├── fd: (17)-->(20,23) 1844 │ │ │ │ │ │ │ │ │ └── ordering: +17 opt(20,23) [actual: +17] 1845 │ │ │ │ │ │ │ │ └── filters 1846 │ │ │ │ │ │ │ │ ├── p_brand:20 = 'Brand#23' [outer=(20), constraints=(/20: [/'Brand#23' - /'Brand#23']; tight), fd=()-->(20)] 1847 │ │ │ │ │ │ │ │ └── p_container:23 = 'MED BOX' [outer=(23), constraints=(/23: [/'MED BOX' - /'MED BOX']; tight), fd=()-->(23)] 1848 │ │ │ │ │ │ │ └── filters (true) 1849 │ │ │ │ │ │ └── filters (true) 1850 │ │ │ │ │ └── aggregations 1851 │ │ │ │ │ └── avg [as=avg:42, outer=(30)] 1852 │ │ │ │ │ └── l_quantity:30 1853 │ │ │ │ └── projections 1854 │ │ │ │ └── avg:42 * 0.2 [as="?column?":43, outer=(42)] 1855 │ │ │ └── filters (true) 1856 │ │ └── filters 1857 │ │ └── l_quantity:5 < "?column?":43 [outer=(5,43), constraints=(/5: (/NULL - ]; /43: (/NULL - ])] 1858 │ └── aggregations 1859 │ └── sum [as=sum:44, outer=(6)] 1860 │ └── l_extendedprice:6 1861 └── projections 1862 └── sum:44 / 7.0 [as=avg_yearly:45, outer=(44)] 1863 1864 # -------------------------------------------------- 1865 # Q18 1866 # Large Volume Customer 1867 # Ranks customers based on their having placed a large quantity order. Large 1868 # quantity orders are defined as those orders whose total quantity is above a 1869 # certain level. 1870 # 1871 # Finds a list of the top 100 customers who have ever placed large quantity 1872 # orders. The query lists the customer name, customer key, the order key, date 1873 # and total price and the quantity for the order. 1874 # -------------------------------------------------- 1875 opt 1876 SELECT 1877 c_name, 1878 c_custkey, 1879 o_orderkey, 1880 o_orderdate, 1881 o_totalprice, 1882 sum(l_quantity) 1883 FROM 1884 customer, 1885 orders, 1886 lineitem 1887 WHERE 1888 o_orderkey IN ( 1889 SELECT 1890 l_orderkey 1891 FROM 1892 lineitem 1893 GROUP BY 1894 l_orderkey HAVING 1895 sum(l_quantity) > 300 1896 ) 1897 AND c_custkey = o_custkey 1898 AND o_orderkey = l_orderkey 1899 GROUP BY 1900 c_name, 1901 c_custkey, 1902 o_orderkey, 1903 o_orderdate, 1904 o_totalprice 1905 ORDER BY 1906 o_totalprice DESC, 1907 o_orderdate 1908 LIMIT 100; 1909 ---- 1910 limit 1911 ├── columns: c_name:2!null c_custkey:1!null o_orderkey:9!null o_orderdate:13!null o_totalprice:12!null sum:51!null 1912 ├── internal-ordering: -12,+13 1913 ├── cardinality: [0 - 100] 1914 ├── key: (9) 1915 ├── fd: (1)-->(2), (9)-->(1,2,12,13,51) 1916 ├── ordering: -12,+13 1917 ├── sort 1918 │ ├── columns: c_custkey:1!null c_name:2!null o_orderkey:9!null o_totalprice:12!null o_orderdate:13!null sum:51!null 1919 │ ├── key: (9) 1920 │ ├── fd: (1)-->(2), (9)-->(1,2,12,13,51) 1921 │ ├── ordering: -12,+13 1922 │ ├── limit hint: 100.00 1923 │ └── group-by 1924 │ ├── columns: c_custkey:1!null c_name:2!null o_orderkey:9!null o_totalprice:12!null o_orderdate:13!null sum:51!null 1925 │ ├── grouping columns: o_orderkey:9!null 1926 │ ├── key: (9) 1927 │ ├── fd: (1)-->(2), (9)-->(1,2,12,13,51) 1928 │ ├── inner-join (hash) 1929 │ │ ├── columns: c_custkey:1!null c_name:2!null o_orderkey:9!null o_custkey:10!null o_totalprice:12!null o_orderdate:13!null l_orderkey:18!null l_quantity:22!null 1930 │ │ ├── fd: (1)-->(2), (9)-->(10,12,13), (9)==(18), (18)==(9), (1)==(10), (10)==(1) 1931 │ │ ├── scan lineitem 1932 │ │ │ └── columns: l_orderkey:18!null l_quantity:22!null 1933 │ │ ├── inner-join (hash) 1934 │ │ │ ├── columns: c_custkey:1!null c_name:2!null o_orderkey:9!null o_custkey:10!null o_totalprice:12!null o_orderdate:13!null 1935 │ │ │ ├── key: (9) 1936 │ │ │ ├── fd: (9)-->(10,12,13), (1)-->(2), (1)==(10), (10)==(1) 1937 │ │ │ ├── semi-join (merge) 1938 │ │ │ │ ├── columns: o_orderkey:9!null o_custkey:10!null o_totalprice:12!null o_orderdate:13!null 1939 │ │ │ │ ├── left ordering: +9 1940 │ │ │ │ ├── right ordering: +34 1941 │ │ │ │ ├── key: (9) 1942 │ │ │ │ ├── fd: (9)-->(10,12,13) 1943 │ │ │ │ ├── scan orders 1944 │ │ │ │ │ ├── columns: o_orderkey:9!null o_custkey:10!null o_totalprice:12!null o_orderdate:13!null 1945 │ │ │ │ │ ├── key: (9) 1946 │ │ │ │ │ ├── fd: (9)-->(10,12,13) 1947 │ │ │ │ │ └── ordering: +9 1948 │ │ │ │ ├── select 1949 │ │ │ │ │ ├── columns: l_orderkey:34!null sum:50!null 1950 │ │ │ │ │ ├── key: (34) 1951 │ │ │ │ │ ├── fd: (34)-->(50) 1952 │ │ │ │ │ ├── ordering: +34 1953 │ │ │ │ │ ├── group-by 1954 │ │ │ │ │ │ ├── columns: l_orderkey:34!null sum:50!null 1955 │ │ │ │ │ │ ├── grouping columns: l_orderkey:34!null 1956 │ │ │ │ │ │ ├── key: (34) 1957 │ │ │ │ │ │ ├── fd: (34)-->(50) 1958 │ │ │ │ │ │ ├── ordering: +34 1959 │ │ │ │ │ │ ├── scan lineitem 1960 │ │ │ │ │ │ │ ├── columns: l_orderkey:34!null l_quantity:38!null 1961 │ │ │ │ │ │ │ └── ordering: +34 1962 │ │ │ │ │ │ └── aggregations 1963 │ │ │ │ │ │ └── sum [as=sum:50, outer=(38)] 1964 │ │ │ │ │ │ └── l_quantity:38 1965 │ │ │ │ │ └── filters 1966 │ │ │ │ │ └── sum:50 > 300.0 [outer=(50), constraints=(/50: [/300.00000000000006 - ]; tight)] 1967 │ │ │ │ └── filters (true) 1968 │ │ │ ├── scan customer 1969 │ │ │ │ ├── columns: c_custkey:1!null c_name:2!null 1970 │ │ │ │ ├── key: (1) 1971 │ │ │ │ └── fd: (1)-->(2) 1972 │ │ │ └── filters 1973 │ │ │ └── c_custkey:1 = o_custkey:10 [outer=(1,10), constraints=(/1: (/NULL - ]; /10: (/NULL - ]), fd=(1)==(10), (10)==(1)] 1974 │ │ └── filters 1975 │ │ └── o_orderkey:9 = l_orderkey:18 [outer=(9,18), constraints=(/9: (/NULL - ]; /18: (/NULL - ]), fd=(9)==(18), (18)==(9)] 1976 │ └── aggregations 1977 │ ├── sum [as=sum:51, outer=(22)] 1978 │ │ └── l_quantity:22 1979 │ ├── const-agg [as=c_custkey:1, outer=(1)] 1980 │ │ └── c_custkey:1 1981 │ ├── const-agg [as=c_name:2, outer=(2)] 1982 │ │ └── c_name:2 1983 │ ├── const-agg [as=o_totalprice:12, outer=(12)] 1984 │ │ └── o_totalprice:12 1985 │ └── const-agg [as=o_orderdate:13, outer=(13)] 1986 │ └── o_orderdate:13 1987 └── 100 1988 1989 # -------------------------------------------------- 1990 # Q19 1991 # Discounted Revenue 1992 # Reports the gross discounted revenue attributed to the sale of selected parts 1993 # handled in a particular manner. This query is an example of code such as might 1994 # be produced programmatically by a data mining tool. 1995 # 1996 # The Discounted Revenue query finds the gross discounted revenue for all orders 1997 # for three different types of parts that were shipped by air and delivered in 1998 # person. Parts are selected based on the combination of specific brands, a list 1999 # of containers, and a range of sizes. 2000 # -------------------------------------------------- 2001 opt 2002 SELECT 2003 sum(l_extendedprice* (1 - l_discount)) AS revenue 2004 FROM 2005 lineitem, 2006 part 2007 WHERE 2008 ( 2009 p_partkey = l_partkey 2010 AND p_brand = 'Brand#12' 2011 AND p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') 2012 AND l_quantity >= 1 AND l_quantity <= 1 + 10 2013 AND p_size BETWEEN 1 AND 5 2014 AND l_shipmode IN ('AIR', 'AIR REG') 2015 AND l_shipinstruct = 'DELIVER IN PERSON' 2016 ) 2017 OR 2018 ( 2019 p_partkey = l_partkey 2020 AND p_brand = 'Brand#23' 2021 AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') 2022 AND l_quantity >= 10 AND l_quantity <= 10 + 10 2023 AND p_size BETWEEN 1 AND 10 2024 AND l_shipmode IN ('AIR', 'AIR REG') 2025 AND l_shipinstruct = 'DELIVER IN PERSON' 2026 ) 2027 OR 2028 ( 2029 p_partkey = l_partkey 2030 AND p_brand = 'Brand#34' 2031 AND p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') 2032 AND l_quantity >= 20 AND l_quantity <= 20 + 10 2033 AND p_size BETWEEN 1 AND 15 2034 AND l_shipmode IN ('AIR', 'AIR REG') 2035 AND l_shipinstruct = 'DELIVER IN PERSON' 2036 ); 2037 ---- 2038 scalar-group-by 2039 ├── columns: revenue:27 2040 ├── cardinality: [1 - 1] 2041 ├── key: () 2042 ├── fd: ()-->(27) 2043 ├── project 2044 │ ├── columns: column26:26!null 2045 │ ├── inner-join (hash) 2046 │ │ ├── columns: l_partkey:2!null l_quantity:5!null l_extendedprice:6!null l_discount:7!null l_shipinstruct:14!null l_shipmode:15!null p_partkey:17!null p_brand:20!null p_size:22!null p_container:23!null 2047 │ │ ├── fd: ()-->(14), (17)-->(20,22,23), (2)==(17), (17)==(2) 2048 │ │ ├── select 2049 │ │ │ ├── columns: l_partkey:2!null l_quantity:5!null l_extendedprice:6!null l_discount:7!null l_shipinstruct:14!null l_shipmode:15!null 2050 │ │ │ ├── fd: ()-->(14) 2051 │ │ │ ├── scan lineitem 2052 │ │ │ │ └── columns: l_partkey:2!null l_quantity:5!null l_extendedprice:6!null l_discount:7!null l_shipinstruct:14!null l_shipmode:15!null 2053 │ │ │ └── filters 2054 │ │ │ ├── l_shipmode:15 IN ('AIR', 'AIR REG') [outer=(15), constraints=(/15: [/'AIR' - /'AIR'] [/'AIR REG' - /'AIR REG']; tight)] 2055 │ │ │ └── l_shipinstruct:14 = 'DELIVER IN PERSON' [outer=(14), constraints=(/14: [/'DELIVER IN PERSON' - /'DELIVER IN PERSON']; tight), fd=()-->(14)] 2056 │ │ ├── select 2057 │ │ │ ├── columns: p_partkey:17!null p_brand:20!null p_size:22!null p_container:23!null 2058 │ │ │ ├── key: (17) 2059 │ │ │ ├── fd: (17)-->(20,22,23) 2060 │ │ │ ├── scan part 2061 │ │ │ │ ├── columns: p_partkey:17!null p_brand:20!null p_size:22!null p_container:23!null 2062 │ │ │ │ ├── key: (17) 2063 │ │ │ │ └── fd: (17)-->(20,22,23) 2064 │ │ │ └── filters 2065 │ │ │ └── p_size:22 >= 1 [outer=(22), constraints=(/22: [/1 - ]; tight)] 2066 │ │ └── filters 2067 │ │ ├── p_partkey:17 = l_partkey:2 [outer=(2,17), constraints=(/2: (/NULL - ]; /17: (/NULL - ]), fd=(2)==(17), (17)==(2)] 2068 │ │ └── ((((((p_brand:20 = 'Brand#12') AND (p_container:23 IN ('SM BOX', 'SM CASE', 'SM PACK', 'SM PKG'))) AND (l_quantity:5 >= 1.0)) AND (l_quantity:5 <= 11.0)) AND (p_size:22 <= 5)) OR (((((p_brand:20 = 'Brand#23') AND (p_container:23 IN ('MED BAG', 'MED BOX', 'MED PACK', 'MED PKG'))) AND (l_quantity:5 >= 10.0)) AND (l_quantity:5 <= 20.0)) AND (p_size:22 <= 10))) OR (((((p_brand:20 = 'Brand#34') AND (p_container:23 IN ('LG BOX', 'LG CASE', 'LG PACK', 'LG PKG'))) AND (l_quantity:5 >= 20.0)) AND (l_quantity:5 <= 30.0)) AND (p_size:22 <= 15)) [outer=(5,20,22,23), constraints=(/5: [/1.0 - /30.0]; /20: [/'Brand#12' - /'Brand#12'] [/'Brand#23' - /'Brand#23'] [/'Brand#34' - /'Brand#34']; /22: (/NULL - /15]; /23: [/'LG BOX' - /'LG BOX'] [/'LG CASE' - /'LG CASE'] [/'LG PACK' - /'LG PACK'] [/'LG PKG' - /'LG PKG'] [/'MED BAG' - /'MED BAG'] [/'MED BOX' - /'MED BOX'] [/'MED PACK' - /'MED PACK'] [/'MED PKG' - /'MED PKG'] [/'SM BOX' - /'SM BOX'] [/'SM CASE' - /'SM CASE'] [/'SM PACK' - /'SM PACK'] [/'SM PKG' - /'SM PKG'])] 2069 │ └── projections 2070 │ └── l_extendedprice:6 * (1.0 - l_discount:7) [as=column26:26, outer=(6,7)] 2071 └── aggregations 2072 └── sum [as=sum:27, outer=(26)] 2073 └── column26:26 2074 2075 # -------------------------------------------------- 2076 # Q20 2077 # Potential Part Promotion 2078 # Identifies suppliers in a particular nation having selected parts that may be 2079 # candidates for a promotional offer. 2080 # 2081 # Identifies suppliers who have an excess of a given part available; an excess 2082 # defined to be more than 50% of the parts like the given part that the supplier 2083 # shipped in a given year for a given nation. Only parts whose names share a 2084 # certain naming convention are considered. 2085 # 2086 # TODO: 2087 # 1. Push 'forest%' prefix filter down into Scan 2088 # -------------------------------------------------- 2089 opt 2090 SELECT 2091 s_name, 2092 s_address 2093 FROM 2094 supplier, 2095 nation 2096 WHERE 2097 s_suppkey IN ( 2098 SELECT 2099 ps_suppkey 2100 FROM 2101 partsupp 2102 WHERE 2103 ps_partkey IN ( 2104 SELECT 2105 p_partkey 2106 FROM 2107 part 2108 WHERE 2109 p_name LIKE 'forest%' 2110 ) 2111 AND ps_availqty > ( 2112 SELECT 2113 0.5 * sum(l_quantity) 2114 FROM 2115 lineitem 2116 WHERE 2117 l_partkey = ps_partkey 2118 AND l_suppkey = ps_suppkey 2119 AND l_shipdate >= DATE '1994-01-01' 2120 AND l_shipdate < DATE '1994-01-01' + INTERVAL '1' YEAR 2121 ) 2122 ) 2123 AND s_nationkey = n_nationkey 2124 AND n_name = 'CANADA' 2125 ORDER BY 2126 s_name; 2127 ---- 2128 sort 2129 ├── columns: s_name:2!null s_address:3!null 2130 ├── ordering: +2 2131 └── project 2132 ├── columns: s_name:2!null s_address:3!null 2133 └── inner-join (hash) 2134 ├── columns: s_suppkey:1!null s_name:2!null s_address:3!null s_nationkey:4!null n_nationkey:8!null n_name:9!null 2135 ├── key: (1) 2136 ├── fd: ()-->(9), (1)-->(2-4), (4)==(8), (8)==(4) 2137 ├── semi-join (hash) 2138 │ ├── columns: s_suppkey:1!null s_name:2!null s_address:3!null s_nationkey:4!null 2139 │ ├── key: (1) 2140 │ ├── fd: (1)-->(2-4) 2141 │ ├── scan supplier 2142 │ │ ├── columns: s_suppkey:1!null s_name:2!null s_address:3!null s_nationkey:4!null 2143 │ │ ├── key: (1) 2144 │ │ └── fd: (1)-->(2-4) 2145 │ ├── project 2146 │ │ ├── columns: ps_partkey:12!null ps_suppkey:13!null 2147 │ │ ├── key: (12,13) 2148 │ │ └── project 2149 │ │ ├── columns: ps_partkey:12!null ps_suppkey:13!null p_partkey:17!null 2150 │ │ ├── key: (13,17) 2151 │ │ ├── fd: (12)==(17), (17)==(12) 2152 │ │ └── inner-join (hash) 2153 │ │ ├── columns: ps_partkey:12!null ps_suppkey:13!null ps_availqty:14!null p_partkey:17!null p_name:18!null sum:42 2154 │ │ ├── key: (13,17) 2155 │ │ ├── fd: (12,13)-->(14,42), (17)-->(18), (12)==(17), (17)==(12) 2156 │ │ ├── select 2157 │ │ │ ├── columns: ps_partkey:12!null ps_suppkey:13!null ps_availqty:14!null sum:42 2158 │ │ │ ├── key: (12,13) 2159 │ │ │ ├── fd: (12,13)-->(14,42) 2160 │ │ │ ├── group-by 2161 │ │ │ │ ├── columns: ps_partkey:12!null ps_suppkey:13!null ps_availqty:14!null sum:42 2162 │ │ │ │ ├── grouping columns: ps_partkey:12!null ps_suppkey:13!null 2163 │ │ │ │ ├── key: (12,13) 2164 │ │ │ │ ├── fd: (12,13)-->(14,42) 2165 │ │ │ │ ├── right-join (hash) 2166 │ │ │ │ │ ├── columns: ps_partkey:12!null ps_suppkey:13!null ps_availqty:14!null l_partkey:27 l_suppkey:28 l_quantity:30 l_shipdate:36 2167 │ │ │ │ │ ├── fd: (12,13)-->(14) 2168 │ │ │ │ │ ├── index-join lineitem 2169 │ │ │ │ │ │ ├── columns: l_partkey:27!null l_suppkey:28!null l_quantity:30!null l_shipdate:36!null 2170 │ │ │ │ │ │ └── scan lineitem@l_sd 2171 │ │ │ │ │ │ ├── columns: l_orderkey:26!null l_linenumber:29!null l_shipdate:36!null 2172 │ │ │ │ │ │ ├── constraint: /36/26/29: [/'1994-01-01' - /'1994-12-31'] 2173 │ │ │ │ │ │ ├── key: (26,29) 2174 │ │ │ │ │ │ └── fd: (26,29)-->(36) 2175 │ │ │ │ │ ├── scan partsupp 2176 │ │ │ │ │ │ ├── columns: ps_partkey:12!null ps_suppkey:13!null ps_availqty:14!null 2177 │ │ │ │ │ │ ├── key: (12,13) 2178 │ │ │ │ │ │ └── fd: (12,13)-->(14) 2179 │ │ │ │ │ └── filters 2180 │ │ │ │ │ ├── l_partkey:27 = ps_partkey:12 [outer=(12,27), constraints=(/12: (/NULL - ]; /27: (/NULL - ]), fd=(12)==(27), (27)==(12)] 2181 │ │ │ │ │ └── l_suppkey:28 = ps_suppkey:13 [outer=(13,28), constraints=(/13: (/NULL - ]; /28: (/NULL - ]), fd=(13)==(28), (28)==(13)] 2182 │ │ │ │ └── aggregations 2183 │ │ │ │ ├── sum [as=sum:42, outer=(30)] 2184 │ │ │ │ │ └── l_quantity:30 2185 │ │ │ │ └── const-agg [as=ps_availqty:14, outer=(14)] 2186 │ │ │ │ └── ps_availqty:14 2187 │ │ │ └── filters 2188 │ │ │ └── ps_availqty:14 > (sum:42 * 0.5) [outer=(14,42), constraints=(/14: (/NULL - ])] 2189 │ │ ├── select 2190 │ │ │ ├── columns: p_partkey:17!null p_name:18!null 2191 │ │ │ ├── key: (17) 2192 │ │ │ ├── fd: (17)-->(18) 2193 │ │ │ ├── scan part 2194 │ │ │ │ ├── columns: p_partkey:17!null p_name:18!null 2195 │ │ │ │ ├── key: (17) 2196 │ │ │ │ └── fd: (17)-->(18) 2197 │ │ │ └── filters 2198 │ │ │ └── p_name:18 LIKE 'forest%' [outer=(18), constraints=(/18: [/'forest' - /'foresu'); tight)] 2199 │ │ └── filters 2200 │ │ └── ps_partkey:12 = p_partkey:17 [outer=(12,17), constraints=(/12: (/NULL - ]; /17: (/NULL - ]), fd=(12)==(17), (17)==(12)] 2201 │ └── filters 2202 │ └── s_suppkey:1 = ps_suppkey:13 [outer=(1,13), constraints=(/1: (/NULL - ]; /13: (/NULL - ]), fd=(1)==(13), (13)==(1)] 2203 ├── select 2204 │ ├── columns: n_nationkey:8!null n_name:9!null 2205 │ ├── key: (8) 2206 │ ├── fd: ()-->(9) 2207 │ ├── scan nation 2208 │ │ ├── columns: n_nationkey:8!null n_name:9!null 2209 │ │ ├── key: (8) 2210 │ │ └── fd: (8)-->(9) 2211 │ └── filters 2212 │ └── n_name:9 = 'CANADA' [outer=(9), constraints=(/9: [/'CANADA' - /'CANADA']; tight), fd=()-->(9)] 2213 └── filters 2214 └── s_nationkey:4 = n_nationkey:8 [outer=(4,8), constraints=(/4: (/NULL - ]; /8: (/NULL - ]), fd=(4)==(8), (8)==(4)] 2215 2216 # -------------------------------------------------- 2217 # Q21 2218 # Suppliers Who Kept Orders Waiting Query 2219 # Identifies certain suppliers who were not able to ship required parts in a 2220 # timely manner. 2221 # 2222 # Identifies suppliers, for a given nation, whose product was part of a multi- 2223 # supplier order (with current status of 'F') where they were the only supplier 2224 # who failed to meet the committed delivery date. 2225 # -------------------------------------------------- 2226 opt 2227 SELECT 2228 s_name, 2229 count(*) AS numwait 2230 FROM 2231 supplier, 2232 lineitem l1, 2233 orders, 2234 nation 2235 WHERE 2236 s_suppkey = l1.l_suppkey 2237 AND o_orderkey = l1.l_orderkey 2238 AND o_orderstatus = 'F' 2239 AND l1.l_receiptDATE > l1.l_commitdate 2240 AND EXISTS ( 2241 SELECT 2242 * 2243 FROM 2244 lineitem l2 2245 WHERE 2246 l2.l_orderkey = l1.l_orderkey 2247 AND l2.l_suppkey <> l1.l_suppkey 2248 ) 2249 AND NOT EXISTS ( 2250 SELECT 2251 * 2252 FROM 2253 lineitem l3 2254 WHERE 2255 l3.l_orderkey = l1.l_orderkey 2256 AND l3.l_suppkey <> l1.l_suppkey 2257 AND l3.l_receiptDATE > l3.l_commitdate 2258 ) 2259 AND s_nationkey = n_nationkey 2260 AND n_name = 'SAUDI ARABIA' 2261 GROUP BY 2262 s_name 2263 ORDER BY 2264 numwait DESC, 2265 s_name 2266 LIMIT 100; 2267 ---- 2268 limit 2269 ├── columns: s_name:2!null numwait:69!null 2270 ├── internal-ordering: -69,+2 2271 ├── cardinality: [0 - 100] 2272 ├── key: (2) 2273 ├── fd: (2)-->(69) 2274 ├── ordering: -69,+2 2275 ├── sort 2276 │ ├── columns: s_name:2!null count_rows:69!null 2277 │ ├── key: (2) 2278 │ ├── fd: (2)-->(69) 2279 │ ├── ordering: -69,+2 2280 │ ├── limit hint: 100.00 2281 │ └── group-by 2282 │ ├── columns: s_name:2!null count_rows:69!null 2283 │ ├── grouping columns: s_name:2!null 2284 │ ├── key: (2) 2285 │ ├── fd: (2)-->(69) 2286 │ ├── inner-join (lookup orders) 2287 │ │ ├── columns: s_suppkey:1!null s_name:2!null s_nationkey:4!null l1.l_orderkey:8!null l1.l_suppkey:10!null l1.l_commitdate:19!null l1.l_receiptdate:20!null o_orderkey:24!null o_orderstatus:26!null n_nationkey:33!null n_name:34!null 2288 │ │ ├── key columns: [8] = [24] 2289 │ │ ├── lookup columns are key 2290 │ │ ├── fd: ()-->(26,34), (1)-->(2,4), (8)==(24), (24)==(8), (1)==(10), (10)==(1), (4)==(33), (33)==(4) 2291 │ │ ├── inner-join (hash) 2292 │ │ │ ├── columns: s_suppkey:1!null s_name:2!null s_nationkey:4!null l1.l_orderkey:8!null l1.l_suppkey:10!null l1.l_commitdate:19!null l1.l_receiptdate:20!null n_nationkey:33!null n_name:34!null 2293 │ │ │ ├── fd: ()-->(34), (1)-->(2,4), (1)==(10), (10)==(1), (4)==(33), (33)==(4) 2294 │ │ │ ├── semi-join (hash) 2295 │ │ │ │ ├── columns: l1.l_orderkey:8!null l1.l_suppkey:10!null l1.l_commitdate:19!null l1.l_receiptdate:20!null 2296 │ │ │ │ ├── anti-join (merge) 2297 │ │ │ │ │ ├── columns: l1.l_orderkey:8!null l1.l_suppkey:10!null l1.l_commitdate:19!null l1.l_receiptdate:20!null 2298 │ │ │ │ │ ├── left ordering: +8 2299 │ │ │ │ │ ├── right ordering: +53 2300 │ │ │ │ │ ├── select 2301 │ │ │ │ │ │ ├── columns: l1.l_orderkey:8!null l1.l_suppkey:10!null l1.l_commitdate:19!null l1.l_receiptdate:20!null 2302 │ │ │ │ │ │ ├── ordering: +8 2303 │ │ │ │ │ │ ├── scan l1 2304 │ │ │ │ │ │ │ ├── columns: l1.l_orderkey:8!null l1.l_suppkey:10!null l1.l_commitdate:19!null l1.l_receiptdate:20!null 2305 │ │ │ │ │ │ │ └── ordering: +8 2306 │ │ │ │ │ │ └── filters 2307 │ │ │ │ │ │ └── l1.l_receiptdate:20 > l1.l_commitdate:19 [outer=(19,20), constraints=(/19: (/NULL - ]; /20: (/NULL - ])] 2308 │ │ │ │ │ ├── select 2309 │ │ │ │ │ │ ├── columns: l3.l_orderkey:53!null l3.l_suppkey:55!null l3.l_commitdate:64!null l3.l_receiptdate:65!null 2310 │ │ │ │ │ │ ├── ordering: +53 2311 │ │ │ │ │ │ ├── scan l3 2312 │ │ │ │ │ │ │ ├── columns: l3.l_orderkey:53!null l3.l_suppkey:55!null l3.l_commitdate:64!null l3.l_receiptdate:65!null 2313 │ │ │ │ │ │ │ └── ordering: +53 2314 │ │ │ │ │ │ └── filters 2315 │ │ │ │ │ │ └── l3.l_receiptdate:65 > l3.l_commitdate:64 [outer=(64,65), constraints=(/64: (/NULL - ]; /65: (/NULL - ])] 2316 │ │ │ │ │ └── filters 2317 │ │ │ │ │ └── l3.l_suppkey:55 != l1.l_suppkey:10 [outer=(10,55), constraints=(/10: (/NULL - ]; /55: (/NULL - ])] 2318 │ │ │ │ ├── scan l2@l_sk 2319 │ │ │ │ │ └── columns: l2.l_orderkey:37!null l2.l_suppkey:39!null 2320 │ │ │ │ └── filters 2321 │ │ │ │ ├── l2.l_orderkey:37 = l1.l_orderkey:8 [outer=(8,37), constraints=(/8: (/NULL - ]; /37: (/NULL - ]), fd=(8)==(37), (37)==(8)] 2322 │ │ │ │ └── l2.l_suppkey:39 != l1.l_suppkey:10 [outer=(10,39), constraints=(/10: (/NULL - ]; /39: (/NULL - ])] 2323 │ │ │ ├── inner-join (lookup supplier) 2324 │ │ │ │ ├── columns: s_suppkey:1!null s_name:2!null s_nationkey:4!null n_nationkey:33!null n_name:34!null 2325 │ │ │ │ ├── key columns: [1] = [1] 2326 │ │ │ │ ├── lookup columns are key 2327 │ │ │ │ ├── key: (1) 2328 │ │ │ │ ├── fd: ()-->(34), (1)-->(2,4), (4)==(33), (33)==(4) 2329 │ │ │ │ ├── inner-join (lookup supplier@s_nk) 2330 │ │ │ │ │ ├── columns: s_suppkey:1!null s_nationkey:4!null n_nationkey:33!null n_name:34!null 2331 │ │ │ │ │ ├── key columns: [33] = [4] 2332 │ │ │ │ │ ├── key: (1) 2333 │ │ │ │ │ ├── fd: ()-->(34), (1)-->(4), (4)==(33), (33)==(4) 2334 │ │ │ │ │ ├── select 2335 │ │ │ │ │ │ ├── columns: n_nationkey:33!null n_name:34!null 2336 │ │ │ │ │ │ ├── key: (33) 2337 │ │ │ │ │ │ ├── fd: ()-->(34) 2338 │ │ │ │ │ │ ├── scan nation 2339 │ │ │ │ │ │ │ ├── columns: n_nationkey:33!null n_name:34!null 2340 │ │ │ │ │ │ │ ├── key: (33) 2341 │ │ │ │ │ │ │ └── fd: (33)-->(34) 2342 │ │ │ │ │ │ └── filters 2343 │ │ │ │ │ │ └── n_name:34 = 'SAUDI ARABIA' [outer=(34), constraints=(/34: [/'SAUDI ARABIA' - /'SAUDI ARABIA']; tight), fd=()-->(34)] 2344 │ │ │ │ │ └── filters (true) 2345 │ │ │ │ └── filters (true) 2346 │ │ │ └── filters 2347 │ │ │ └── s_suppkey:1 = l1.l_suppkey:10 [outer=(1,10), constraints=(/1: (/NULL - ]; /10: (/NULL - ]), fd=(1)==(10), (10)==(1)] 2348 │ │ └── filters 2349 │ │ └── o_orderstatus:26 = 'F' [outer=(26), constraints=(/26: [/'F' - /'F']; tight), fd=()-->(26)] 2350 │ └── aggregations 2351 │ └── count-rows [as=count_rows:69] 2352 └── 100 2353 2354 # -------------------------------------------------- 2355 # Q22 2356 # Global Sales Opportunity 2357 # Identifies geographies where there are customers who may be likely to make a 2358 # purchase. 2359 # 2360 # This query counts how many customers within a specific range of country codes 2361 # have not placed orders for 7 years but who have a greater than average 2362 # “positive” account balance. It also reflects the magnitude of that balance. 2363 # Country code is defined as the first two characters of c_phone. 2364 # -------------------------------------------------- 2365 opt 2366 SELECT 2367 cntrycode, 2368 count(*) AS numcust, 2369 sum(c_acctbal) AS totacctbal 2370 FROM ( 2371 SELECT 2372 substring(c_phone FROM 1 FOR 2) AS cntrycode, 2373 c_acctbal 2374 FROM 2375 customer 2376 WHERE 2377 substring(c_phone FROM 1 FOR 2) in 2378 ('13', '31', '23', '29', '30', '18', '17') 2379 AND c_acctbal > ( 2380 SELECT 2381 avg(c_acctbal) 2382 FROM 2383 customer 2384 WHERE 2385 c_acctbal > 0.00 2386 AND substring(c_phone FROM 1 FOR 2) in 2387 ('13', '31', '23', '29', '30', '18', '17') 2388 ) 2389 AND NOT EXISTS ( 2390 SELECT 2391 * 2392 FROM 2393 orders 2394 WHERE 2395 o_custkey = c_custkey 2396 ) 2397 ) AS custsale 2398 GROUP BY 2399 cntrycode 2400 ORDER BY 2401 cntrycode; 2402 ---- 2403 sort 2404 ├── columns: cntrycode:27 numcust:28!null totacctbal:29!null 2405 ├── immutable 2406 ├── key: (27) 2407 ├── fd: (27)-->(28,29) 2408 ├── ordering: +27 2409 └── group-by 2410 ├── columns: cntrycode:27 count_rows:28!null sum:29!null 2411 ├── grouping columns: cntrycode:27 2412 ├── immutable 2413 ├── key: (27) 2414 ├── fd: (27)-->(28,29) 2415 ├── project 2416 │ ├── columns: cntrycode:27 c_acctbal:6!null 2417 │ ├── immutable 2418 │ ├── anti-join (lookup orders@o_ck) 2419 │ │ ├── columns: c_custkey:1!null c_phone:5!null c_acctbal:6!null 2420 │ │ ├── key columns: [1] = [19] 2421 │ │ ├── immutable 2422 │ │ ├── key: (1) 2423 │ │ ├── fd: (1)-->(5,6) 2424 │ │ ├── select 2425 │ │ │ ├── columns: c_custkey:1!null c_phone:5!null c_acctbal:6!null 2426 │ │ │ ├── immutable 2427 │ │ │ ├── key: (1) 2428 │ │ │ ├── fd: (1)-->(5,6) 2429 │ │ │ ├── scan customer 2430 │ │ │ │ ├── columns: c_custkey:1!null c_phone:5!null c_acctbal:6!null 2431 │ │ │ │ ├── key: (1) 2432 │ │ │ │ └── fd: (1)-->(5,6) 2433 │ │ │ └── filters 2434 │ │ │ ├── substring(c_phone:5, 1, 2) IN ('13', '17', '18', '23', '29', '30', '31') [outer=(5), immutable] 2435 │ │ │ └── gt [outer=(6), immutable, subquery, constraints=(/6: (/NULL - ])] 2436 │ │ │ ├── c_acctbal:6 2437 │ │ │ └── subquery 2438 │ │ │ └── scalar-group-by 2439 │ │ │ ├── columns: avg:17 2440 │ │ │ ├── cardinality: [1 - 1] 2441 │ │ │ ├── immutable 2442 │ │ │ ├── key: () 2443 │ │ │ ├── fd: ()-->(17) 2444 │ │ │ ├── select 2445 │ │ │ │ ├── columns: c_phone:13!null c_acctbal:14!null 2446 │ │ │ │ ├── immutable 2447 │ │ │ │ ├── scan customer 2448 │ │ │ │ │ └── columns: c_phone:13!null c_acctbal:14!null 2449 │ │ │ │ └── filters 2450 │ │ │ │ ├── c_acctbal:14 > 0.0 [outer=(14), constraints=(/14: [/5e-324 - ]; tight)] 2451 │ │ │ │ └── substring(c_phone:13, 1, 2) IN ('13', '17', '18', '23', '29', '30', '31') [outer=(13), immutable] 2452 │ │ │ └── aggregations 2453 │ │ │ └── avg [as=avg:17, outer=(14)] 2454 │ │ │ └── c_acctbal:14 2455 │ │ └── filters (true) 2456 │ └── projections 2457 │ └── substring(c_phone:5, 1, 2) [as=cntrycode:27, outer=(5), immutable] 2458 └── aggregations 2459 ├── count-rows [as=count_rows:28] 2460 └── sum [as=sum:29, outer=(6)] 2461 └── c_acctbal:6