4. Customers can order from QVC either through the QVC website (QVC.com) or by c

Question

4. Customers can order from QVC either through the QVC website (QVC.com) or by calling a phone number when am infomercial is aired (Air). The column named ORDER_PLATFORM captures this information. Also, customers can place order anytime in a 24-hours day. The ORDER_TIME column captures when the order was placed. TOTAL_LINE_AMT shows the dollar amount for each order. Write a query to show the total dollar amount that has been ordered grouped by hours (0-23), ranked by total dollar amount when the order platform is QVC.com. This query can show online customer ordering behaviors. Use the SQL Character or Text (String Processing) function to handle the ORDER_TIME column.

5. It would be interesting as well to identify monthly trend in customer ordering. Write a query to show both total order count and total dollar amount for each month in 2015. Sort the results based on the order count. Use the Oracle DATE processing functions that you can find on the Internet to process the data information in this query.

Explanation / Answer

Query 1:
-- suppose there is a table "orderdetail" have columns:
   ----ORDER_PLATFORM captures this information
   ----ORDER_TIME column captures when the order was placed
   ----TOTAL_LINE_AMT shows the dollar amount for each order

SELECT sum(TOTAL_LINE_AMT) AS "total dollar amount"
FROM orderdetail
WHERE ORDER_PLATFORM = "QVC.com"
GROUP BY EXTRACT(hour FROM ORDER_TIME)
ORDER BY "TOTAL_LINE_AMT" ASC;


Query 2:

SELECT sum(TOTAL_LINE_AMT) AS "total dollar amount",
COUNT(ORDER_TIME) "No. of Orders"
WHERE EXTRACT(YEAR FROM
   TO_DATE(ORDER_TIME, 'DD-MON-RR')) = 2015
GROUP BY EXTRACT(Month FROM ORDER_TIME)
ORDER BY "No. of Orders" ASC;

Related Questions

Navigate

• Browse (All)
• Browse 4
• Subjects

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