CODEWITHSUNDEEP
mysqlsqljoin
DDDD
DDDD

Reputation: 3940

Query date column from join table

Here is the schema:

Customer (Customer_ID, Name, Address, Phone),
Porder (Customer_ID, Pizza_ID, Quantity, Order_Date), 
Pizza (Pizza_ID, Name, Price).

I want to get all customers that ordered a pizza in the last 30 days, based on the Order_Date & who spent the most money in the last 30 days. Can these be combined into one?

Here is what I am trying and I am not sure about DATEDIFF or how the query would calculate the total money.

SELECT customer.customer_ID, customer.name FROM customer 
JOIN porder ON customer.customer_ID = porder.customer_ID
GROUP BY customer.customer_ID, customer.name
WHERE DATEDIFF(porder.porder_date,getdate()) between 0 and 30

Who spent the most money last 30 days?

SELECT porder.customer_ID, porder.pizza_id, porder.quantity FROM order 
JOIN pizza ON porder.pizza_ID = pizza.pizza_ID
GROUP BY porder.customer_ID
WHERE MAX((porder.quantity * pizza.price)) && DATEDIFF(porder.porder_date,getdate()) between 0 and 30

Upvotes: 0

Views: 44

Answers (2)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

Remember that functions are blackboxes to query optimizer, so you better make the query fit the index, and not the other way around.

WHERE DATEDIFF(order.order_date,getdate()) between 0 and 30

can be rewritten, so that the query would use plain index on order_date

WHERE order.order_date >= CURRENT_DATE - INTERVAL 30 DAY

Who spent the most money in the last 30 days

SELECT 
  o.customer_id, SUM(p.price * o.quantity)
FROM 
  order o
  INNER JOIN pizza p 
    ON o.pizza_id = p.pizza_id
WHERE 
  order_date >= CURRENT_DATE - INTERVAL 30 DAY
GROUP BY o.customer_id
ORDER BY SUM(p.price * o.quantity) DESC
LIMIT 1

Upvotes: 2

Strawberry
Strawberry

Reputation: 33935

Something to think about once you've sorted out your tables, and separated order details from orders.

SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

SELECT x.*
     , IF(x.i = y.maxi,1,0) is_biggest 
  FROM ints x 
  LEFT 
  JOIN (SELECT MAX(i) maxi FROM ints) y 
    ON y.maxi = x.i;
+---+------------+
| i | is_biggest |
+---+------------+
| 0 |          0 |
| 1 |          0 |
| 2 |          0 |
| 3 |          0 |
| 4 |          0 |
| 5 |          0 |
| 6 |          0 |
| 7 |          0 |
| 8 |          0 |
| 9 |          1 |
+---+------------+

Upvotes: 0

Related Questions