Essentially I want to write a statement to identify if the customer has had a completed transaction after having had a failed transaction sometime before. So in this example, customer 1 and customer 2 would be satisfy this. Assume that there is an added timestamp column next to transaction success.

The resulting table should look like this:

CodePudding user response：

Assuming that is not important if the Complete was after or prior to the Cancellation, you can LEFT JOIN the table with a subquery that only takes the completes. If the result is NULL, then is not have a complete state. Otherwise is true.

As you don't provide your DBMS (Please read: Why should I "tag my RDBMS"?) we take in consideration IFNULL but this can change in other DBMS: https://www.w3schools.com/sql/sql_isnull.asp

SELECT
yt.customer_id,
IFNULL(completes.customer_id,'false','true') as returning_success
FROM
yourtable yt
LEFT JOIN 
(
SELECT 
customer_id
FROM
yourTable
WHERE transaction_success = 'Complete') completes
ON completes.customer_id = yt.customer_id

CodePudding user response：

Didn't really understand what means 'before' cause you don't have any coloumn to order you data. If you jast need customers that had had both succesfull and faild transactions, you can use this:

select customer_id, case when  sum(case 
                                    when transaction='Faild'
                                    then 1 
                                    else 0 end)>0
                                and 
                                sum(case 
                                    when transaction='Complete'
                                    then 1 
                                    else 0 end)>0
                          then 'True'
                          else 'False' end 
                    returning_success
from table_
group by customer_id

If you actually do have some timestamp column, you can use this:

select nvl(c.customer_id, f.customer_id) customer_id
     , case when last_complete_time is null 
                    or first_fail_time is null 
                    or first_fail_time>last_complete_time 
            then 'False'
            else 'True' end
       returning_success
from (
    select customer_id, max(time_) last_complete_time
    from table_
    group by customer_id
    where transaction='Complete'
    ) c
full join (
    select customer_id, min(time_) first_fail_time
    from table_
    group by customer_id
    where transaction='Fail' 
    ) f on c.customer_id=f.customer_id

You also can use this query to filter all True cases and then just union or join the rest

    select f.customer_id, 'True'
    from (
        select customer_id, max(time_) last_complete_time
        from table_
        group by customer_id
        where transaction='Complete'
        ) c
    join (
        select customer_id, min(time_) first_fail_time
        from table_
        group by customer_id
        where transaction='Fail'
        ) f on c.customer_id=f.customer_id
    where first_fail_time<last_complete_time