wdbd_lab8

select firstname, lastname, count (*) from customer
group by firstname, lastname
order by count (*) desc;

select c.customerkey, c.lastname, c.firstname, count (*)
from customer c left join orderheader oh on c.customerkey = oh.customerkey
group by c.customerkey, c.lastname, c.firstname
order by c.customerkey


select firstname, lastname, count (*) from customer
group by firstname, lastname
order by count (*) desc;

select c.customerkey, c.lastname, c.firstname,
count (oh.orderkey),
count (distinct oh.paymentmethodkey)
from customer c left join orderheader oh on c.customerkey = oh.customerkey
group by c.customerkey, c.lastname, c.firstname
having count (distinct oh.paymentmethodkey) > 3
order by count(oh.orderkey) desc


select c.customerkey, c.firstname, c.lastname, count(oh.customerkey)
from orderheader oh
right join customer c on oh.customerkey = c.customerkey
and to_char(oh.orderdate, 'yyyy') = '2019'
group by c.customerkey, c.firstname, c.lastname
order by count(oh.customerkey)


select oc.channelname "Channel", oc.channelkey "Key", c.countryname "Countryname", count(*) "#Orders" from orderheader oh
left join orderchannel oc on oh.channelkey = oc.channelkey
right join country c on oh.countrykey = c.countrykey
group by oc.channelkey, oc.channelname, c.countryname
order by count(*);


select c.countryname "Country", oc.channelname "Name", count(oh.orderkey) "#Order" from
country c cross join orderchannel oc
left join orderheader oh
on oh.channelkey = oc.channelkey
group by c.countryname, oc.channelname
order by count(oh.orderkey)


select sum(od.transactionprice * od.quantity) "Total Orders Value" from
orderdetail od
inner join orderheader oh on od.orderkey = oh.orderkey
group by to_char(oh.orderdate, 'yyyy')


select to_char(oh.orderdate, 'yyyy'), c.countryname "Name", sum(od.transactionprice * od.quantity) "Total Orders Value" from
orderdetail od
inner join orderheader oh on od.orderkey = oh.orderkey
inner join country c on c.countrykey = oh.countrykey
group by to_char(oh.orderdate, 'yyyy'), rollup(c.countryname)


select avg((oh.deliverydate - oh.orderdate)), c.countryname, to_char(oh.deliverydate, 'yyyy') "AVG Delivery Time"
from orderheader oh
inner join country c on oh.countrykey = c.countrykey
group by to_char(oh.deliverydate, 'yyyy'), c.countryname