SQL beginner practice problems

Question

Given two tables, orders (order_id, date, $, customer_id) and customers (ID, name)

Here's my method but I'm not sure if it's working & I'd like to know if there's faster/better way of solving these problems:

1) find out number of customers who made at least one order on date 7/9/2018

Select count (distinct customer_id)
From
(
Select customer_id from orders a 
Left join customer b
On a.customer_id = b.ID 
Group by customer_id,date
Having date = 7/9/2018
) a

2) find out number of customers who did not make an order on 7/9/2018

Select count (customer_id) from customer where customer_id not in
(
Select customer_id from orders a 
Left join customer b
On a.customer_id = b.ID 
Group by customer_id,date
Having date = 7/9/2018
)

3) find the date with most sales between 7/1 and 7/30

select date, max($)
from (
Select sum($),date from orders a 
Left join customer b
On a.customer_id = b.ID 
Group by date
Having date between 7/1 and 7/30
)

Thanks,

There is no date equal to 7 divided by 9 divided by 2018 :-( — Strawberry, Jul 10 '18 at 17:06
Dates have to be put in quotes, and in the format `YYYY-MM-DD` — Barmar, Jul 10 '18 at 17:09
Query one is wrong it should be also using `COUNT(*) >= 1` in the `HAVING` clause. — Raymond Nijland, Jul 10 '18 at 17:10
@Barmar Dates _should_ be in that format, but they don't have to be. As long as the server (through localization or whatever) can determine a date, it will read the string as a date. It may not be the expected date (1/2/2018 or 2/1/2018). — Shawn, Jul 10 '18 at 17:39

Strawberry · Answer 1 · 2018-07-10T17:18:32.390

For problem 1, a valid solution might look like this:

SELECT COUNT(DISTINCT customer_id) x
  FROM orders 
 WHERE date = '2018-09-07';  -- or is that '2018-07-09' ??

For problem 2, a valid solution might look like this:

SELECT COUNT(*) x
  FROM customer c
  LEFT
  JOIN orders o
    ON o.customer_id = x.customer_id
   AND o.date = '2018-07-09'
 WHERE o.crder_id IS NULL;

Assuming there are no ties, a valid solution to problem 3 might look like this:

SELECT date 
     , COUNT(*) sales 
  FROM orders 
 WHERE date BETWEEN '2018-07-01' AND '2018-07-30' 
 GROUP 
    BY date 
 ORDER 
    BY sales DESC 
 LIMIT 1;

Barmar · Answer 2 · 2018-07-10T21:03:10.497

1

The default format for a date in MySQL is YYYY-MM-DD, although this can be customized. You have to put quotes around it, otherwise it's treated as an arithmetic expression.

And none of your queries need to join with the customer table. The customer ID is already in the orders table, and you're not returning any info about the customers (like the name or address), you're just counting them.

1) You don't need the subquery or grouping.

SELECT COUNT(DISTINCT customer_id)
FROM orders
WHERE date = '2018-07-09'

2) Again, you don't need GROUP BY in the subquery. There's also a better pattern than NOT IN to get the count of non-matching rows.

SELECT COUNT(*)
FROM customer AS c
LEFT JOIN order AS o on c.id = o.customer_id AND o.date = '2018-07-09'
WHERE o.id IS NULL

See Return row only if value doesn't exist for various patterns to do this.

3) You can't use MAX($) in the outer query because the inner query doesn't return a column with that name. But even if you fix that, it still won't work, because the date column won't necessarily come from the same row that has the maximum. See SQL select only rows with max value on a column for more explanation of this.

You don't need a subquery at all. Use a query that returns the total sales for each day, then use ORDER BY to get the highest one.

SELECT date, SUM($) AS total_sales
FROM orders
WHERE date BETWEEN '2018-07-01' AND '2017-07-30'
GROUP BY date
ORDER BY total_sales DESC
LIMIT 1

If "most sales" is supposed to mean "most number of sales", replace SUM($) with COUNT(*).

edited Jul 10 '18 at 21:03

answered Jul 10 '18 at 17:18

Barmar

741,623
53
500
612

1

SUM($) would (presumably) give the highest value extracted, which is not necessarily the same thing as 'most sales' – Strawberry Jul 10 '18 at 17:20
@Strawberry It depends on whether he means most number of sales or most value of sales. I've chosen to use the same interpretation as the OP. – Barmar Jul 10 '18 at 17:21
@barmar would this work then select date, max(total_sales) from ( Select sum($) as total_sales,date from orders a Left join customer b On a.customer_id = b.ID Group by date Having date between 7/1 and 7/30 ) – SQL Learner Jul 10 '18 at 20:49
if using a subquery instead of order/limit – SQL Learner Jul 10 '18 at 20:50
@SQLLearner No. When you use an aggregation function like `max()` there's no guarantee that the non-aggregated column will come from the same row that has the max. – Barmar Jul 10 '18 at 21:00
@BARMAR i know the solution posted work better, but my code for 1 and 2 should work as well? right? – SQL Learner Jul 10 '18 at 21:53
Yes, they'll work as long as you fix the date syntax. But they're doing lots of unnecessary work. – Barmar Jul 11 '18 at 04:22

SQL beginner practice problems

2 Answers2