To get used to the extraction figure, perform the following query but only selec

Question

To get used to the extraction figure, perform the following query but only select records where the Shipdate year is greater than 2013.

SELECT shipdate, COUNT(*)
FROM dw.orderline
GROUP BY shipdate
ORDER BY 1;

************************************************************************

Your boss wants to look at data only from the last quarter of each year. All years are to be selected. Perform the following query but only select data in the last 3 months of each year (OCT, NOV, and DEC).

SELECT Extract(YEAR from orderdate) as year,
Extract(MONTH from orderdate) as month,
COUNT(*) as numorders, COUNT(DISTINCT customerid) as numcust,
SUM(totalprice) as totspend,
SUM(totalprice)*1.0/COUNT(*) as avgordersize,
SUM(totalprice)*1.0/COUNT(DISTINCT customerid) as avgcustorder
FROM dw.orders o
GROUP BY Extract(YEAR from orderdate),
Extract(MONTH from orderdate)
ORDER BY 1, 2;

**************************************************************************

While going through some of your data, you see that there are some outliers in shipdate that have badly affected your results. Use the following as the basis for your query but strip off all shipdates less than 0 or greater than 365 days difference from the order date.

SELECT ol.shipdate - o.orderdate as days, COUNT(*) as numol
FROM dw.orders o JOIN dw.orderline ol ON o.orderid = ol.orderid
GROUP by (ol.shipdate - o.orderdate)
ORDER BY 1;

Explanation / Answer

To get used to the extraction figure, perform the following query but only select records where the Shipdate year is greater than 2013.

SELECT shipdate, COUNT(*)
FROM   dw.orderline
WHERE Extract(YEAR from orderdate) > 2013;
GROUP BY shipdate
ORDER BY 1;

************************************************************************

Your boss wants to look at data only from the last quarter of each year. All years are to be selected.
Perform the following query but only select data in the last 3 months of each year (OCT, NOV, and DEC).

SELECT Extract(YEAR from orderdate) as year,
Extract(MONTH from orderdate) as month,
COUNT(*) as numorders, COUNT(DISTINCT customerid) as numcust,
SUM(totalprice) as totspend,
SUM(totalprice)*1.0/COUNT(*) as avgordersize,
SUM(totalprice)*1.0/COUNT(DISTINCT customerid) as avgcustorder
FROM dw.orders o
WHERE Extract(MONTH from orderdate) > 9
GROUP BY Extract(YEAR from orderdate),
Extract(MONTH from orderdate)
ORDER BY 1, 2;

**************************************************************************

While going through some of your data, you see that there are some outliers in shipdate that have badly affected your results.
Use the following as the basis for your query but strip off all shipdates less than 0 or greater than 365 days difference from the order date.

SELECT a.days, a.numol
FROM   (SELECT ol.shipdate - o.orderdate as days, COUNT(*) as numol
   FROM dw.orders o JOIN dw.orderline ol ON o.orderid = ol.orderid
   GROUP by (ol.shipdate - o.orderdate)) a
WHERE a.days BETWEEN 0 AND 365
ORDER BY 1;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.