SELECT date_trunc('year', l_receiptdate) as receipt_year, o_orderpriority, 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 demo_local.orders_mergekey join demo_local.lineitem_mergekey on o_orderkey = l_orderkey WHERE l_shipmode in ('AIR', 'SHIP') AND l_commitdate < l_receiptdate AND l_shipdate < l_commitdate AND l_receiptdate >= date '1990-01-01' AND l_receiptdate < date '2000-01-01' + interval '1' year GROUP BY  receipt_year, o_orderpriority, l_shipmode ORDER BY receipt_year,  l_shipmode;
