calculate rate of attribute by id sql

Question

First, this is my table schema:

order_id, product_id, add_to_cart_order, reordered

My problem is calculate the rate of reordered by product. So we can see "add_to_cart_order" is useless, I don't know for "order_id". "reordered" can be have '1' and '0' value.

For the moment, I can have the count of "reordored" by product_id with

SELECT 
    product_id, 
    COUNT(reordered) 
FROM 
    train 
WHERE
    reordered = '1' 
GROUP BY 
    product_id;

and the count of occurrence of a product with

SELECT 
    product_id, COUNT(*) 
FROM
    train  
GROUP BY 
    product_id;

I tried

SELECT 
    t1.product_id, 
    COUNT(t1.product_id) / (SELECT COUNT(reordered) 
                            FROM train t2 
                            WHERE t2.reordered = '1' 
                              AND t1.product_id = t2.product_id 
                            GROUP BY product_id) 
FROM
    train t1 
GROUP BY
    t1.product_id;

But it takes too much time (I don't know if it's the right request because I don't have results yet)

Answer 1

Is this what you are looking for?

SELECT Product_id, SUM(CASE WHEN reordered=1 THEN 1 ELSE 0 END ) /
COUNT(*) AS ReorderedRate
FROM
train
GROUP BY Product_id