github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/benchmark/tpch/03_QUERIES/q12.sql (about)

     1  use tpch;
     2  select
     3          l_shipmode,
     4          sum(case
     5                  when o_orderpriority = '1-URGENT'
     6                          or o_orderpriority = '2-HIGH'
     7                          then 1
     8                  else 0
     9          end) as high_line_count,
    10          sum(case
    11                  when o_orderpriority <> '1-URGENT'
    12                          and o_orderpriority <> '2-HIGH'
    13                          then 1
    14                  else 0
    15          end) as low_line_count
    16  from
    17          orders,
    18          lineitem
    19  where
    20          o_orderkey = l_orderkey
    21          and l_shipmode in ('FOB', 'TRUCK')
    22          and l_commitdate < l_receiptdate
    23          and l_shipdate < l_commitdate
    24          and l_receiptdate >= date '1996-01-01'
    25          and l_receiptdate < date '1996-01-01' + interval '1' year
    26  group by
    27          l_shipmode
    28  order by
    29          l_shipmode
    30  ;