github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/benchmark/tpch/04_CLEANUP/03_Snapshot_tpch.result (about) 1 use tpch; 2 select 3 l_returnflag, 4 l_linestatus, 5 sum(l_quantity) as sum_qty, 6 sum(l_extendedprice) as sum_base_price, 7 sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, 8 sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, 9 avg(l_quantity) as avg_qty, 10 avg(l_extendedprice) as avg_price, 11 avg(l_discount) as avg_disc, 12 count(*) as count_order 13 from 14 lineitem {snapshot = 'tpch_snapshot'} 15 where 16 l_shipdate <= date '1998-12-01' - interval '112' day 17 group by 18 l_returnflag, 19 l_linestatus 20 order by 21 l_returnflag, 22 l_linestatus 23 ; 24 l_returnflag l_linestatus sum_qty sum_base_price sum_disc_price sum_charge avg_qty avg_price avg_disc count_order 25 A F 37474.00 37569624.64 35676192.0970 37101416.222424 25.35453315 25419.23182679 0.05086604 1478 26 N F 1041.00 1041301.07 999060.8980 1036450.802280 27.39473684 27402.65973684 0.04289474 38 27 N O 73962.00 74177309.20 70512214.0527 73308018.638225 25.52173913 25596.03492063 0.04965148 2898 28 R F 36511.00 36570841.24 34738472.8758 36169060.112193 25.05902539 25100.09693892 0.05002745 1457 29 select 30 l_returnflag, 31 l_linestatus, 32 sum(l_quantity) as sum_qty, 33 sum(l_extendedprice) as sum_base_price, 34 sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, 35 sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, 36 avg(l_quantity) as avg_qty, 37 avg(l_extendedprice) as avg_price, 38 avg(l_discount) as avg_disc, 39 count(*) as count_order 40 from 41 lineitem {snapshot = 'tpch_snapshot'} 42 where 43 l_shipdate <= date '1998-12-01' - interval '112' day 44 group by 45 l_returnflag, 46 l_linestatus 47 order by 48 l_returnflag, 49 l_linestatus 50 ; 51 l_returnflag l_linestatus sum_qty sum_base_price sum_disc_price sum_charge avg_qty avg_price avg_disc count_order 52 A F 37474.00 37569624.64 35676192.0970 37101416.222424 25.35453315 25419.23182679 0.05086604 1478 53 N F 1041.00 1041301.07 999060.8980 1036450.802280 27.39473684 27402.65973684 0.04289474 38 54 N O 73962.00 74177309.20 70512214.0527 73308018.638225 25.52173913 25596.03492063 0.04965148 2898 55 R F 36511.00 36570841.24 34738472.8758 36169060.112193 25.05902539 25100.09693892 0.05002745 1457 56 select 57 l_returnflag, 58 l_linestatus, 59 sum(l_quantity) as sum_qty, 60 sum(l_extendedprice) as sum_base_price, 61 sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, 62 sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, 63 avg(l_quantity) as avg_qty, 64 avg(l_extendedprice) as avg_price, 65 avg(l_discount) as avg_disc, 66 count(*) as count_order 67 from 68 lineitem {snapshot = 'tpch_snapshot'} 69 where 70 l_shipdate <= date '1998-12-01' - interval '112' day 71 group by 72 l_returnflag, 73 l_linestatus 74 order by 75 l_returnflag, 76 l_linestatus 77 ; 78 l_returnflag l_linestatus sum_qty sum_base_price sum_disc_price sum_charge avg_qty avg_price avg_disc count_order 79 A F 37474.00 37569624.64 35676192.0970 37101416.222424 25.35453315 25419.23182679 0.05086604 1478 80 N F 1041.00 1041301.07 999060.8980 1036450.802280 27.39473684 27402.65973684 0.04289474 38 81 N O 73962.00 74177309.20 70512214.0527 73308018.638225 25.52173913 25596.03492063 0.04965148 2898 82 R F 36511.00 36570841.24 34738472.8758 36169060.112193 25.05902539 25100.09693892 0.05002745 1457 83 select 84 s_acctbal, 85 s_name, 86 n_name, 87 p_partkey, 88 p_mfgr, 89 s_address, 90 s_phone, 91 s_comment 92 from 93 part {snapshot = 'tpch_snapshot'}, 94 supplier {snapshot = 'tpch_snapshot'}, 95 partsupp {snapshot = 'tpch_snapshot'}, 96 nation {snapshot = 'tpch_snapshot'}, 97 region {snapshot = 'tpch_snapshot'} 98 where 99 p_partkey = ps_partkey 100 and s_suppkey = ps_suppkey 101 and p_size = 48 102 and p_type like '%TIN' 103 and s_nationkey = n_nationkey 104 and n_regionkey = r_regionkey 105 and r_name = 'MIDDLE EAST' 106 and ps_supplycost = ( 107 select 108 min(ps_supplycost) 109 from 110 partsupp {snapshot = 'tpch_snapshot'}, 111 supplier {snapshot = 'tpch_snapshot'}, 112 nation {snapshot = 'tpch_snapshot'}, 113 region {snapshot = 'tpch_snapshot'} 114 where 115 p_partkey = ps_partkey 116 and s_suppkey = ps_suppkey 117 and s_nationkey = n_nationkey 118 and n_regionkey = r_regionkey 119 and r_name = 'MIDDLE EAST' 120 ) 121 order by 122 s_acctbal desc, 123 n_name, 124 s_name, 125 p_partkey 126 limit 100 127 ; 128 s_acctbal s_name n_name p_partkey p_mfgr s_address s_phone s_comment 129 -283.84 Supplier#000000005 IRAQ 44 Manufacturer#4 Gcdm2rJRzl5qlTVzc 21-151-690-3663 . slyly regular pinto bea 130 select 131 s_acctbal, 132 s_name, 133 n_name, 134 p_partkey, 135 p_mfgr, 136 s_address, 137 s_phone, 138 s_comment 139 from 140 part {snapshot = 'tpch_snapshot'}, 141 supplier {snapshot = 'tpch_snapshot'}, 142 partsupp {snapshot = 'tpch_snapshot'}, 143 nation {snapshot = 'tpch_snapshot'}, 144 region {snapshot = 'tpch_snapshot'} 145 where 146 p_partkey = ps_partkey 147 and s_suppkey = ps_suppkey 148 and p_size = 48 149 and p_type like '%TIN' 150 and s_nationkey = n_nationkey 151 and n_regionkey = r_regionkey 152 and r_name = 'MIDDLE EAST' 153 and ps_supplycost = ( 154 select 155 min(ps_supplycost) 156 from 157 partsupp {snapshot = 'tpch_snapshot'}, 158 supplier {snapshot = 'tpch_snapshot'}, 159 nation {snapshot = 'tpch_snapshot'}, 160 region {snapshot = 'tpch_snapshot'} 161 where 162 p_partkey = ps_partkey 163 and s_suppkey = ps_suppkey 164 and s_nationkey = n_nationkey 165 and n_regionkey = r_regionkey 166 and r_name = 'MIDDLE EAST' 167 ) 168 order by 169 s_acctbal desc, 170 n_name, 171 s_name, 172 p_partkey 173 limit 100 174 ; 175 s_acctbal s_name n_name p_partkey p_mfgr s_address s_phone s_comment 176 -283.84 Supplier#000000005 IRAQ 44 Manufacturer#4 Gcdm2rJRzl5qlTVzc 21-151-690-3663 . slyly regular pinto bea 177 select 178 s_acctbal, 179 s_name, 180 n_name, 181 p_partkey, 182 p_mfgr, 183 s_address, 184 s_phone, 185 s_comment 186 from 187 part {snapshot = 'tpch_snapshot'}, 188 supplier {snapshot = 'tpch_snapshot'}, 189 partsupp {snapshot = 'tpch_snapshot'}, 190 nation {snapshot = 'tpch_snapshot'}, 191 region {snapshot = 'tpch_snapshot'} 192 where 193 p_partkey = ps_partkey 194 and s_suppkey = ps_suppkey 195 and p_size = 48 196 and p_type like '%TIN' 197 and s_nationkey = n_nationkey 198 and n_regionkey = r_regionkey 199 and r_name = 'MIDDLE EAST' 200 and ps_supplycost = ( 201 select 202 min(ps_supplycost) 203 from 204 partsupp {snapshot = 'tpch_snapshot'}, 205 supplier {snapshot = 'tpch_snapshot'}, 206 nation {snapshot = 'tpch_snapshot'}, 207 region {snapshot = 'tpch_snapshot'} 208 where 209 p_partkey = ps_partkey 210 and s_suppkey = ps_suppkey 211 and s_nationkey = n_nationkey 212 and n_regionkey = r_regionkey 213 and r_name = 'MIDDLE EAST' 214 ) 215 order by 216 s_acctbal desc, 217 n_name, 218 s_name, 219 p_partkey 220 limit 100 221 ; 222 s_acctbal s_name n_name p_partkey p_mfgr s_address s_phone s_comment 223 -283.84 Supplier#000000005 IRAQ 44 Manufacturer#4 Gcdm2rJRzl5qlTVzc 21-151-690-3663 . slyly regular pinto bea 224 select 225 l_orderkey, 226 sum(l_extendedprice * (1 - l_discount)) as revenue, 227 o_orderdate, 228 o_shippriority 229 from 230 customer {snapshot = 'tpch_snapshot'}, 231 orders {snapshot = 'tpch_snapshot'}, 232 lineitem {snapshot = 'tpch_snapshot'} 233 where 234 c_mktsegment = 'HOUSEHOLD' 235 and c_custkey = o_custkey 236 and l_orderkey = o_orderkey 237 and o_orderdate < date '1995-03-29' 238 and l_shipdate > date '1995-03-29' 239 group by 240 l_orderkey, 241 o_orderdate, 242 o_shippriority 243 order by 244 revenue desc, 245 o_orderdate 246 limit 10 247 ; 248 l_orderkey revenue o_orderdate o_shippriority 249 643 174011.2942 1995-03-25 0 250 5444 166691.7175 1995-03-18 0 251 4642 113368.5066 1995-02-27 0 252 3749 83734.8686 1995-02-24 0 253 5955 65943.2992 1995-03-27 0 254 5765 65713.4436 1994-12-15 0 255 5636 64688.1780 1995-02-16 0 256 930 51611.7600 1994-12-17 0 257 1445 44384.8914 1995-01-10 0 258 3399 36727.7730 1995-02-28 0 259 select 260 l_orderkey, 261 sum(l_extendedprice * (1 - l_discount)) as revenue, 262 o_orderdate, 263 o_shippriority 264 from 265 customer {snapshot = 'tpch_snapshot'}, 266 orders {snapshot = 'tpch_snapshot'}, 267 lineitem {snapshot = 'tpch_snapshot'} 268 where 269 c_mktsegment = 'HOUSEHOLD' 270 and c_custkey = o_custkey 271 and l_orderkey = o_orderkey 272 and o_orderdate < date '1995-03-29' 273 and l_shipdate > date '1995-03-29' 274 group by 275 l_orderkey, 276 o_orderdate, 277 o_shippriority 278 order by 279 revenue desc, 280 o_orderdate 281 limit 10 282 ; 283 l_orderkey revenue o_orderdate o_shippriority 284 643 174011.2942 1995-03-25 0 285 5444 166691.7175 1995-03-18 0 286 4642 113368.5066 1995-02-27 0 287 3749 83734.8686 1995-02-24 0 288 5955 65943.2992 1995-03-27 0 289 5765 65713.4436 1994-12-15 0 290 5636 64688.1780 1995-02-16 0 291 930 51611.7600 1994-12-17 0 292 1445 44384.8914 1995-01-10 0 293 3399 36727.7730 1995-02-28 0 294 select 295 l_orderkey, 296 sum(l_extendedprice * (1 - l_discount)) as revenue, 297 o_orderdate, 298 o_shippriority 299 from 300 customer {snapshot = 'tpch_snapshot'}, 301 orders {snapshot = 'tpch_snapshot'}, 302 lineitem {snapshot = 'tpch_snapshot'} 303 where 304 c_mktsegment = 'HOUSEHOLD' 305 and c_custkey = o_custkey 306 and l_orderkey = o_orderkey 307 and o_orderdate < date '1995-03-29' 308 and l_shipdate > date '1995-03-29' 309 group by 310 l_orderkey, 311 o_orderdate, 312 o_shippriority 313 order by 314 revenue desc, 315 o_orderdate 316 limit 10 317 ; 318 l_orderkey revenue o_orderdate o_shippriority 319 643 174011.2942 1995-03-25 0 320 5444 166691.7175 1995-03-18 0 321 4642 113368.5066 1995-02-27 0 322 3749 83734.8686 1995-02-24 0 323 5955 65943.2992 1995-03-27 0 324 5765 65713.4436 1994-12-15 0 325 5636 64688.1780 1995-02-16 0 326 930 51611.7600 1994-12-17 0 327 1445 44384.8914 1995-01-10 0 328 3399 36727.7730 1995-02-28 0 329 select 330 o_orderpriority, 331 count(*) as order_count 332 from 333 orders {snapshot = 'tpch_snapshot'} 334 where 335 o_orderdate >= date '1997-07-01' 336 and o_orderdate < date '1997-07-01' + interval '3' month 337 and exists ( 338 select 339 * 340 from 341 lineitem {snapshot = 'tpch_snapshot'} 342 where 343 l_orderkey = o_orderkey 344 and l_commitdate < l_receiptdate 345 ) 346 group by 347 o_orderpriority 348 order by 349 o_orderpriority 350 ; 351 o_orderpriority order_count 352 1-URGENT 16 353 2-HIGH 9 354 3-MEDIUM 6 355 4-NOT SPECIFIED 8 356 5-LOW 6 357 select 358 o_orderpriority, 359 count(*) as order_count 360 from 361 orders {snapshot = 'tpch_snapshot'} 362 where 363 o_orderdate >= date '1997-07-01' 364 and o_orderdate < date '1997-07-01' + interval '3' month 365 and exists ( 366 select 367 * 368 from 369 lineitem {snapshot = 'tpch_snapshot'} 370 where 371 l_orderkey = o_orderkey 372 and l_commitdate < l_receiptdate 373 ) 374 group by 375 o_orderpriority 376 order by 377 o_orderpriority 378 ; 379 o_orderpriority order_count 380 1-URGENT 16 381 2-HIGH 9 382 3-MEDIUM 6 383 4-NOT SPECIFIED 8 384 5-LOW 6 385 select 386 o_orderpriority, 387 count(*) as order_count 388 from 389 orders {snapshot = 'tpch_snapshot'} 390 where 391 o_orderdate >= date '1997-07-01' 392 and o_orderdate < date '1997-07-01' + interval '3' month 393 and exists ( 394 select 395 * 396 from 397 lineitem {snapshot = 'tpch_snapshot'} 398 where 399 l_orderkey = o_orderkey 400 and l_commitdate < l_receiptdate 401 ) 402 group by 403 o_orderpriority 404 order by 405 o_orderpriority 406 ; 407 o_orderpriority order_count 408 1-URGENT 16 409 2-HIGH 9 410 3-MEDIUM 6 411 4-NOT SPECIFIED 8 412 5-LOW 6 413 select 414 n_name, 415 sum(l_extendedprice * (1 - l_discount)) as revenue 416 from 417 customer {snapshot = 'tpch_snapshot'}, 418 orders {snapshot = 'tpch_snapshot'}, 419 lineitem {snapshot = 'tpch_snapshot'}, 420 supplier {snapshot = 'tpch_snapshot'}, 421 nation {snapshot = 'tpch_snapshot'}, 422 region {snapshot = 'tpch_snapshot'} 423 where 424 c_custkey = o_custkey 425 and l_orderkey = o_orderkey 426 and l_suppkey = s_suppkey 427 and c_nationkey = s_nationkey 428 and s_nationkey = n_nationkey 429 and n_regionkey = r_regionkey 430 and r_name = 'AMERICA' 431 and o_orderdate >= date '1994-01-01' 432 and o_orderdate < date '1994-01-01' + interval '1' year 433 group by 434 n_name 435 order by 436 revenue desc 437 ; 438 n_name revenue 439 PERU 272671.5220 440 select 441 n_name, 442 sum(l_extendedprice * (1 - l_discount)) as revenue 443 from 444 customer {snapshot = 'tpch_snapshot'}, 445 orders {snapshot = 'tpch_snapshot'}, 446 lineitem {snapshot = 'tpch_snapshot'}, 447 supplier {snapshot = 'tpch_snapshot'}, 448 nation {snapshot = 'tpch_snapshot'}, 449 region {snapshot = 'tpch_snapshot'} 450 where 451 c_custkey = o_custkey 452 and l_orderkey = o_orderkey 453 and l_suppkey = s_suppkey 454 and c_nationkey = s_nationkey 455 and s_nationkey = n_nationkey 456 and n_regionkey = r_regionkey 457 and r_name = 'AMERICA' 458 and o_orderdate >= date '1994-01-01' 459 and o_orderdate < date '1994-01-01' + interval '1' year 460 group by 461 n_name 462 order by 463 revenue desc 464 ; 465 n_name revenue 466 PERU 272671.5220 467 select 468 n_name, 469 sum(l_extendedprice * (1 - l_discount)) as revenue 470 from 471 customer {snapshot = 'tpch_snapshot'}, 472 orders {snapshot = 'tpch_snapshot'}, 473 lineitem {snapshot = 'tpch_snapshot'}, 474 supplier {snapshot = 'tpch_snapshot'}, 475 nation {snapshot = 'tpch_snapshot'}, 476 region {snapshot = 'tpch_snapshot'} 477 where 478 c_custkey = o_custkey 479 and l_orderkey = o_orderkey 480 and l_suppkey = s_suppkey 481 and c_nationkey = s_nationkey 482 and s_nationkey = n_nationkey 483 and n_regionkey = r_regionkey 484 and r_name = 'AMERICA' 485 and o_orderdate >= date '1994-01-01' 486 and o_orderdate < date '1994-01-01' + interval '1' year 487 group by 488 n_name 489 order by 490 revenue desc 491 ; 492 n_name revenue 493 PERU 272671.5220 494 select 495 sum(l_extendedprice * l_discount) as revenue 496 from 497 lineitem {snapshot = 'tpch_snapshot'} 498 where 499 l_shipdate >= date '1994-01-01' 500 and l_shipdate < date '1994-01-01' + interval '1' year 501 and l_discount between 0.03 - 0.01 and 0.03 + 0.01 502 and l_quantity < 24; 503 revenue 504 43092.5479 505 select 506 sum(l_extendedprice * l_discount) as revenue 507 from 508 lineitem {snapshot = 'tpch_snapshot'} 509 where 510 l_shipdate >= date '1994-01-01' 511 and l_shipdate < date '1994-01-01' + interval '1' year 512 and l_discount between 0.03 - 0.01 and 0.03 + 0.01 513 and l_quantity < 24; 514 revenue 515 43092.5479 516 select 517 sum(l_extendedprice * l_discount) as revenue 518 from 519 lineitem {snapshot = 'tpch_snapshot'} 520 where 521 l_shipdate >= date '1994-01-01' 522 and l_shipdate < date '1994-01-01' + interval '1' year 523 and l_discount between 0.03 - 0.01 and 0.03 + 0.01 524 and l_quantity < 24; 525 revenue 526 43092.5479 527 select 528 supp_nation, 529 cust_nation, 530 l_year, 531 sum(volume) as revenue 532 from 533 ( 534 select 535 n1.n_name as supp_nation, 536 n2.n_name as cust_nation, 537 extract(year from l_shipdate) as l_year, 538 l_extendedprice * (1 - l_discount) as volume 539 from 540 supplier {snapshot = 'tpch_snapshot'}, 541 lineitem {snapshot = 'tpch_snapshot'}, 542 orders {snapshot = 'tpch_snapshot'}, 543 customer {snapshot = 'tpch_snapshot'}, 544 nation {snapshot = 'tpch_snapshot'} n1 , 545 nation {snapshot = 'tpch_snapshot'} n2 546 where 547 s_suppkey = l_suppkey 548 and o_orderkey = l_orderkey 549 and c_custkey = o_custkey 550 and s_nationkey = n1.n_nationkey 551 and c_nationkey = n2.n_nationkey 552 and ( 553 (n1.n_name = 'FRANCE' and n2.n_name = 'ARGENTINA') 554 or (n1.n_name = 'ARGENTINA' and n2.n_name = 'FRANCE') 555 ) 556 and l_shipdate between date '1995-01-01' and date '1996-12-31' 557 ) as shipping 558 group by 559 supp_nation, 560 cust_nation, 561 l_year 562 order by 563 supp_nation, 564 cust_nation, 565 l_year 566 ; 567 supp_nation cust_nation l_year revenue 568 ARGENTINA FRANCE 1995 36554.0010 569 ARGENTINA FRANCE 1996 64102.9664 570 select 571 supp_nation, 572 cust_nation, 573 l_year, 574 sum(volume) as revenue 575 from 576 ( 577 select 578 n1.n_name as supp_nation, 579 n2.n_name as cust_nation, 580 extract(year from l_shipdate) as l_year, 581 l_extendedprice * (1 - l_discount) as volume 582 from 583 supplier {snapshot = 'tpch_snapshot'}, 584 lineitem {snapshot = 'tpch_snapshot'}, 585 orders {snapshot = 'tpch_snapshot'}, 586 customer {snapshot = 'tpch_snapshot'}, 587 nation {snapshot = 'tpch_snapshot'} n1 , 588 nation {snapshot = 'tpch_snapshot'} n2 589 where 590 s_suppkey = l_suppkey 591 and o_orderkey = l_orderkey 592 and c_custkey = o_custkey 593 and s_nationkey = n1.n_nationkey 594 and c_nationkey = n2.n_nationkey 595 and ( 596 (n1.n_name = 'FRANCE' and n2.n_name = 'ARGENTINA') 597 or (n1.n_name = 'ARGENTINA' and n2.n_name = 'FRANCE') 598 ) 599 and l_shipdate between date '1995-01-01' and date '1996-12-31' 600 ) as shipping 601 group by 602 supp_nation, 603 cust_nation, 604 l_year 605 order by 606 supp_nation, 607 cust_nation, 608 l_year 609 ; 610 supp_nation cust_nation l_year revenue 611 ARGENTINA FRANCE 1995 36554.0010 612 ARGENTINA FRANCE 1996 64102.9664 613 select 614 supp_nation, 615 cust_nation, 616 l_year, 617 sum(volume) as revenue 618 from 619 ( 620 select 621 n1.n_name as supp_nation, 622 n2.n_name as cust_nation, 623 extract(year from l_shipdate) as l_year, 624 l_extendedprice * (1 - l_discount) as volume 625 from 626 supplier {snapshot = 'tpch_snapshot'}, 627 lineitem {snapshot = 'tpch_snapshot'}, 628 orders {snapshot = 'tpch_snapshot'}, 629 customer {snapshot = 'tpch_snapshot'}, 630 nation {snapshot = 'tpch_snapshot'} n1 , 631 nation {snapshot = 'tpch_snapshot'} n2 632 where 633 s_suppkey = l_suppkey 634 and o_orderkey = l_orderkey 635 and c_custkey = o_custkey 636 and s_nationkey = n1.n_nationkey 637 and c_nationkey = n2.n_nationkey 638 and ( 639 (n1.n_name = 'FRANCE' and n2.n_name = 'ARGENTINA') 640 or (n1.n_name = 'ARGENTINA' and n2.n_name = 'FRANCE') 641 ) 642 and l_shipdate between date '1995-01-01' and date '1996-12-31' 643 ) as shipping 644 group by 645 supp_nation, 646 cust_nation, 647 l_year 648 order by 649 supp_nation, 650 cust_nation, 651 l_year 652 ; 653 supp_nation cust_nation l_year revenue 654 ARGENTINA FRANCE 1995 36554.0010 655 ARGENTINA FRANCE 1996 64102.9664 656 select 657 o_year, 658 (sum(case 659 when nation = 'ARGENTINA' then volume 660 else 0 661 end) / sum(volume)) as mkt_share 662 from 663 ( 664 select 665 extract(year from o_orderdate) as o_year, 666 l_extendedprice * (1 - l_discount) as volume, 667 n2.n_name as nation 668 from 669 part {snapshot = 'tpch_snapshot'}, 670 supplier {snapshot = 'tpch_snapshot'}, 671 lineitem {snapshot = 'tpch_snapshot'}, 672 orders {snapshot = 'tpch_snapshot'}, 673 customer {snapshot = 'tpch_snapshot'}, 674 nation {snapshot = 'tpch_snapshot'} n1, 675 nation {snapshot = 'tpch_snapshot'} n2, 676 region {snapshot = 'tpch_snapshot'} 677 where 678 p_partkey = l_partkey 679 and s_suppkey = l_suppkey 680 and l_orderkey = o_orderkey 681 and o_custkey = c_custkey 682 and c_nationkey = n1.n_nationkey 683 and n1.n_regionkey = r_regionkey 684 and r_name = 'AMERICA' 685 and s_nationkey = n2.n_nationkey 686 and o_orderdate between date '1995-01-01' and date '1996-12-31' 687 and p_type = 'ECONOMY BURNISHED TIN' 688 ) as all_nations 689 group by 690 o_year 691 order by 692 o_year 693 ; 694 o_year mkt_share 695 1995 0E-10 696 1996 0E-10 697 select 698 o_year, 699 (sum(case 700 when nation = 'ARGENTINA' then volume 701 else 0 702 end) / sum(volume)) as mkt_share 703 from 704 ( 705 select 706 extract(year from o_orderdate) as o_year, 707 l_extendedprice * (1 - l_discount) as volume, 708 n2.n_name as nation 709 from 710 part {snapshot = 'tpch_snapshot'}, 711 supplier {snapshot = 'tpch_snapshot'}, 712 lineitem {snapshot = 'tpch_snapshot'}, 713 orders {snapshot = 'tpch_snapshot'}, 714 customer {snapshot = 'tpch_snapshot'}, 715 nation {snapshot = 'tpch_snapshot'} n1, 716 nation {snapshot = 'tpch_snapshot'} n2, 717 region {snapshot = 'tpch_snapshot'} 718 where 719 p_partkey = l_partkey 720 and s_suppkey = l_suppkey 721 and l_orderkey = o_orderkey 722 and o_custkey = c_custkey 723 and c_nationkey = n1.n_nationkey 724 and n1.n_regionkey = r_regionkey 725 and r_name = 'AMERICA' 726 and s_nationkey = n2.n_nationkey 727 and o_orderdate between date '1995-01-01' and date '1996-12-31' 728 and p_type = 'ECONOMY BURNISHED TIN' 729 ) as all_nations 730 group by 731 o_year 732 order by 733 o_year 734 ; 735 o_year mkt_share 736 1995 0E-10 737 1996 0E-10 738 select 739 o_year, 740 (sum(case 741 when nation = 'ARGENTINA' then volume 742 else 0 743 end) / sum(volume)) as mkt_share 744 from 745 ( 746 select 747 extract(year from o_orderdate) as o_year, 748 l_extendedprice * (1 - l_discount) as volume, 749 n2.n_name as nation 750 from 751 part {snapshot = 'tpch_snapshot'}, 752 supplier {snapshot = 'tpch_snapshot'}, 753 lineitem {snapshot = 'tpch_snapshot'}, 754 orders {snapshot = 'tpch_snapshot'}, 755 customer {snapshot = 'tpch_snapshot'}, 756 nation {snapshot = 'tpch_snapshot'} n1, 757 nation {snapshot = 'tpch_snapshot'} n2, 758 region {snapshot = 'tpch_snapshot'} 759 where 760 p_partkey = l_partkey 761 and s_suppkey = l_suppkey 762 and l_orderkey = o_orderkey 763 and o_custkey = c_custkey 764 and c_nationkey = n1.n_nationkey 765 and n1.n_regionkey = r_regionkey 766 and r_name = 'AMERICA' 767 and s_nationkey = n2.n_nationkey 768 and o_orderdate between date '1995-01-01' and date '1996-12-31' 769 and p_type = 'ECONOMY BURNISHED TIN' 770 ) as all_nations 771 group by 772 o_year 773 order by 774 o_year 775 ; 776 o_year mkt_share 777 1995 0E-10 778 1996 0E-10 779 select 780 nation, 781 o_year, 782 sum(amount) as sum_profit 783 from 784 ( 785 select 786 n_name as nation, 787 extract(year from o_orderdate) as o_year, 788 l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount 789 from 790 part {snapshot = 'tpch_snapshot'}, 791 supplier {snapshot = 'tpch_snapshot'}, 792 lineitem {snapshot = 'tpch_snapshot'}, 793 partsupp {snapshot = 'tpch_snapshot'}, 794 orders {snapshot = 'tpch_snapshot'}, 795 nation {snapshot = 'tpch_snapshot'} 796 where 797 s_suppkey = l_suppkey 798 and ps_suppkey = l_suppkey 799 and ps_partkey = l_partkey 800 and p_partkey = l_partkey 801 and o_orderkey = l_orderkey 802 and s_nationkey = n_nationkey 803 and p_name like '%pink%' 804 ) as profit 805 group by 806 nation, 807 o_year 808 order by 809 nation, 810 o_year desc 811 ; 812 nation o_year sum_profit 813 ARGENTINA 1997 6575.4934 814 ARGENTINA 1996 12913.1476 815 ARGENTINA 1995 109908.3997 816 ARGENTINA 1994 39271.1404 817 ARGENTINA 1993 39770.6973 818 ARGENTINA 1992 35740.0000 819 ETHIOPIA 1998 24489.1496 820 ETHIOPIA 1997 39665.2644 821 ETHIOPIA 1996 113173.0956 822 ETHIOPIA 1995 25732.7452 823 ETHIOPIA 1994 93731.1008 824 ETHIOPIA 1993 147385.8720 825 ETHIOPIA 1992 66636.9616 826 IRAN 1998 116681.1813 827 IRAN 1997 50964.2584 828 IRAN 1996 185552.5634 829 IRAN 1995 103479.6512 830 IRAN 1994 164106.8680 831 IRAN 1993 100245.6590 832 IRAN 1992 73757.9540 833 IRAQ 1998 21292.0377 834 IRAQ 1997 128533.3496 835 IRAQ 1996 141170.6272 836 IRAQ 1995 16607.1624 837 IRAQ 1994 16515.2008 838 IRAQ 1993 138752.8297 839 IRAQ 1992 71365.9886 840 KENYA 1997 47549.3594 841 KENYA 1996 1866.6024 842 KENYA 1995 13480.5734 843 KENYA 1994 54826.0012 844 KENYA 1993 111983.0035 845 KENYA 1992 7651.5866 846 MOROCCO 1998 225961.3058 847 MOROCCO 1997 288732.1257 848 MOROCCO 1996 295243.1019 849 MOROCCO 1995 255208.9581 850 MOROCCO 1994 158194.0814 851 MOROCCO 1993 159196.2528 852 MOROCCO 1992 256151.4254 853 PERU 1998 35151.8682 854 PERU 1997 99779.3198 855 PERU 1996 53735.2566 856 PERU 1995 71954.3164 857 PERU 1994 73084.5552 858 PERU 1993 95623.4989 859 PERU 1992 38212.4187 860 UNITED KINGDOM 1998 176649.9065 861 UNITED KINGDOM 1997 74705.2598 862 UNITED KINGDOM 1996 62768.5129 863 UNITED KINGDOM 1995 93466.7552 864 UNITED KINGDOM 1994 89094.2599 865 UNITED KINGDOM 1993 112886.5742 866 UNITED KINGDOM 1992 142060.9690 867 UNITED STATES 1997 111352.3559 868 UNITED STATES 1996 78268.8638 869 UNITED STATES 1995 251.2274 870 UNITED STATES 1994 57303.1500 871 UNITED STATES 1993 70350.5923 872 UNITED STATES 1992 65369.7612 873 select 874 nation, 875 o_year, 876 sum(amount) as sum_profit 877 from 878 ( 879 select 880 n_name as nation, 881 extract(year from o_orderdate) as o_year, 882 l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount 883 from 884 part {snapshot = 'tpch_snapshot'}, 885 supplier {snapshot = 'tpch_snapshot'}, 886 lineitem {snapshot = 'tpch_snapshot'}, 887 partsupp {snapshot = 'tpch_snapshot'}, 888 orders {snapshot = 'tpch_snapshot'}, 889 nation {snapshot = 'tpch_snapshot'} 890 where 891 s_suppkey = l_suppkey 892 and ps_suppkey = l_suppkey 893 and ps_partkey = l_partkey 894 and p_partkey = l_partkey 895 and o_orderkey = l_orderkey 896 and s_nationkey = n_nationkey 897 and p_name like '%pink%' 898 ) as profit 899 group by 900 nation, 901 o_year 902 order by 903 nation, 904 o_year desc 905 ; 906 nation o_year sum_profit 907 ARGENTINA 1997 6575.4934 908 ARGENTINA 1996 12913.1476 909 ARGENTINA 1995 109908.3997 910 ARGENTINA 1994 39271.1404 911 ARGENTINA 1993 39770.6973 912 ARGENTINA 1992 35740.0000 913 ETHIOPIA 1998 24489.1496 914 ETHIOPIA 1997 39665.2644 915 ETHIOPIA 1996 113173.0956 916 ETHIOPIA 1995 25732.7452 917 ETHIOPIA 1994 93731.1008 918 ETHIOPIA 1993 147385.8720 919 ETHIOPIA 1992 66636.9616 920 IRAN 1998 116681.1813 921 IRAN 1997 50964.2584 922 IRAN 1996 185552.5634 923 IRAN 1995 103479.6512 924 IRAN 1994 164106.8680 925 IRAN 1993 100245.6590 926 IRAN 1992 73757.9540 927 IRAQ 1998 21292.0377 928 IRAQ 1997 128533.3496 929 IRAQ 1996 141170.6272 930 IRAQ 1995 16607.1624 931 IRAQ 1994 16515.2008 932 IRAQ 1993 138752.8297 933 IRAQ 1992 71365.9886 934 KENYA 1997 47549.3594 935 KENYA 1996 1866.6024 936 KENYA 1995 13480.5734 937 KENYA 1994 54826.0012 938 KENYA 1993 111983.0035 939 KENYA 1992 7651.5866 940 MOROCCO 1998 225961.3058 941 MOROCCO 1997 288732.1257 942 MOROCCO 1996 295243.1019 943 MOROCCO 1995 255208.9581 944 MOROCCO 1994 158194.0814 945 MOROCCO 1993 159196.2528 946 MOROCCO 1992 256151.4254 947 PERU 1998 35151.8682 948 PERU 1997 99779.3198 949 PERU 1996 53735.2566 950 PERU 1995 71954.3164 951 PERU 1994 73084.5552 952 PERU 1993 95623.4989 953 PERU 1992 38212.4187 954 UNITED KINGDOM 1998 176649.9065 955 UNITED KINGDOM 1997 74705.2598 956 UNITED KINGDOM 1996 62768.5129 957 UNITED KINGDOM 1995 93466.7552 958 UNITED KINGDOM 1994 89094.2599 959 UNITED KINGDOM 1993 112886.5742 960 UNITED KINGDOM 1992 142060.9690 961 UNITED STATES 1997 111352.3559 962 UNITED STATES 1996 78268.8638 963 UNITED STATES 1995 251.2274 964 UNITED STATES 1994 57303.1500 965 UNITED STATES 1993 70350.5923 966 UNITED STATES 1992 65369.7612 967 select 968 nation, 969 o_year, 970 sum(amount) as sum_profit 971 from 972 ( 973 select 974 n_name as nation, 975 extract(year from o_orderdate) as o_year, 976 l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount 977 from 978 part {snapshot = 'tpch_snapshot'}, 979 supplier {snapshot = 'tpch_snapshot'}, 980 lineitem {snapshot = 'tpch_snapshot'}, 981 partsupp {snapshot = 'tpch_snapshot'}, 982 orders {snapshot = 'tpch_snapshot'}, 983 nation {snapshot = 'tpch_snapshot'} 984 where 985 s_suppkey = l_suppkey 986 and ps_suppkey = l_suppkey 987 and ps_partkey = l_partkey 988 and p_partkey = l_partkey 989 and o_orderkey = l_orderkey 990 and s_nationkey = n_nationkey 991 and p_name like '%pink%' 992 ) as profit 993 group by 994 nation, 995 o_year 996 order by 997 nation, 998 o_year desc 999 ; 1000 nation o_year sum_profit 1001 ARGENTINA 1997 6575.4934 1002 ARGENTINA 1996 12913.1476 1003 ARGENTINA 1995 109908.3997 1004 ARGENTINA 1994 39271.1404 1005 ARGENTINA 1993 39770.6973 1006 ARGENTINA 1992 35740.0000 1007 ETHIOPIA 1998 24489.1496 1008 ETHIOPIA 1997 39665.2644 1009 ETHIOPIA 1996 113173.0956 1010 ETHIOPIA 1995 25732.7452 1011 ETHIOPIA 1994 93731.1008 1012 ETHIOPIA 1993 147385.8720 1013 ETHIOPIA 1992 66636.9616 1014 IRAN 1998 116681.1813 1015 IRAN 1997 50964.2584 1016 IRAN 1996 185552.5634 1017 IRAN 1995 103479.6512 1018 IRAN 1994 164106.8680 1019 IRAN 1993 100245.6590 1020 IRAN 1992 73757.9540 1021 IRAQ 1998 21292.0377 1022 IRAQ 1997 128533.3496 1023 IRAQ 1996 141170.6272 1024 IRAQ 1995 16607.1624 1025 IRAQ 1994 16515.2008 1026 IRAQ 1993 138752.8297 1027 IRAQ 1992 71365.9886 1028 KENYA 1997 47549.3594 1029 KENYA 1996 1866.6024 1030 KENYA 1995 13480.5734 1031 KENYA 1994 54826.0012 1032 KENYA 1993 111983.0035 1033 KENYA 1992 7651.5866 1034 MOROCCO 1998 225961.3058 1035 MOROCCO 1997 288732.1257 1036 MOROCCO 1996 295243.1019 1037 MOROCCO 1995 255208.9581 1038 MOROCCO 1994 158194.0814 1039 MOROCCO 1993 159196.2528 1040 MOROCCO 1992 256151.4254 1041 PERU 1998 35151.8682 1042 PERU 1997 99779.3198 1043 PERU 1996 53735.2566 1044 PERU 1995 71954.3164 1045 PERU 1994 73084.5552 1046 PERU 1993 95623.4989 1047 PERU 1992 38212.4187 1048 UNITED KINGDOM 1998 176649.9065 1049 UNITED KINGDOM 1997 74705.2598 1050 UNITED KINGDOM 1996 62768.5129 1051 UNITED KINGDOM 1995 93466.7552 1052 UNITED KINGDOM 1994 89094.2599 1053 UNITED KINGDOM 1993 112886.5742 1054 UNITED KINGDOM 1992 142060.9690 1055 UNITED STATES 1997 111352.3559 1056 UNITED STATES 1996 78268.8638 1057 UNITED STATES 1995 251.2274 1058 UNITED STATES 1994 57303.1500 1059 UNITED STATES 1993 70350.5923 1060 UNITED STATES 1992 65369.7612 1061 select 1062 c_custkey, 1063 c_name, 1064 sum(l_extendedprice * (1 - l_discount)) as revenue, 1065 c_acctbal, 1066 n_name, 1067 c_address, 1068 c_phone, 1069 c_comment 1070 from 1071 customer {snapshot = 'tpch_snapshot'}, 1072 orders {snapshot = 'tpch_snapshot'}, 1073 lineitem {snapshot = 'tpch_snapshot'}, 1074 nation {snapshot = 'tpch_snapshot'} 1075 where 1076 c_custkey = o_custkey 1077 and l_orderkey = o_orderkey 1078 and o_orderdate >= date '1993-03-01' 1079 and o_orderdate < date '1993-03-01' + interval '3' month 1080 and l_returnflag = 'R' 1081 and c_nationkey = n_nationkey 1082 group by 1083 c_custkey, 1084 c_name, 1085 c_acctbal, 1086 c_phone, 1087 n_name, 1088 c_address, 1089 c_comment 1090 order by 1091 revenue desc 1092 limit 20 1093 ; 1094 c_custkey c_name revenue c_acctbal n_name c_address c_phone c_comment 1095 70 Customer#000000070 316759.4208 4867.52 RUSSIA mFowIuhnHjp2GjCiYYavkW kUwOjIaTCQ 32-828-107-2832 fter the special asymptotes. ideas after the unusual frets cajole quickly regular pinto be 1096 109 Customer#000000109 272732.4698 -716.10 MOZAMBIQUE OOOkYBgCMzgMQXUmkocoLb56rfrdWp2NE2c 26-992-422-8153 es. fluffily final dependencies sleep along the blithely even pinto beans. final deposits haggle furiously furiou 1097 106 Customer#000000106 175284.3983 3288.42 ARGENTINA xGCOEAUjUNG 11-751-989-4627 lose slyly. ironic accounts along the evenly regular theodolites wake about the special, final gifts. 1098 2 Customer#000000002 121173.0081 121.65 JORDAN XSTf4,NCwDVaWNe6tEgvwfmRchLXak 23-768-687-3665 l accounts. blithely ironic theodolites integrate boldly: caref 1099 100 Customer#000000100 113840.4294 9889.89 SAUDI ARABIA fptUABXcmkC5Wx 30-749-445-4907 was furiously fluffily quiet deposits. silent, pending requests boost against 1100 58 Customer#000000058 103790.5932 6478.46 JORDAN g9ap7Dk1Sv9fcXEWjpMYpBZIRUohi T 23-244-493-2508 ideas. ironic ideas affix furiously express, final instructions. regular excuses use quickly e 1101 94 Customer#000000094 100840.0020 5500.11 INDONESIA IfVNIN9KtkScJ9dUjK3Pg5gY1aFeaXewwf 19-953-499-8833 latelets across the bold, final requests sleep according to the fluffily bold accounts. unusual deposits amon 1102 97 Customer#000000097 99194.2317 2164.48 PERU OApyejbhJG,0Iw3j rd1M 27-588-919-5638 haggle slyly. bold, special ideas are blithely above the thinly bold theo 1103 101 Customer#000000101 90564.6478 7470.96 BRAZIL sMmL2rNeHDltovSm Y 12-514-298-3699 sleep. pending packages detect slyly ironic pack 1104 52 Customer#000000052 85250.3310 5630.28 IRAQ 7 QOqGqqSy9jfV51BC71jcHJSD0 21-186-284-5998 ic platelets use evenly even accounts. stealthy theodolites cajole furiou 1105 142 Customer#000000142 74220.1107 2209.81 INDONESIA AnJ5lxtLjioClr2khl9pb8NLxG2, 19-407-425-2584 . even, express theodolites upo 1106 59 Customer#000000059 71312.3586 3458.60 ARGENTINA zLOCP0wh92OtBihgspOGl4 11-355-584-3112 ously final packages haggle blithely after the express deposits. furiou 1107 40 Customer#000000040 69690.6972 1335.30 CANADA gOnGWAyhSV1ofv 13-652-915-8939 rges impress after the slyly ironic courts. foxes are. blithely 1108 8 Customer#000000008 67088.8134 6819.74 PERU I0B10bB0AymmC, 0PrRYBCP1yGJ8xcBPmWhl5 27-147-574-9335 among the slyly regular theodolites kindle blithely courts. carefully even theodolites haggle slyly along the ide 1109 49 Customer#000000049 66789.7960 4573.94 IRAN cNgAeX7Fqrdf7HQN9EwjUa4nxT,68L FKAxzl 20-908-631-4424 nusual foxes! fluffily pending packages maintain to the regular 1110 26 Customer#000000026 66653.8296 5182.05 RUSSIA 8ljrc5ZeMl7UciP 32-363-455-4837 c requests use furiously ironic requests. slyly ironic dependencies us 1111 77 Customer#000000077 65603.6040 1738.87 PERU 4tAE5KdMFGD4byHtXF92vx 27-269-357-4674 uffily silent requests. carefully ironic asymptotes among the ironic hockey players are carefully bli 1112 136 Customer#000000136 64024.4532 -842.39 GERMANY QoLsJ0v5C1IQbh,DS1 17-501-210-4726 ackages sleep ironic, final courts. even requests above the blithely bold requests g 1113 112 Customer#000000112 62915.4416 2953.35 ROMANIA RcfgG3bO7QeCnfjqJT1 29-233-262-8382 rmanently unusual multipliers. blithely ruthless deposits are furiously along the 1114 80 Customer#000000080 57993.8394 7383.53 ALGERIA K,vtXp8qYB 10-267-172-7101 tect among the dependencies. bold accounts engage closely even pinto beans. ca 1115 select 1116 c_custkey, 1117 c_name, 1118 sum(l_extendedprice * (1 - l_discount)) as revenue, 1119 c_acctbal, 1120 n_name, 1121 c_address, 1122 c_phone, 1123 c_comment 1124 from 1125 customer {snapshot = 'tpch_snapshot'}, 1126 orders {snapshot = 'tpch_snapshot'}, 1127 lineitem {snapshot = 'tpch_snapshot'}, 1128 nation {snapshot = 'tpch_snapshot'} 1129 where 1130 c_custkey = o_custkey 1131 and l_orderkey = o_orderkey 1132 and o_orderdate >= date '1993-03-01' 1133 and o_orderdate < date '1993-03-01' + interval '3' month 1134 and l_returnflag = 'R' 1135 and c_nationkey = n_nationkey 1136 group by 1137 c_custkey, 1138 c_name, 1139 c_acctbal, 1140 c_phone, 1141 n_name, 1142 c_address, 1143 c_comment 1144 order by 1145 revenue desc 1146 limit 20 1147 ; 1148 c_custkey c_name revenue c_acctbal n_name c_address c_phone c_comment 1149 70 Customer#000000070 316759.4208 4867.52 RUSSIA mFowIuhnHjp2GjCiYYavkW kUwOjIaTCQ 32-828-107-2832 fter the special asymptotes. ideas after the unusual frets cajole quickly regular pinto be 1150 109 Customer#000000109 272732.4698 -716.10 MOZAMBIQUE OOOkYBgCMzgMQXUmkocoLb56rfrdWp2NE2c 26-992-422-8153 es. fluffily final dependencies sleep along the blithely even pinto beans. final deposits haggle furiously furiou 1151 106 Customer#000000106 175284.3983 3288.42 ARGENTINA xGCOEAUjUNG 11-751-989-4627 lose slyly. ironic accounts along the evenly regular theodolites wake about the special, final gifts. 1152 2 Customer#000000002 121173.0081 121.65 JORDAN XSTf4,NCwDVaWNe6tEgvwfmRchLXak 23-768-687-3665 l accounts. blithely ironic theodolites integrate boldly: caref 1153 100 Customer#000000100 113840.4294 9889.89 SAUDI ARABIA fptUABXcmkC5Wx 30-749-445-4907 was furiously fluffily quiet deposits. silent, pending requests boost against 1154 58 Customer#000000058 103790.5932 6478.46 JORDAN g9ap7Dk1Sv9fcXEWjpMYpBZIRUohi T 23-244-493-2508 ideas. ironic ideas affix furiously express, final instructions. regular excuses use quickly e 1155 94 Customer#000000094 100840.0020 5500.11 INDONESIA IfVNIN9KtkScJ9dUjK3Pg5gY1aFeaXewwf 19-953-499-8833 latelets across the bold, final requests sleep according to the fluffily bold accounts. unusual deposits amon 1156 97 Customer#000000097 99194.2317 2164.48 PERU OApyejbhJG,0Iw3j rd1M 27-588-919-5638 haggle slyly. bold, special ideas are blithely above the thinly bold theo 1157 101 Customer#000000101 90564.6478 7470.96 BRAZIL sMmL2rNeHDltovSm Y 12-514-298-3699 sleep. pending packages detect slyly ironic pack 1158 52 Customer#000000052 85250.3310 5630.28 IRAQ 7 QOqGqqSy9jfV51BC71jcHJSD0 21-186-284-5998 ic platelets use evenly even accounts. stealthy theodolites cajole furiou 1159 142 Customer#000000142 74220.1107 2209.81 INDONESIA AnJ5lxtLjioClr2khl9pb8NLxG2, 19-407-425-2584 . even, express theodolites upo 1160 59 Customer#000000059 71312.3586 3458.60 ARGENTINA zLOCP0wh92OtBihgspOGl4 11-355-584-3112 ously final packages haggle blithely after the express deposits. furiou 1161 40 Customer#000000040 69690.6972 1335.30 CANADA gOnGWAyhSV1ofv 13-652-915-8939 rges impress after the slyly ironic courts. foxes are. blithely 1162 8 Customer#000000008 67088.8134 6819.74 PERU I0B10bB0AymmC, 0PrRYBCP1yGJ8xcBPmWhl5 27-147-574-9335 among the slyly regular theodolites kindle blithely courts. carefully even theodolites haggle slyly along the ide 1163 49 Customer#000000049 66789.7960 4573.94 IRAN cNgAeX7Fqrdf7HQN9EwjUa4nxT,68L FKAxzl 20-908-631-4424 nusual foxes! fluffily pending packages maintain to the regular 1164 26 Customer#000000026 66653.8296 5182.05 RUSSIA 8ljrc5ZeMl7UciP 32-363-455-4837 c requests use furiously ironic requests. slyly ironic dependencies us 1165 77 Customer#000000077 65603.6040 1738.87 PERU 4tAE5KdMFGD4byHtXF92vx 27-269-357-4674 uffily silent requests. carefully ironic asymptotes among the ironic hockey players are carefully bli 1166 136 Customer#000000136 64024.4532 -842.39 GERMANY QoLsJ0v5C1IQbh,DS1 17-501-210-4726 ackages sleep ironic, final courts. even requests above the blithely bold requests g 1167 112 Customer#000000112 62915.4416 2953.35 ROMANIA RcfgG3bO7QeCnfjqJT1 29-233-262-8382 rmanently unusual multipliers. blithely ruthless deposits are furiously along the 1168 80 Customer#000000080 57993.8394 7383.53 ALGERIA K,vtXp8qYB 10-267-172-7101 tect among the dependencies. bold accounts engage closely even pinto beans. ca 1169 select 1170 c_custkey, 1171 c_name, 1172 sum(l_extendedprice * (1 - l_discount)) as revenue, 1173 c_acctbal, 1174 n_name, 1175 c_address, 1176 c_phone, 1177 c_comment 1178 from 1179 customer {snapshot = 'tpch_snapshot'}, 1180 orders {snapshot = 'tpch_snapshot'}, 1181 lineitem {snapshot = 'tpch_snapshot'}, 1182 nation {snapshot = 'tpch_snapshot'} 1183 where 1184 c_custkey = o_custkey 1185 and l_orderkey = o_orderkey 1186 and o_orderdate >= date '1993-03-01' 1187 and o_orderdate < date '1993-03-01' + interval '3' month 1188 and l_returnflag = 'R' 1189 and c_nationkey = n_nationkey 1190 group by 1191 c_custkey, 1192 c_name, 1193 c_acctbal, 1194 c_phone, 1195 n_name, 1196 c_address, 1197 c_comment 1198 order by 1199 revenue desc 1200 limit 20 1201 ; 1202 c_custkey c_name revenue c_acctbal n_name c_address c_phone c_comment 1203 70 Customer#000000070 316759.4208 4867.52 RUSSIA mFowIuhnHjp2GjCiYYavkW kUwOjIaTCQ 32-828-107-2832 fter the special asymptotes. ideas after the unusual frets cajole quickly regular pinto be 1204 109 Customer#000000109 272732.4698 -716.10 MOZAMBIQUE OOOkYBgCMzgMQXUmkocoLb56rfrdWp2NE2c 26-992-422-8153 es. fluffily final dependencies sleep along the blithely even pinto beans. final deposits haggle furiously furiou 1205 106 Customer#000000106 175284.3983 3288.42 ARGENTINA xGCOEAUjUNG 11-751-989-4627 lose slyly. ironic accounts along the evenly regular theodolites wake about the special, final gifts. 1206 2 Customer#000000002 121173.0081 121.65 JORDAN XSTf4,NCwDVaWNe6tEgvwfmRchLXak 23-768-687-3665 l accounts. blithely ironic theodolites integrate boldly: caref 1207 100 Customer#000000100 113840.4294 9889.89 SAUDI ARABIA fptUABXcmkC5Wx 30-749-445-4907 was furiously fluffily quiet deposits. silent, pending requests boost against 1208 58 Customer#000000058 103790.5932 6478.46 JORDAN g9ap7Dk1Sv9fcXEWjpMYpBZIRUohi T 23-244-493-2508 ideas. ironic ideas affix furiously express, final instructions. regular excuses use quickly e 1209 94 Customer#000000094 100840.0020 5500.11 INDONESIA IfVNIN9KtkScJ9dUjK3Pg5gY1aFeaXewwf 19-953-499-8833 latelets across the bold, final requests sleep according to the fluffily bold accounts. unusual deposits amon 1210 97 Customer#000000097 99194.2317 2164.48 PERU OApyejbhJG,0Iw3j rd1M 27-588-919-5638 haggle slyly. bold, special ideas are blithely above the thinly bold theo 1211 101 Customer#000000101 90564.6478 7470.96 BRAZIL sMmL2rNeHDltovSm Y 12-514-298-3699 sleep. pending packages detect slyly ironic pack 1212 52 Customer#000000052 85250.3310 5630.28 IRAQ 7 QOqGqqSy9jfV51BC71jcHJSD0 21-186-284-5998 ic platelets use evenly even accounts. stealthy theodolites cajole furiou 1213 142 Customer#000000142 74220.1107 2209.81 INDONESIA AnJ5lxtLjioClr2khl9pb8NLxG2, 19-407-425-2584 . even, express theodolites upo 1214 59 Customer#000000059 71312.3586 3458.60 ARGENTINA zLOCP0wh92OtBihgspOGl4 11-355-584-3112 ously final packages haggle blithely after the express deposits. furiou 1215 40 Customer#000000040 69690.6972 1335.30 CANADA gOnGWAyhSV1ofv 13-652-915-8939 rges impress after the slyly ironic courts. foxes are. blithely 1216 8 Customer#000000008 67088.8134 6819.74 PERU I0B10bB0AymmC, 0PrRYBCP1yGJ8xcBPmWhl5 27-147-574-9335 among the slyly regular theodolites kindle blithely courts. carefully even theodolites haggle slyly along the ide 1217 49 Customer#000000049 66789.7960 4573.94 IRAN cNgAeX7Fqrdf7HQN9EwjUa4nxT,68L FKAxzl 20-908-631-4424 nusual foxes! fluffily pending packages maintain to the regular 1218 26 Customer#000000026 66653.8296 5182.05 RUSSIA 8ljrc5ZeMl7UciP 32-363-455-4837 c requests use furiously ironic requests. slyly ironic dependencies us 1219 77 Customer#000000077 65603.6040 1738.87 PERU 4tAE5KdMFGD4byHtXF92vx 27-269-357-4674 uffily silent requests. carefully ironic asymptotes among the ironic hockey players are carefully bli 1220 136 Customer#000000136 64024.4532 -842.39 GERMANY QoLsJ0v5C1IQbh,DS1 17-501-210-4726 ackages sleep ironic, final courts. even requests above the blithely bold requests g 1221 112 Customer#000000112 62915.4416 2953.35 ROMANIA RcfgG3bO7QeCnfjqJT1 29-233-262-8382 rmanently unusual multipliers. blithely ruthless deposits are furiously along the 1222 80 Customer#000000080 57993.8394 7383.53 ALGERIA K,vtXp8qYB 10-267-172-7101 tect among the dependencies. bold accounts engage closely even pinto beans. ca 1223 select 1224 ps_partkey, 1225 sum(ps_supplycost * ps_availqty) as value 1226 from 1227 partsupp {snapshot = 'tpch_snapshot'}, 1228 supplier {snapshot = 'tpch_snapshot'}, 1229 nation {snapshot = 'tpch_snapshot'} 1230 where 1231 ps_suppkey = s_suppkey 1232 and s_nationkey = n_nationkey 1233 and n_name = 'JAPAN' 1234 group by 1235 ps_partkey having 1236 sum(ps_supplycost * ps_availqty) > ( 1237 select 1238 sum(ps_supplycost * ps_availqty) * 0.0001000000 1239 from 1240 partsupp {snapshot = 'tpch_snapshot'}, 1241 supplier {snapshot = 'tpch_snapshot'}, 1242 nation {snapshot = 'tpch_snapshot'} 1243 where 1244 ps_suppkey = s_suppkey 1245 and s_nationkey = n_nationkey 1246 and n_name = 'JAPAN' 1247 ) 1248 order by 1249 value desc 1250 ; 1251 ps_partkey value 1252 select 1253 ps_partkey, 1254 sum(ps_supplycost * ps_availqty) as value 1255 from 1256 partsupp {snapshot = 'tpch_snapshot'}, 1257 supplier {snapshot = 'tpch_snapshot'}, 1258 nation {snapshot = 'tpch_snapshot'} 1259 where 1260 ps_suppkey = s_suppkey 1261 and s_nationkey = n_nationkey 1262 and n_name = 'JAPAN' 1263 group by 1264 ps_partkey having 1265 sum(ps_supplycost * ps_availqty) > ( 1266 select 1267 sum(ps_supplycost * ps_availqty) * 0.0001000000 1268 from 1269 partsupp {snapshot = 'tpch_snapshot'}, 1270 supplier {snapshot = 'tpch_snapshot'}, 1271 nation {snapshot = 'tpch_snapshot'} 1272 where 1273 ps_suppkey = s_suppkey 1274 and s_nationkey = n_nationkey 1275 and n_name = 'JAPAN' 1276 ) 1277 order by 1278 value desc 1279 ; 1280 ps_partkey value 1281 select 1282 ps_partkey, 1283 sum(ps_supplycost * ps_availqty) as value 1284 from 1285 partsupp {snapshot = 'tpch_snapshot'}, 1286 supplier {snapshot = 'tpch_snapshot'}, 1287 nation {snapshot = 'tpch_snapshot'} 1288 where 1289 ps_suppkey = s_suppkey 1290 and s_nationkey = n_nationkey 1291 and n_name = 'JAPAN' 1292 group by 1293 ps_partkey having 1294 sum(ps_supplycost * ps_availqty) > ( 1295 select 1296 sum(ps_supplycost * ps_availqty) * 0.0001000000 1297 from 1298 partsupp {snapshot = 'tpch_snapshot'}, 1299 supplier {snapshot = 'tpch_snapshot'}, 1300 nation {snapshot = 'tpch_snapshot'} 1301 where 1302 ps_suppkey = s_suppkey 1303 and s_nationkey = n_nationkey 1304 and n_name = 'JAPAN' 1305 ) 1306 order by 1307 value desc 1308 ; 1309 ps_partkey value 1310 select 1311 l_shipmode, 1312 sum(case 1313 when o_orderpriority = '1-URGENT' 1314 or o_orderpriority = '2-HIGH' 1315 then 1 1316 else 0 1317 end) as high_line_count, 1318 sum(case 1319 when o_orderpriority <> '1-URGENT' 1320 and o_orderpriority <> '2-HIGH' 1321 then 1 1322 else 0 1323 end) as low_line_count 1324 from 1325 orders {snapshot = 'tpch_snapshot'}, 1326 lineitem {snapshot = 'tpch_snapshot'} 1327 where 1328 o_orderkey = l_orderkey 1329 and l_shipmode in ('FOB', 'TRUCK') 1330 and l_commitdate < l_receiptdate 1331 and l_shipdate < l_commitdate 1332 and l_receiptdate >= date '1996-01-01' 1333 and l_receiptdate < date '1996-01-01' + interval '1' year 1334 group by 1335 l_shipmode 1336 order by 1337 l_shipmode 1338 ; 1339 l_shipmode high_line_count low_line_count 1340 FOB 4 5 1341 TRUCK 5 7 1342 select 1343 l_shipmode, 1344 sum(case 1345 when o_orderpriority = '1-URGENT' 1346 or o_orderpriority = '2-HIGH' 1347 then 1 1348 else 0 1349 end) as high_line_count, 1350 sum(case 1351 when o_orderpriority <> '1-URGENT' 1352 and o_orderpriority <> '2-HIGH' 1353 then 1 1354 else 0 1355 end) as low_line_count 1356 from 1357 orders {snapshot = 'tpch_snapshot'}, 1358 lineitem {snapshot = 'tpch_snapshot'} 1359 where 1360 o_orderkey = l_orderkey 1361 and l_shipmode in ('FOB', 'TRUCK') 1362 and l_commitdate < l_receiptdate 1363 and l_shipdate < l_commitdate 1364 and l_receiptdate >= date '1996-01-01' 1365 and l_receiptdate < date '1996-01-01' + interval '1' year 1366 group by 1367 l_shipmode 1368 order by 1369 l_shipmode 1370 ; 1371 l_shipmode high_line_count low_line_count 1372 FOB 4 5 1373 TRUCK 5 7 1374 select 1375 l_shipmode, 1376 sum(case 1377 when o_orderpriority = '1-URGENT' 1378 or o_orderpriority = '2-HIGH' 1379 then 1 1380 else 0 1381 end) as high_line_count, 1382 sum(case 1383 when o_orderpriority <> '1-URGENT' 1384 and o_orderpriority <> '2-HIGH' 1385 then 1 1386 else 0 1387 end) as low_line_count 1388 from 1389 orders {snapshot = 'tpch_snapshot'}, 1390 lineitem {snapshot = 'tpch_snapshot'} 1391 where 1392 o_orderkey = l_orderkey 1393 and l_shipmode in ('FOB', 'TRUCK') 1394 and l_commitdate < l_receiptdate 1395 and l_shipdate < l_commitdate 1396 and l_receiptdate >= date '1996-01-01' 1397 and l_receiptdate < date '1996-01-01' + interval '1' year 1398 group by 1399 l_shipmode 1400 order by 1401 l_shipmode 1402 ; 1403 l_shipmode high_line_count low_line_count 1404 FOB 4 5 1405 TRUCK 5 7 1406 select 1407 c_count, 1408 count(*) as custdist 1409 from 1410 ( 1411 select 1412 c_custkey, 1413 count(o_orderkey) 1414 from 1415 customer {snapshot = 'tpch_snapshot'} left outer join orders {snapshot = 'tpch_snapshot'} on 1416 c_custkey = o_custkey 1417 and o_comment not like '%pending%accounts%' 1418 group by 1419 c_custkey 1420 ) as c_orders (c_custkey, c_count) 1421 group by 1422 c_count 1423 order by 1424 custdist desc, 1425 c_count desc 1426 ; 1427 c_count custdist 1428 0 50 1429 17 8 1430 16 7 1431 22 6 1432 14 6 1433 11 6 1434 20 5 1435 12 5 1436 10 5 1437 9 5 1438 7 5 1439 23 4 1440 21 4 1441 15 4 1442 13 4 1443 4 4 1444 26 3 1445 19 3 1446 8 3 1447 6 3 1448 18 2 1449 5 2 1450 30 1 1451 29 1 1452 28 1 1453 25 1 1454 24 1 1455 3 1 1456 select 1457 c_count, 1458 count(*) as custdist 1459 from 1460 ( 1461 select 1462 c_custkey, 1463 count(o_orderkey) 1464 from 1465 customer {snapshot = 'tpch_snapshot'} left outer join orders {snapshot = 'tpch_snapshot'} on 1466 c_custkey = o_custkey 1467 and o_comment not like '%pending%accounts%' 1468 group by 1469 c_custkey 1470 ) as c_orders (c_custkey, c_count) 1471 group by 1472 c_count 1473 order by 1474 custdist desc, 1475 c_count desc 1476 ; 1477 c_count custdist 1478 0 50 1479 17 8 1480 16 7 1481 22 6 1482 14 6 1483 11 6 1484 20 5 1485 12 5 1486 10 5 1487 9 5 1488 7 5 1489 23 4 1490 21 4 1491 15 4 1492 13 4 1493 4 4 1494 26 3 1495 19 3 1496 8 3 1497 6 3 1498 18 2 1499 5 2 1500 30 1 1501 29 1 1502 28 1 1503 25 1 1504 24 1 1505 3 1 1506 select 1507 c_count, 1508 count(*) as custdist 1509 from 1510 ( 1511 select 1512 c_custkey, 1513 count(o_orderkey) 1514 from 1515 customer {snapshot = 'tpch_snapshot'} left outer join orders {snapshot = 'tpch_snapshot'} on 1516 c_custkey = o_custkey 1517 and o_comment not like '%pending%accounts%' 1518 group by 1519 c_custkey 1520 ) as c_orders (c_custkey, c_count) 1521 group by 1522 c_count 1523 order by 1524 custdist desc, 1525 c_count desc 1526 ; 1527 c_count custdist 1528 0 50 1529 17 8 1530 16 7 1531 22 6 1532 14 6 1533 11 6 1534 20 5 1535 12 5 1536 10 5 1537 9 5 1538 7 5 1539 23 4 1540 21 4 1541 15 4 1542 13 4 1543 4 4 1544 26 3 1545 19 3 1546 8 3 1547 6 3 1548 18 2 1549 5 2 1550 30 1 1551 29 1 1552 28 1 1553 25 1 1554 24 1 1555 3 1 1556 select 1557 100.00 * sum(case 1558 when p_type like 'PROMO%' 1559 then l_extendedprice * (1 - l_discount) 1560 else 0 1561 end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue 1562 from 1563 lineitem {snapshot = 'tpch_snapshot'}, 1564 part {snapshot = 'tpch_snapshot'} 1565 where 1566 l_partkey = p_partkey 1567 and l_shipdate >= date '1996-04-01' 1568 and l_shipdate < date '1996-04-01' + interval '1' month; 1569 promo_revenue 1570 17.682841639365 1571 select 1572 100.00 * sum(case 1573 when p_type like 'PROMO%' 1574 then l_extendedprice * (1 - l_discount) 1575 else 0 1576 end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue 1577 from 1578 lineitem {snapshot = 'tpch_snapshot'}, 1579 part {snapshot = 'tpch_snapshot'} 1580 where 1581 l_partkey = p_partkey 1582 and l_shipdate >= date '1996-04-01' 1583 and l_shipdate < date '1996-04-01' + interval '1' month; 1584 promo_revenue 1585 17.682841639365 1586 select 1587 100.00 * sum(case 1588 when p_type like 'PROMO%' 1589 then l_extendedprice * (1 - l_discount) 1590 else 0 1591 end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue 1592 from 1593 lineitem {snapshot = 'tpch_snapshot'}, 1594 part {snapshot = 'tpch_snapshot'} 1595 where 1596 l_partkey = p_partkey 1597 and l_shipdate >= date '1996-04-01' 1598 and l_shipdate < date '1996-04-01' + interval '1' month; 1599 promo_revenue 1600 17.682841639365 1601 with q15_revenue0 as ( 1602 select 1603 l_suppkey as supplier_no, 1604 sum(l_extendedprice * (1 - l_discount)) as total_revenue 1605 from 1606 lineitem {snapshot = 'tpch_snapshot'} 1607 where 1608 l_shipdate >= date '1995-12-01' 1609 and l_shipdate < date '1995-12-01' + interval '3' month 1610 group by 1611 l_suppkey 1612 ) 1613 select 1614 s_suppkey, 1615 s_name, 1616 s_address, 1617 s_phone, 1618 total_revenue 1619 from 1620 supplier {snapshot = 'tpch_snapshot'}, 1621 q15_revenue0 {snapshot = 'tpch_snapshot'} 1622 where 1623 s_suppkey = supplier_no 1624 and total_revenue = ( 1625 select 1626 max(total_revenue) 1627 from 1628 q15_revenue0 {snapshot = 'tpch_snapshot'} 1629 ) 1630 order by 1631 s_suppkey 1632 ; 1633 s_suppkey s_name s_address s_phone total_revenue 1634 1 Supplier#000000001 N kD4on9OM Ipw3,gf0JBoQDd7tgrzrddZ 27-918-335-1736 863528.2162 1635 with q15_revenue0 as ( 1636 select 1637 l_suppkey as supplier_no, 1638 sum(l_extendedprice * (1 - l_discount)) as total_revenue 1639 from 1640 lineitem {snapshot = 'tpch_snapshot'} 1641 where 1642 l_shipdate >= date '1995-12-01' 1643 and l_shipdate < date '1995-12-01' + interval '3' month 1644 group by 1645 l_suppkey 1646 ) 1647 select 1648 s_suppkey, 1649 s_name, 1650 s_address, 1651 s_phone, 1652 total_revenue 1653 from 1654 supplier {snapshot = 'tpch_snapshot'}, 1655 q15_revenue0 {snapshot = 'tpch_snapshot'} 1656 where 1657 s_suppkey = supplier_no 1658 and total_revenue = ( 1659 select 1660 max(total_revenue) 1661 from 1662 q15_revenue0 {snapshot = 'tpch_snapshot'} 1663 ) 1664 order by 1665 s_suppkey 1666 ; 1667 s_suppkey s_name s_address s_phone total_revenue 1668 1 Supplier#000000001 N kD4on9OM Ipw3,gf0JBoQDd7tgrzrddZ 27-918-335-1736 863528.2162 1669 with q15_revenue0 as ( 1670 select 1671 l_suppkey as supplier_no, 1672 sum(l_extendedprice * (1 - l_discount)) as total_revenue 1673 from 1674 lineitem {snapshot = 'tpch_snapshot'} 1675 where 1676 l_shipdate >= date '1995-12-01' 1677 and l_shipdate < date '1995-12-01' + interval '3' month 1678 group by 1679 l_suppkey 1680 ) 1681 select 1682 s_suppkey, 1683 s_name, 1684 s_address, 1685 s_phone, 1686 total_revenue 1687 from 1688 supplier {snapshot = 'tpch_snapshot'}, 1689 q15_revenue0 {snapshot = 'tpch_snapshot'} 1690 where 1691 s_suppkey = supplier_no 1692 and total_revenue = ( 1693 select 1694 max(total_revenue) 1695 from 1696 q15_revenue0 {snapshot = 'tpch_snapshot'} 1697 ) 1698 order by 1699 s_suppkey 1700 ; 1701 s_suppkey s_name s_address s_phone total_revenue 1702 1 Supplier#000000001 N kD4on9OM Ipw3,gf0JBoQDd7tgrzrddZ 27-918-335-1736 863528.2162 1703 select 1704 p_brand, 1705 p_type, 1706 p_size, 1707 count(distinct ps_suppkey) as supplier_cnt 1708 from 1709 partsupp {snapshot = 'tpch_snapshot'}, 1710 part {snapshot = 'tpch_snapshot'} 1711 where 1712 p_partkey = ps_partkey 1713 and p_brand <> 'Brand#35' 1714 and p_type not like 'ECONOMY BURNISHED%' 1715 and p_size in (14, 7, 21, 24, 35, 33, 2, 20) 1716 and ps_suppkey not in ( 1717 select 1718 s_suppkey 1719 from 1720 supplier {snapshot = 'tpch_snapshot'} 1721 where 1722 s_comment like '%Customer%Complaints%' 1723 ) 1724 group by 1725 p_brand, 1726 p_type, 1727 p_size 1728 order by 1729 supplier_cnt desc, 1730 p_brand, 1731 p_type, 1732 p_size 1733 ; 1734 p_brand p_type p_size supplier_cnt 1735 Brand#11 STANDARD PLATED TIN 20 4 1736 Brand#12 MEDIUM PLATED STEEL 20 4 1737 Brand#13 PROMO BURNISHED COPPER 7 4 1738 Brand#13 SMALL BRUSHED COPPER 20 4 1739 Brand#14 ECONOMY POLISHED STEEL 24 4 1740 Brand#14 LARGE ANODIZED TIN 20 4 1741 Brand#15 LARGE POLISHED TIN 20 4 1742 Brand#15 STANDARD PLATED STEEL 24 4 1743 Brand#21 SMALL BRUSHED COPPER 35 4 1744 Brand#22 STANDARD ANODIZED TIN 35 4 1745 Brand#23 ECONOMY ANODIZED TIN 7 4 1746 Brand#23 LARGE PLATED BRASS 35 4 1747 Brand#23 SMALL ANODIZED NICKEL 33 4 1748 Brand#24 LARGE ANODIZED TIN 2 4 1749 Brand#31 STANDARD PLATED STEEL 20 4 1750 Brand#32 PROMO PLATED TIN 2 4 1751 Brand#33 MEDIUM ANODIZED COPPER 35 4 1752 Brand#33 PROMO PLATED COPPER 7 4 1753 Brand#33 STANDARD BURNISHED NICKEL 7 4 1754 Brand#34 SMALL PLATED BRASS 14 4 1755 Brand#42 STANDARD POLISHED BRASS 21 4 1756 Brand#43 ECONOMY BRUSHED STEEL 20 4 1757 Brand#43 SMALL POLISHED NICKEL 2 4 1758 Brand#45 LARGE BURNISHED BRASS 14 4 1759 Brand#45 STANDARD POLISHED STEEL 24 4 1760 Brand#51 ECONOMY POLISHED STEEL 7 4 1761 Brand#52 MEDIUM PLATED STEEL 20 4 1762 Brand#52 MEDIUM POLISHED BRASS 2 4 1763 Brand#52 SMALL BURNISHED NICKEL 14 4 1764 Brand#53 MEDIUM POLISHED TIN 2 4 1765 Brand#53 PROMO BRUSHED COPPER 24 4 1766 Brand#53 PROMO POLISHED NICKEL 33 4 1767 Brand#21 MEDIUM BURNISHED STEEL 24 2 1768 Brand#22 SMALL PLATED STEEL 2 2 1769 Brand#32 MEDIUM BRUSHED STEEL 7 2 1770 Brand#42 SMALL POLISHED STEEL 35 2 1771 Brand#43 MEDIUM ANODIZED BRASS 14 2 1772 Brand#45 STANDARD BURNISHED BRASS 2 2 1773 Brand#52 PROMO POLISHED STEEL 35 1 1774 Brand#53 ECONOMY BRUSHED TIN 21 1 1775 Brand#53 STANDARD BURNISHED STEEL 7 1 1776 select 1777 p_brand, 1778 p_type, 1779 p_size, 1780 count(distinct ps_suppkey) as supplier_cnt 1781 from 1782 partsupp {snapshot = 'tpch_snapshot'}, 1783 part {snapshot = 'tpch_snapshot'} 1784 where 1785 p_partkey = ps_partkey 1786 and p_brand <> 'Brand#35' 1787 and p_type not like 'ECONOMY BURNISHED%' 1788 and p_size in (14, 7, 21, 24, 35, 33, 2, 20) 1789 and ps_suppkey not in ( 1790 select 1791 s_suppkey 1792 from 1793 supplier {snapshot = 'tpch_snapshot'} 1794 where 1795 s_comment like '%Customer%Complaints%' 1796 ) 1797 group by 1798 p_brand, 1799 p_type, 1800 p_size 1801 order by 1802 supplier_cnt desc, 1803 p_brand, 1804 p_type, 1805 p_size 1806 ; 1807 p_brand p_type p_size supplier_cnt 1808 Brand#11 STANDARD PLATED TIN 20 4 1809 Brand#12 MEDIUM PLATED STEEL 20 4 1810 Brand#13 PROMO BURNISHED COPPER 7 4 1811 Brand#13 SMALL BRUSHED COPPER 20 4 1812 Brand#14 ECONOMY POLISHED STEEL 24 4 1813 Brand#14 LARGE ANODIZED TIN 20 4 1814 Brand#15 LARGE POLISHED TIN 20 4 1815 Brand#15 STANDARD PLATED STEEL 24 4 1816 Brand#21 SMALL BRUSHED COPPER 35 4 1817 Brand#22 STANDARD ANODIZED TIN 35 4 1818 Brand#23 ECONOMY ANODIZED TIN 7 4 1819 Brand#23 LARGE PLATED BRASS 35 4 1820 Brand#23 SMALL ANODIZED NICKEL 33 4 1821 Brand#24 LARGE ANODIZED TIN 2 4 1822 Brand#31 STANDARD PLATED STEEL 20 4 1823 Brand#32 PROMO PLATED TIN 2 4 1824 Brand#33 MEDIUM ANODIZED COPPER 35 4 1825 Brand#33 PROMO PLATED COPPER 7 4 1826 Brand#33 STANDARD BURNISHED NICKEL 7 4 1827 Brand#34 SMALL PLATED BRASS 14 4 1828 Brand#42 STANDARD POLISHED BRASS 21 4 1829 Brand#43 ECONOMY BRUSHED STEEL 20 4 1830 Brand#43 SMALL POLISHED NICKEL 2 4 1831 Brand#45 LARGE BURNISHED BRASS 14 4 1832 Brand#45 STANDARD POLISHED STEEL 24 4 1833 Brand#51 ECONOMY POLISHED STEEL 7 4 1834 Brand#52 MEDIUM PLATED STEEL 20 4 1835 Brand#52 MEDIUM POLISHED BRASS 2 4 1836 Brand#52 SMALL BURNISHED NICKEL 14 4 1837 Brand#53 MEDIUM POLISHED TIN 2 4 1838 Brand#53 PROMO BRUSHED COPPER 24 4 1839 Brand#53 PROMO POLISHED NICKEL 33 4 1840 Brand#21 MEDIUM BURNISHED STEEL 24 2 1841 Brand#22 SMALL PLATED STEEL 2 2 1842 Brand#32 MEDIUM BRUSHED STEEL 7 2 1843 Brand#42 SMALL POLISHED STEEL 35 2 1844 Brand#43 MEDIUM ANODIZED BRASS 14 2 1845 Brand#45 STANDARD BURNISHED BRASS 2 2 1846 Brand#52 PROMO POLISHED STEEL 35 1 1847 Brand#53 ECONOMY BRUSHED TIN 21 1 1848 Brand#53 STANDARD BURNISHED STEEL 7 1 1849 select 1850 p_brand, 1851 p_type, 1852 p_size, 1853 count(distinct ps_suppkey) as supplier_cnt 1854 from 1855 partsupp {snapshot = 'tpch_snapshot'}, 1856 part {snapshot = 'tpch_snapshot'} 1857 where 1858 p_partkey = ps_partkey 1859 and p_brand <> 'Brand#35' 1860 and p_type not like 'ECONOMY BURNISHED%' 1861 and p_size in (14, 7, 21, 24, 35, 33, 2, 20) 1862 and ps_suppkey not in ( 1863 select 1864 s_suppkey 1865 from 1866 supplier {snapshot = 'tpch_snapshot'} 1867 where 1868 s_comment like '%Customer%Complaints%' 1869 ) 1870 group by 1871 p_brand, 1872 p_type, 1873 p_size 1874 order by 1875 supplier_cnt desc, 1876 p_brand, 1877 p_type, 1878 p_size 1879 ; 1880 p_brand p_type p_size supplier_cnt 1881 Brand#11 STANDARD PLATED TIN 20 4 1882 Brand#12 MEDIUM PLATED STEEL 20 4 1883 Brand#13 PROMO BURNISHED COPPER 7 4 1884 Brand#13 SMALL BRUSHED COPPER 20 4 1885 Brand#14 ECONOMY POLISHED STEEL 24 4 1886 Brand#14 LARGE ANODIZED TIN 20 4 1887 Brand#15 LARGE POLISHED TIN 20 4 1888 Brand#15 STANDARD PLATED STEEL 24 4 1889 Brand#21 SMALL BRUSHED COPPER 35 4 1890 Brand#22 STANDARD ANODIZED TIN 35 4 1891 Brand#23 ECONOMY ANODIZED TIN 7 4 1892 Brand#23 LARGE PLATED BRASS 35 4 1893 Brand#23 SMALL ANODIZED NICKEL 33 4 1894 Brand#24 LARGE ANODIZED TIN 2 4 1895 Brand#31 STANDARD PLATED STEEL 20 4 1896 Brand#32 PROMO PLATED TIN 2 4 1897 Brand#33 MEDIUM ANODIZED COPPER 35 4 1898 Brand#33 PROMO PLATED COPPER 7 4 1899 Brand#33 STANDARD BURNISHED NICKEL 7 4 1900 Brand#34 SMALL PLATED BRASS 14 4 1901 Brand#42 STANDARD POLISHED BRASS 21 4 1902 Brand#43 ECONOMY BRUSHED STEEL 20 4 1903 Brand#43 SMALL POLISHED NICKEL 2 4 1904 Brand#45 LARGE BURNISHED BRASS 14 4 1905 Brand#45 STANDARD POLISHED STEEL 24 4 1906 Brand#51 ECONOMY POLISHED STEEL 7 4 1907 Brand#52 MEDIUM PLATED STEEL 20 4 1908 Brand#52 MEDIUM POLISHED BRASS 2 4 1909 Brand#52 SMALL BURNISHED NICKEL 14 4 1910 Brand#53 MEDIUM POLISHED TIN 2 4 1911 Brand#53 PROMO BRUSHED COPPER 24 4 1912 Brand#53 PROMO POLISHED NICKEL 33 4 1913 Brand#21 MEDIUM BURNISHED STEEL 24 2 1914 Brand#22 SMALL PLATED STEEL 2 2 1915 Brand#32 MEDIUM BRUSHED STEEL 7 2 1916 Brand#42 SMALL POLISHED STEEL 35 2 1917 Brand#43 MEDIUM ANODIZED BRASS 14 2 1918 Brand#45 STANDARD BURNISHED BRASS 2 2 1919 Brand#52 PROMO POLISHED STEEL 35 1 1920 Brand#53 ECONOMY BRUSHED TIN 21 1 1921 Brand#53 STANDARD BURNISHED STEEL 7 1 1922 select 1923 sum(l_extendedprice) / 7.0 as avg_yearly 1924 from 1925 lineitem {snapshot = 'tpch_snapshot'}, 1926 part {snapshot = 'tpch_snapshot'} 1927 where 1928 p_partkey = l_partkey 1929 and p_brand = 'Brand#54' 1930 and p_container = 'LG BAG' 1931 and l_quantity < ( 1932 select 1933 0.2 * avg(l_quantity) 1934 from 1935 lineitem {snapshot = 'tpch_snapshot'} 1936 where 1937 l_partkey = p_partkey 1938 ); 1939 avg_yearly 1940 null 1941 select 1942 sum(l_extendedprice) / 7.0 as avg_yearly 1943 from 1944 lineitem {snapshot = 'tpch_snapshot'}, 1945 part {snapshot = 'tpch_snapshot'} 1946 where 1947 p_partkey = l_partkey 1948 and p_brand = 'Brand#54' 1949 and p_container = 'LG BAG' 1950 and l_quantity < ( 1951 select 1952 0.2 * avg(l_quantity) 1953 from 1954 lineitem {snapshot = 'tpch_snapshot'} 1955 where 1956 l_partkey = p_partkey 1957 ); 1958 avg_yearly 1959 null 1960 select 1961 sum(l_extendedprice) / 7.0 as avg_yearly 1962 from 1963 lineitem {snapshot = 'tpch_snapshot'}, 1964 part {snapshot = 'tpch_snapshot'} 1965 where 1966 p_partkey = l_partkey 1967 and p_brand = 'Brand#54' 1968 and p_container = 'LG BAG' 1969 and l_quantity < ( 1970 select 1971 0.2 * avg(l_quantity) 1972 from 1973 lineitem {snapshot = 'tpch_snapshot'} 1974 where 1975 l_partkey = p_partkey 1976 ); 1977 avg_yearly 1978 null 1979 select 1980 c_name, 1981 c_custkey, 1982 o_orderkey, 1983 o_orderdate, 1984 o_totalprice, 1985 sum(l_quantity) 1986 from 1987 customer {snapshot = 'tpch_snapshot'}, 1988 orders {snapshot = 'tpch_snapshot'}, 1989 lineitem {snapshot = 'tpch_snapshot'} 1990 where 1991 o_orderkey in ( 1992 select 1993 l_orderkey 1994 from 1995 lineitem {snapshot = 'tpch_snapshot'} 1996 group by 1997 l_orderkey having 1998 sum(l_quantity) > 314 1999 ) 2000 and c_custkey = o_custkey 2001 and o_orderkey = l_orderkey 2002 group by 2003 c_name, 2004 c_custkey, 2005 o_orderkey, 2006 o_orderdate, 2007 o_totalprice 2008 order by 2009 o_totalprice desc, 2010 o_orderdate 2011 limit 100 2012 ; 2013 c_name c_custkey o_orderkey o_orderdate o_totalprice sum(l_quantity) 2014 select 2015 c_name, 2016 c_custkey, 2017 o_orderkey, 2018 o_orderdate, 2019 o_totalprice, 2020 sum(l_quantity) 2021 from 2022 customer {snapshot = 'tpch_snapshot'}, 2023 orders {snapshot = 'tpch_snapshot'}, 2024 lineitem {snapshot = 'tpch_snapshot'} 2025 where 2026 o_orderkey in ( 2027 select 2028 l_orderkey 2029 from 2030 lineitem {snapshot = 'tpch_snapshot'} 2031 group by 2032 l_orderkey having 2033 sum(l_quantity) > 314 2034 ) 2035 and c_custkey = o_custkey 2036 and o_orderkey = l_orderkey 2037 group by 2038 c_name, 2039 c_custkey, 2040 o_orderkey, 2041 o_orderdate, 2042 o_totalprice 2043 order by 2044 o_totalprice desc, 2045 o_orderdate 2046 limit 100 2047 ; 2048 c_name c_custkey o_orderkey o_orderdate o_totalprice sum(l_quantity) 2049 select 2050 c_name, 2051 c_custkey, 2052 o_orderkey, 2053 o_orderdate, 2054 o_totalprice, 2055 sum(l_quantity) 2056 from 2057 customer {snapshot = 'tpch_snapshot'}, 2058 orders {snapshot = 'tpch_snapshot'}, 2059 lineitem {snapshot = 'tpch_snapshot'} 2060 where 2061 o_orderkey in ( 2062 select 2063 l_orderkey 2064 from 2065 lineitem {snapshot = 'tpch_snapshot'} 2066 group by 2067 l_orderkey having 2068 sum(l_quantity) > 314 2069 ) 2070 and c_custkey = o_custkey 2071 and o_orderkey = l_orderkey 2072 group by 2073 c_name, 2074 c_custkey, 2075 o_orderkey, 2076 o_orderdate, 2077 o_totalprice 2078 order by 2079 o_totalprice desc, 2080 o_orderdate 2081 limit 100 2082 ; 2083 c_name c_custkey o_orderkey o_orderdate o_totalprice sum(l_quantity) 2084 select 2085 sum(l_extendedprice* (1 - l_discount)) as revenue 2086 from 2087 lineitem {snapshot = 'tpch_snapshot'}, 2088 part {snapshot = 'tpch_snapshot'} 2089 where 2090 ( 2091 p_partkey = l_partkey 2092 and p_brand = 'Brand#23' 2093 and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') 2094 and l_quantity >= 5 and l_quantity <= 5 + 10 2095 and p_size between 1 and 5 2096 and l_shipmode in ('AIR', 'AIR REG') 2097 and l_shipinstruct = 'DELIVER IN PERSON' 2098 ) 2099 or 2100 ( 2101 p_partkey = l_partkey 2102 and p_brand = 'Brand#15' 2103 and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') 2104 and l_quantity >= 14 and l_quantity <= 14 + 10 2105 and p_size between 1 and 10 2106 and l_shipmode in ('AIR', 'AIR REG') 2107 and l_shipinstruct = 'DELIVER IN PERSON' 2108 ) 2109 or 2110 ( 2111 p_partkey = l_partkey 2112 and p_brand = 'Brand#44' 2113 and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') 2114 and l_quantity >= 28 and l_quantity <= 28 + 10 2115 and p_size between 1 and 15 2116 and l_shipmode in ('AIR', 'AIR REG') 2117 and l_shipinstruct = 'DELIVER IN PERSON' 2118 ); 2119 revenue 2120 null 2121 select 2122 sum(l_extendedprice* (1 - l_discount)) as revenue 2123 from 2124 lineitem {snapshot = 'tpch_snapshot'}, 2125 part {snapshot = 'tpch_snapshot'} 2126 where 2127 ( 2128 p_partkey = l_partkey 2129 and p_brand = 'Brand#23' 2130 and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') 2131 and l_quantity >= 5 and l_quantity <= 5 + 10 2132 and p_size between 1 and 5 2133 and l_shipmode in ('AIR', 'AIR REG') 2134 and l_shipinstruct = 'DELIVER IN PERSON' 2135 ) 2136 or 2137 ( 2138 p_partkey = l_partkey 2139 and p_brand = 'Brand#15' 2140 and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') 2141 and l_quantity >= 14 and l_quantity <= 14 + 10 2142 and p_size between 1 and 10 2143 and l_shipmode in ('AIR', 'AIR REG') 2144 and l_shipinstruct = 'DELIVER IN PERSON' 2145 ) 2146 or 2147 ( 2148 p_partkey = l_partkey 2149 and p_brand = 'Brand#44' 2150 and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') 2151 and l_quantity >= 28 and l_quantity <= 28 + 10 2152 and p_size between 1 and 15 2153 and l_shipmode in ('AIR', 'AIR REG') 2154 and l_shipinstruct = 'DELIVER IN PERSON' 2155 ); 2156 revenue 2157 null 2158 select 2159 sum(l_extendedprice* (1 - l_discount)) as revenue 2160 from 2161 lineitem {snapshot = 'tpch_snapshot'}, 2162 part {snapshot = 'tpch_snapshot'} 2163 where 2164 ( 2165 p_partkey = l_partkey 2166 and p_brand = 'Brand#23' 2167 and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') 2168 and l_quantity >= 5 and l_quantity <= 5 + 10 2169 and p_size between 1 and 5 2170 and l_shipmode in ('AIR', 'AIR REG') 2171 and l_shipinstruct = 'DELIVER IN PERSON' 2172 ) 2173 or 2174 ( 2175 p_partkey = l_partkey 2176 and p_brand = 'Brand#15' 2177 and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') 2178 and l_quantity >= 14 and l_quantity <= 14 + 10 2179 and p_size between 1 and 10 2180 and l_shipmode in ('AIR', 'AIR REG') 2181 and l_shipinstruct = 'DELIVER IN PERSON' 2182 ) 2183 or 2184 ( 2185 p_partkey = l_partkey 2186 and p_brand = 'Brand#44' 2187 and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') 2188 and l_quantity >= 28 and l_quantity <= 28 + 10 2189 and p_size between 1 and 15 2190 and l_shipmode in ('AIR', 'AIR REG') 2191 and l_shipinstruct = 'DELIVER IN PERSON' 2192 ); 2193 revenue 2194 null 2195 select 2196 s_name, 2197 s_address 2198 from 2199 supplier {snapshot = 'tpch_snapshot'}, 2200 nation {snapshot = 'tpch_snapshot'} 2201 where 2202 s_suppkey in ( 2203 select 2204 ps_suppkey 2205 from 2206 partsupp {snapshot = 'tpch_snapshot'} 2207 where 2208 ps_partkey in ( 2209 select 2210 p_partkey 2211 from 2212 part {snapshot = 'tpch_snapshot'} 2213 where 2214 p_name like 'lime%' 2215 ) 2216 and ps_availqty > ( 2217 select 2218 0.5 * sum(l_quantity) 2219 from 2220 lineitem {snapshot = 'tpch_snapshot'} 2221 where 2222 l_partkey = ps_partkey 2223 and l_suppkey = ps_suppkey 2224 and l_shipdate >= date '1993-01-01' 2225 and l_shipdate < date '1993-01-01' + interval '1' year 2226 ) 2227 ) 2228 and s_nationkey = n_nationkey 2229 and n_name = 'VIETNAM' 2230 order by s_name 2231 ; 2232 s_name s_address 2233 select 2234 s_name, 2235 s_address 2236 from 2237 supplier {snapshot = 'tpch_snapshot'}, 2238 nation {snapshot = 'tpch_snapshot'} 2239 where 2240 s_suppkey in ( 2241 select 2242 ps_suppkey 2243 from 2244 partsupp {snapshot = 'tpch_snapshot'} 2245 where 2246 ps_partkey in ( 2247 select 2248 p_partkey 2249 from 2250 part {snapshot = 'tpch_snapshot'} 2251 where 2252 p_name like 'lime%' 2253 ) 2254 and ps_availqty > ( 2255 select 2256 0.5 * sum(l_quantity) 2257 from 2258 lineitem {snapshot = 'tpch_snapshot'} 2259 where 2260 l_partkey = ps_partkey 2261 and l_suppkey = ps_suppkey 2262 and l_shipdate >= date '1993-01-01' 2263 and l_shipdate < date '1993-01-01' + interval '1' year 2264 ) 2265 ) 2266 and s_nationkey = n_nationkey 2267 and n_name = 'VIETNAM' 2268 order by s_name 2269 ; 2270 s_name s_address 2271 select 2272 s_name, 2273 s_address 2274 from 2275 supplier {snapshot = 'tpch_snapshot'}, 2276 nation {snapshot = 'tpch_snapshot'} 2277 where 2278 s_suppkey in ( 2279 select 2280 ps_suppkey 2281 from 2282 partsupp {snapshot = 'tpch_snapshot'} 2283 where 2284 ps_partkey in ( 2285 select 2286 p_partkey 2287 from 2288 part {snapshot = 'tpch_snapshot'} 2289 where 2290 p_name like 'lime%' 2291 ) 2292 and ps_availqty > ( 2293 select 2294 0.5 * sum(l_quantity) 2295 from 2296 lineitem {snapshot = 'tpch_snapshot'} 2297 where 2298 l_partkey = ps_partkey 2299 and l_suppkey = ps_suppkey 2300 and l_shipdate >= date '1993-01-01' 2301 and l_shipdate < date '1993-01-01' + interval '1' year 2302 ) 2303 ) 2304 and s_nationkey = n_nationkey 2305 and n_name = 'VIETNAM' 2306 order by s_name 2307 ; 2308 s_name s_address 2309 select 2310 s_name, 2311 count(*) as numwait 2312 from 2313 supplier {snapshot = 'tpch_snapshot'}, 2314 lineitem l1 {snapshot = 'tpch_snapshot'}, 2315 orders {snapshot = 'tpch_snapshot'}, 2316 nation {snapshot = 'tpch_snapshot'} 2317 where 2318 s_suppkey = l1.l_suppkey 2319 and o_orderkey = l1.l_orderkey 2320 and o_orderstatus = 'F' 2321 and l1.l_receiptdate > l1.l_commitdate 2322 and exists ( 2323 select 2324 * 2325 from 2326 lineitem l2 {snapshot = 'tpch_snapshot'} 2327 where 2328 l2.l_orderkey = l1.l_orderkey 2329 and l2.l_suppkey <> l1.l_suppkey 2330 ) 2331 and not exists ( 2332 select 2333 * 2334 from 2335 lineitem l3 {snapshot = 'tpch_snapshot'} 2336 where 2337 l3.l_orderkey = l1.l_orderkey 2338 and l3.l_suppkey <> l1.l_suppkey 2339 and l3.l_receiptdate > l3.l_commitdate 2340 ) 2341 and s_nationkey = n_nationkey 2342 and n_name = 'BRAZIL' 2343 group by 2344 s_name 2345 order by 2346 numwait desc, 2347 s_name 2348 limit 100 2349 ; 2350 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 6 column 14 near " {snapshot = 'tpch_snapshot'}, 2351 orders {snapshot = 'tpch_snapshot'}, 2352 nation {snapshot = 'tpch_snapshot'} 2353 where 2354 s_suppkey = l1.l_suppkey 2355 and o_orderkey = l1.l_orderkey 2356 and o_orderstatus = 'F' 2357 and l1.l_receiptdate > l1.l_commitdate 2358 and exists ( 2359 select 2360 * 2361 from 2362 lineitem l2 {snapshot = 'tpch_snapshot'} 2363 where 2364 l2.l_orderkey = l1.l_orderkey 2365 and l2.l_suppkey <> l1.l_suppkey 2366 ) 2367 and not exists ( 2368 select 2369 * 2370 from 2371 lineitem l3 {snapshot = 'tpch_snapshot'} 2372 where 2373 l3.l_orderkey = l1.l_orderkey 2374 and l3.l_suppkey <> l1.l_suppkey 2375 and l3.l_receiptdate > l3.l_commitdate 2376 ) 2377 and s_nationkey = n_nationkey 2378 and n_name = 'BRAZIL' 2379 group by 2380 s_name 2381 order by 2382 numwait desc, 2383 s_name 2384 limit 100 2385 ;"; 2386 select 2387 s_name, 2388 count(*) as numwait 2389 from 2390 supplier {snapshot = 'tpch_snapshot'}, 2391 lineitem l1 {snapshot = 'tpch_snapshot'}, 2392 orders {snapshot = 'tpch_snapshot'}, 2393 nation {snapshot = 'tpch_snapshot'} 2394 where 2395 s_suppkey = l1.l_suppkey 2396 and o_orderkey = l1.l_orderkey 2397 and o_orderstatus = 'F' 2398 and l1.l_receiptdate > l1.l_commitdate 2399 and exists ( 2400 select 2401 * 2402 from 2403 lineitem l2 {snapshot = 'tpch_snapshot'} 2404 where 2405 l2.l_orderkey = l1.l_orderkey 2406 and l2.l_suppkey <> l1.l_suppkey 2407 ) 2408 and not exists ( 2409 select 2410 * 2411 from 2412 lineitem l3 {snapshot = 'tpch_snapshot'} 2413 where 2414 l3.l_orderkey = l1.l_orderkey 2415 and l3.l_suppkey <> l1.l_suppkey 2416 and l3.l_receiptdate > l3.l_commitdate 2417 ) 2418 and s_nationkey = n_nationkey 2419 and n_name = 'BRAZIL' 2420 group by 2421 s_name 2422 order by 2423 numwait desc, 2424 s_name 2425 limit 100 2426 ; 2427 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 6 column 14 near " {snapshot = 'tpch_snapshot'}, 2428 orders {snapshot = 'tpch_snapshot'}, 2429 nation {snapshot = 'tpch_snapshot'} 2430 where 2431 s_suppkey = l1.l_suppkey 2432 and o_orderkey = l1.l_orderkey 2433 and o_orderstatus = 'F' 2434 and l1.l_receiptdate > l1.l_commitdate 2435 and exists ( 2436 select 2437 * 2438 from 2439 lineitem l2 {snapshot = 'tpch_snapshot'} 2440 where 2441 l2.l_orderkey = l1.l_orderkey 2442 and l2.l_suppkey <> l1.l_suppkey 2443 ) 2444 and not exists ( 2445 select 2446 * 2447 from 2448 lineitem l3 {snapshot = 'tpch_snapshot'} 2449 where 2450 l3.l_orderkey = l1.l_orderkey 2451 and l3.l_suppkey <> l1.l_suppkey 2452 and l3.l_receiptdate > l3.l_commitdate 2453 ) 2454 and s_nationkey = n_nationkey 2455 and n_name = 'BRAZIL' 2456 group by 2457 s_name 2458 order by 2459 numwait desc, 2460 s_name 2461 limit 100 2462 ;"; 2463 select 2464 s_name, 2465 count(*) as numwait 2466 from 2467 supplier {snapshot = 'tpch_snapshot'}, 2468 lineitem l1 {snapshot = 'tpch_snapshot'}, 2469 orders {snapshot = 'tpch_snapshot'}, 2470 nation {snapshot = 'tpch_snapshot'} 2471 where 2472 s_suppkey = l1.l_suppkey 2473 and o_orderkey = l1.l_orderkey 2474 and o_orderstatus = 'F' 2475 and l1.l_receiptdate > l1.l_commitdate 2476 and exists ( 2477 select 2478 * 2479 from 2480 lineitem l2 {snapshot = 'tpch_snapshot'} 2481 where 2482 l2.l_orderkey = l1.l_orderkey 2483 and l2.l_suppkey <> l1.l_suppkey 2484 ) 2485 and not exists ( 2486 select 2487 * 2488 from 2489 lineitem l3 {snapshot = 'tpch_snapshot'} 2490 where 2491 l3.l_orderkey = l1.l_orderkey 2492 and l3.l_suppkey <> l1.l_suppkey 2493 and l3.l_receiptdate > l3.l_commitdate 2494 ) 2495 and s_nationkey = n_nationkey 2496 and n_name = 'BRAZIL' 2497 group by 2498 s_name 2499 order by 2500 numwait desc, 2501 s_name 2502 limit 100 2503 ; 2504 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 6 column 14 near " {snapshot = 'tpch_snapshot'}, 2505 orders {snapshot = 'tpch_snapshot'}, 2506 nation {snapshot = 'tpch_snapshot'} 2507 where 2508 s_suppkey = l1.l_suppkey 2509 and o_orderkey = l1.l_orderkey 2510 and o_orderstatus = 'F' 2511 and l1.l_receiptdate > l1.l_commitdate 2512 and exists ( 2513 select 2514 * 2515 from 2516 lineitem l2 {snapshot = 'tpch_snapshot'} 2517 where 2518 l2.l_orderkey = l1.l_orderkey 2519 and l2.l_suppkey <> l1.l_suppkey 2520 ) 2521 and not exists ( 2522 select 2523 * 2524 from 2525 lineitem l3 {snapshot = 'tpch_snapshot'} 2526 where 2527 l3.l_orderkey = l1.l_orderkey 2528 and l3.l_suppkey <> l1.l_suppkey 2529 and l3.l_receiptdate > l3.l_commitdate 2530 ) 2531 and s_nationkey = n_nationkey 2532 and n_name = 'BRAZIL' 2533 group by 2534 s_name 2535 order by 2536 numwait desc, 2537 s_name 2538 limit 100 2539 ;"; 2540 drop database if exists TPCH; 2541 drop snapshot tpch_snapshot; 2542 drop snapshot tpch_cluster;