USE tpch1;
select r_regionkey, r_name,
sum(ordercnt2) ordercnt3,
round(sum(sumcusttotal2)/100,2) custtotal3,
min(minorderdate2) minorderdate3,
max(maxorderdate2) maxorderdate3
from region,
(select n_nationkey,
n_regionkey,
sum(ordercnt1) ordercnt2,
sum(totalprice1) sumcusttotal2,
min(minorderdate1) minorderdate2,
max(maxorderdate1) maxorderdate2
from customer, nation,
(select o_custkey,
count(*) ordercnt1,
sum(o_totalprice)*100 totalprice1,
min(o_orderdate) minorderdate1,
max(o_orderdate) maxorderdate1
from orders group by o_custkey) order1
where c_custkey = o_custkey
and c_nationkey = n_nationkey
group by n_nationkey, n_regionkey
) nation1
where r_regionkey = n_regionkey
group by r_regionkey, r_name
order by r_regionkey, r_name;
r_regionkey	r_name	ordercnt3	custtotal3	minorderdate3	maxorderdate3
0	AFRICA	298994	45230223902.22	1992-01-01	1998-08-02
1	AMERICA	299103	45306943255.21	1992-01-01	1998-08-02
2	ASIA	301740	45613415042.56	1992-01-01	1998-08-02
3	EUROPE	303286	45793265459.71	1992-01-01	1998-08-02
4	MIDDLE EAST	296877	44885458787.76	1992-01-01	1998-08-02