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