1. List the top 5 best-selling products based on the total sales amount in the d
ID: 3787812 • Letter: 1
Question
1. List the top 5 best-selling products based on the total sales amount in the data set. Show both the product number (PRODUCT_NBR) and the SUM of total dollar amount (TOTAL_LINE_AMT).
2. Write a query to filter your dataset by identifying the product number ending with 35, ordered through “On Air”, and TOTAL_LINE_AMT greater than 50. Sort the result by TOTAL_LINE_AMT).
3.ORDER_LINE_NBR is a column showing how many Line Items are included in a particular order. Write a query to find out how many order contains more than 6 (6 or more) line items. Group the count by ORDER_LINE_NBR. Rank the result dissentingly based on the count.
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
1) Assume table name is order_items
SELECT PRODUCT_NBR, TOTAL_LINE_AMT
2) SELECT PRODUCT_NBR
FROM order_items
where TOTAL_LINE_AMT >50 AND On Air=TRUE
ORDER BY TOTAL_LINE_AMT DESC
IN ( SELECT PRODUCT_NBR FROM order_items
where PRODUCT_NBR REGEX $ 35 )
3)SELECT PRODUCT_NBR
FROM order_items
WHERE ORDER_LINE_NBR >=6
Group BY ORDER_LINE_NBR
ORDER BY ORDER_LINE_NBR
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.