-- $Id: tpch03.sql 2657 2007-06-12 16:08:15Z rdempsey $
-- TPC-H/TPC-R Shipping Priority Query (Q3)
-- Functional Query Definition
-- Approved February 1998

define 1 = BUILDING
define 2 = 1995-03-15

select
        l_orderkey,
        sum(l_extendedprice * (1 - l_discount)) as revenue,
        o_orderdate,
        o_shippriority
from
        customer,
        orders,
        lineitem
where
        c_mktsegment = '&1'
        and c_custkey = o_custkey
        and l_orderkey = o_orderkey
        and o_orderdate < date '&2'
        and l_shipdate > date '&2'
group by
        l_orderkey,
        o_orderdate,
        o_shippriority
order by
        revenue desc,
        o_orderdate;