TPC-D Q5

SELECT

N_NAME, SUM(L_EXTENDEDPRICE * ( 1 - L_DISCOUNT)) AS REVENUE

FROM

CUSTOMER, ORDER, LINEITEM, SUPPLIER, NATION, REGION

WHERE

C_CUSTKEY = O_CUSTKEY

AND O_ORDERKEY = L_ORDERKEY

AND L_SUPPKEY = S_SUPPKEY

AND C_NATIONKEY = S_NATIONKEY

AND S_NATIONKEY = N_NATIONKEY

AND N_REGIONKEY = R_REGIONKEY

AND R_NAME = '[region]'

AND O_ORDERDATE >= DATE '[date]'

AND O_ORDERDATE < DATE '[date]' + INTERVAL 1 YEAR

GROUP BY

N_NAME

ORDER BY

REVENUE DESC;