SQL Business metrics exercise - Reorder rate -11/12


#1

Does anyone feel like the code should be like this:

select name, round(1.0 * count(order_id) /
count(distinct orders.delivered_to), 2) as reorder_rate
from order_items
join orders on
orders.id = order_items.order_id
group by 1
order by 2 desc;

Instead

The orginal code from GET CODE is:

select name, round(1.0 * count(distinct order_id) /
count(distinct orders.delivered_to), 2) as reorder_rate
from order_items
join orders on
orders.id = order_items.order_id
group by 1
order by 2 desc;

Do we really need DISTINCT clause for order_id? It filters out repeating orders, so does not give you exact number of an item ordered. But we do want to count repeating orders don't we?

For the second bit, we do need DISTINCT clause before orders.delivered_to as we do not want to count the customer reordered an item multiple times.

If you run the codes below just for order_items table you will see two different results:

Without Distinct

select name, count(order_id)
from order_items
group by 1
order by 1;

With Distinct:

select name, count( distinct order_id)
from order_items
group by 1
order by 1;

Can anyone comment on this topic please?


#2

I have the very same doubt!!! Sorry for not been really helpful with my post mashuk!!


#3

good point. i agree with you


#4

The reorder_rate is defined as " the ratio of the total number of orders to the number of people making those orders. "
I think a distinct order_id represents an order. I mean, one order_id may appear several times related to different names of product. So here we should use count(distinct order_id) as the total number of orders.