Sample Output NATION YEAR SUM_PROFIT ALGERIA 1998 31342867.24 Returned Item Reporting Query (Q10) The query identifies customers who might be having problems with the parts that are shipped to them. Business question The Returned Item Reporting finds the top 20 customers, in terms of their effect on lost revenue for a given quarter, who have returned parts. The query considers only parts that were ordered in the specified quarter. The query lists the customer's name, address, nation, phone number, account balance, comment information and revenue lost. The customers are listed in descending order of lost revenue. Revenue lost is defined as sum(l_extendedprice*(1-l_discount)) for all qualifying lineitems. Functional Query Definition Return the first 20 selected rows select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= date '[DATE]' and o_orderdate < date '[DATE]' + interval '3' month and l_returnflag = 'R' and c_nationkey = n_nationkey by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc; Substitution Parameters
Sample Output NATION YEAR SUM_PROFIT ALGERIA 1998 31342867.24 Returned Item Reporting Query (Q10) The query identifies customers who might be having problems with the parts that are shipped to them. Business question The Returned Item Reporting finds the top 20 customers, in terms of their effect on lost revenue for a given quarter, who have returned parts. The query considers only parts that were ordered in the specified quarter. The query lists the customer's name, address, nation, phone number, account balance, comment information and revenue lost. The customers are listed in descending order of lost revenue. Revenue lost is defined as sum(l_extendedprice*(1-l_discount)) for all qualifying lineitems. Functional Query Definition Return the first 20 selected rows select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= date '[DATE]' and o_orderdate < date '[DATE]' + interval '3' month and l_returnflag = 'R' and c_nationkey = n_nationkey by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc; Substitution Parameters