SQL - create table via join and show who is not in it in one table-CodePudding

I have three tables:

customer (id, name, email)
product (id, product_category, material, price, purchase_id)
purchase (id, purchase_date, customer_id)

My task: show the name of the customer and the number of purchases they made - this column should be called purchase_count. Sort the results by number of purchases, starting with the customer with the most purchases.

Please note that some customers may not have made any purchases yet. In such a situation, you should display 0 for the number of purchases.

I am trying to create a view such as:

name, purchase_count

This is my code so far. I don't think its correct:

WITH CLIENTS_BUYS AS
(
    SELECT -- (NULL FOR THE CLIENTS WHO HAVE NOT MADE PURCHASE SHOW AS '0' HAVE NOT BEEN SHOWN IN THIS QUERY)
        CU.name, -- SO I TRIED TO USE 'CTE'
        CU.id,
        COUNT(CASE WHEN PU.id IS NOT NULL THEN PU.id ELSE CU.id END) AS PURCHASE_COUNT
    FROM 
        purchase PU
    JOIN 
        customer CU ON CU.id = PU.customer_id
    JOIN 
        product PR ON PR.purchase_id = PU.id
    WHERE 
        customer_id IS NOT NULL 
        OR customer_id IS NULL
    GROUP BY 
        CU.name, CU.id
),
CLIENTS_NOT_BUYS AS 
(
    SELECT 
        CU.name,
        CU.id,
        COUNT(CASE WHEN PU.id IS NULL THEN PU.id ELSE CU.id END) AS PURCHASE_COUNT
    FROM 
        purchase PU 
    JOIN 
        customer CU ON CU.id = PU.customer_id
    JOIN 
        product PR ON PR.purchase_id = PU.id
    WHERE 
        CU.id IN (SELECT customer_id 
                  WHERE CU.id != PU.customer_id)
    GROUP BY 
        CU.name, CU.id
)
SELECT
    name,
    PURCHASE_COUNT
FROM 
    CLIENTS_NOT_BUYS
JOIN 
    CLIENTS_BUYS ON CLIENTS_BUYS.id = CLIENTS_NOT_BUYS.id
GROUP BY 
    name
ORDER BY 
    PURCHASE_COUNT DESC

One issue is purchase_id in the product has NULL for one or some customers. But via join the customers who has NULL in purchase_id have not been presented in the first query.

So, I tried to use CTE but it seems not a working query and I think that I am doing it too difficult because there should be an easier way than I do. Also tried LEFT JOIN but it didn't work.

In the last part select. where I a, trying to join CLIENTS_NOT_BUYS and CLIENTS_BUYS => I get an error

Errors near name and PURCHASE_COUNT
Ambiguous column name

I don't know how to make it work...

This is the result of first select inside the CTE CLIENTS_BUYS without ORDER BY:

name            id  PURCHASE_COUNT
-----------------------------------
Alba Gomez       5      1
Amira Palmer     3      2
Anna Smith       7      2
Charlee Freeman  1      5
Christina Rivas  2      1
Michael Doe      6      2

But I don't know how to show others with NULL in purchase_id and add them at the bottom with '0' in PURCHASE_COUNT to continue the table

CodePudding user response：

You need a FULL OUTER JOIN to get all values from all name in buys and not buys

WITH CLIENTS_BUYS AS(
SELECT -- (NULL FOR THE CLIENTS WHO HAVE NOT MADE PURCHASE SHOW AS '0' HAVE NOT BEEN SHOWN IN THIS QUERY)
    CU.name, -- SO I TRIED TO USE 'CTE'
    CU.id,
    COUNT(CASE WHEN PU.id IS NOT NULL THEN PU.id ELSE CU.id END) AS PURCHASE_COUNT
FROM purchase PU
    JOIN customer CU
    ON CU.id = PU.customer_id
    JOIN product PR
    ON PR.purchase_id = PU.id
WHERE customer_id IS NOT NULL 
    OR customer_id IS NULL
GROUP BY CU.name,CU.id
),
CLIENTS_NOT_BUYS AS (
SELECT 
    CU.name,
    CU.id,
    COUNT(CASE WHEN PU.id IS NULL THEN PU.id ELSE CU.id END) AS PURCHASE_COUNT
FROM purchase PU
    JOIN customer CU
    ON CU.id = PU.customer_id
    JOIN product PR
    ON PR.purchase_id = PU.id
WHERE CU.id IN (
    SELECT customer_id 
    WHERE CU.id != PU.customer_id
    )
GROUP BY CU.name,CU.id
)
SELECT
    COALESCE(CLIENTS_NOT_BUYS.name,CLIENTS_NOT_BUYS.name) name,
    CLIENTS_BUYS.PURCHASE_COUNT AS BUYS_PURCHASE_COUNT,
    CLIENTS_NOT_BUYS.PURCHASE_COUNT  AS NOT_BUYS_PURCHASE_COUNT
FROM CLIENTS_NOT_BUYS
    FULL OUTER JOIN CLIENTS_BUYS
    ON CLIENTS_BUYS.id = CLIENTS_NOT_BUYS.id
ORDER BY CLIENTS_BUYS.PURCHASE_COUNT DESC

CodePudding user response：

I think a left join should work here

SELECT name, case when PURCHASE_COUNT is null then 0 else PURCHASE_COUNT  end as PURCHASE_COUNT 
from(
SELECT CU.name
       COUNT(distinct PU.id) AS PURCHASE_COUNT
FROM customer CU
LEFT JOIN   purchase PU 
on CU.id = PU.customer_id
GROUP BY CU.name, CU.id
) group by name order by 2 desc

If the customer doesn't have any purchase, the left should give you an empty PU.id. I think PU.id is a unique id for each purchase. Like transaction ID.