I have a table similar to below .

Now I want to create a view using the above table where I need to create a new column say Success count in the view along with this table values when the customer type is VIP I need to check the subsequent session id(column B) with Same A Id value to see if the ticket status is Success then the Success Count column should be filled as 1 as the status is Success if not I have to set to 0 . How could I achieve this . I am new to complex SQL .

Output view should be like below . The count is 1 because ticket status is success and has previous row as VIP in Column C for the Same A column Id.

CodePudding user response：

This is a simple solution based on count window function. Pay attention that the count is equal or larger than 1 in case there are multiple vips per A ID.

select t.*
      ,case when count(case "C customer Type" when 'vip' then 1 end) over (partition by "A Id" order by  "B session Id") >= 1
       and  "D ticket status" = 'Success' 
       then 1 else 0 end as E
from   t

Fiddle

CodePudding user response：

You may try the following:

Create View VIP_View As
Select *, Case When D.ticket_status='Success' And D.has_vip =1 Then 1 Else 0 End As E
From 
(
  Select *, MAX(Case When customer_type='vip' Then 1 Else 0 End) Over (Partition By Id) has_vip
  From datatable
) D ;

Or you may write it as:

Create View VIP_View As
Select Id, session_Id, customer_type, ticket_status, 
       Case 
         When ticket_status='Success' And MAX(Case When customer_type='vip' Then 1 Else 0 End) Over (Partition By Id) =1 
         Then 1 
         Else 0 
       End As E
From datatable;

CodePudding user response：

It looks to be straight-forward, using CASE statement for the additional column in view:

create or replace my_view as  
select *,
case 
    when ID in (select distinct ID from data 
                where customer_type='vip') 
                and ticket_status='Success' 
        then 1
    else 0
end col_E
 from data;