ORDERS PRODUCTS CUST
order_id product_id cust_id
Cust_id product_name cust_name
Order_date product_category cust_address
product_id product_price
- What are the names of customers who ordered more than 3 books in the last 3 months?
- Which category had the highest sales in the last 60 days?
- Which is the most expensive product that has at least 5 sales in the last year?
- What are the names of customers who ordered more than 3 books in the last 3 months?
select cust_name from CUST c
inner join ORDERS o
on c.cust_id = o.cust_id
inner join PRODUCTS p
on o.product_id = o.product_id
where p.product_category = 'books'
and Order_date >= DATEADD(MONTH, -3, GETDATE())
having count(*)>3
1 Like
Answer is helpful. Will you please post ans to 2 and 3?
sampadkm, this is obviously a school assignment. You will learn more if you wrestle with it yourself for a while instead of being fed complete answers. Then if you post something on which you got stuck, someone may point you in the right direction.
For remaining 2 questions some columns are missing in order to calculate sales such as unit price,discount,quantity. For your reference i can text the sample code as below:
SELECT productid, SUM(UnitPrice * (1 - Discount) * Quantity) as TotalSales
FROM [Order Details]
GROUP BY ProductID
This query returns total sales for each product. In order to know which product has more sales you need to write the code as
select top 1 * from(
SELECT productid, SUM(UnitPrice * (1 - Discount) * Quantity) as TotalSales
FROM [Order Details]
GROUP BY ProductID)
order by TotalSales desc
Can we assume highest number of sales (quantity)?
Hi
Does this help


- Which category had the highest sales in the last 60 days?
Select category, max(sales)
Where somedatecolumn between
Somedatecolumn and dateadd(d,-60, somedatecolumn)
Group by category