1. List the top 5 best-selling products based on the total sales amount in the d
ID: 3797722 • 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.
Explanation / Answer
#1
select PRODUCT_NBR, sum(TOTAL_LINE_AMT) as TOTAL_AMOUNT
from PRODUCTS
group by PRODUCT_NBR
order by TOTAL_AMOUNT desc limit 5;
#2
select * from PRODUCTS
where PRODUCT_NBR like '%35' and TOTAL_LINE_AMT > 50 and ordered_through = 'OnAir'
order by TOTAL_LINE_AMT;
#3
select ORDER_LINE_NBR, count(ORDER) as cnt,
RANK() OVER (PARTITION BY ORDER_LINE_NBR ORDER BY cnt DESC) AS Rank
from PRODUCTS
group by ORDER_LINE_NBR;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.