modules/sql-engine/src/testFixtures/resources/tpch/q12.sql

-- noinspection SqlDialectInspectionForFile
-- noinspection SqlNoDataSourceInspectionForFile

SELECT
    l_shipmode,
    sum(CASE
            WHEN o_orderpriority = '1-URGENT'
                OR o_orderpriority = '2-HIGH'
                THEN 1
            ELSE 0
        END) AS high_line_count,
    sum(CASE
            WHEN o_orderpriority <> '1-URGENT'
                AND o_orderpriority <> '2-HIGH'
                THEN 1
            ELSE 0
        END) AS low_line_count
FROM
    orders,
    lineitem
WHERE
        o_orderkey = l_orderkey
  AND l_shipmode IN ('MAIL', 'SHIP')
  AND l_commitdate < l_receiptdate
  AND l_shipdate < l_commitdate
  AND l_receiptdate >= DATE '1994-01-01'
  AND l_receiptdate < DATE '1994-01-01' + INTERVAL '1' YEAR
GROUP BY
    l_shipmode
ORDER BY
    l_shipmode