How to check if a customer has made a warranty claim and a product sale?

user_3879598 0
Viewed 61

I would like to know how to check in PostgreSQL to see if whether or not a customer has made both a warranty claim and a product sale.

For example , I have a customer like this.

CustomerID OrderID Product Name
ABC123 1426457 PRODUCT A
ABC123 1426458 WARRANTY CLAIM

So for this customer. I want to set a column to true that he/she contains both a product sale and a warranty claim.

Whereas a customer like this

CustomerID OrderID Product Name
DEF456 1426990 PRODUCT A
DEF456 1427500 PRODUCT B

I want to set a column to false becasue he/she does not have a warranty claim.

It does not matter if a customer has made more than two orders. As long as there is a warranty claim and a product sale then I want to set it to true.

Is this possible? So far I have tried various solutions using SQL but to no avail.

Expected Output :

CustomerID OrderID Product Name Boolean
ABC123 1426457 PRODUCT A TRUE
ABC123 1426458 WARRANTY CLAIM TRUE
CustomerID OrderID Product Name Boolean
DEF456 1426990 PRODUCT A FALSE
DEF456 1427500 PRODUCT B FALSE
postgresql sql
1 Answers

demos:db<>fiddle

SELECT
    "CustomerID",
    bool_or("Product Name" = 'WARRANTY CLAIM')
        AND
    bool_or("Product Name" != 'WARRANTY CLAIM')
FROM mytable
GROUP BY "CustomerID"

With aggregating using bool_or() on a CustomerID with checking whether a Product Name is WARRENTY CLAIM or not you return your expected result as aggregate. So, one check should be if there is at least a WARRENT CLAIM and at least one without.

If you want to get this on every record, you can do this using the bool_or() window function:

SELECT
    *,
    bool_or("Product Name" = 'WARRANTY CLAIM') OVER (PARTITION BY "CustomerID")
        AND
    bool_or("Product Name" != 'WARRANTY CLAIM') OVER (PARTITION BY "CustomerID")
FROM mytable
user_39842210
Related