TPC-H Q12

/* using 19620718 as a seed to the RNG */
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 ('FOB', 'MAIL')
  AND l_commitdate < l_receiptdate
  AND l_shipdate < l_commitdate
  AND l_receiptdate >= CAST('1996-01-01' AS DATE)
  AND l_receiptdate < DATEADD(YEAR, 1, CAST('1996-01-01' AS DATE))
GROUP BY
  l_shipmode
ORDER BY
  l_shipmode;