How to apply joins and get the result?

Transact-SQL
#1

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

  1. What are the names of customers who ordered more than 3 books in the last 3 months?
  2. Which category had the highest sales in the last 60 days?
  3. Which is the most expensive product that has at least 5 sales in the last year?
#2
  1. 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
#3

Answer is helpful. Will you please post ans to 2 and 3?

#4

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.

#5

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

#6

Can we assume highest number of sales (quantity)?

#7

Hi

Does this help

:thinking:
:thinking:

  1. 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