Probability Distribution calculation in sql

Question

Hello,

Is there any way to get probability in sql.

Suppose in remarks column we have more loss, and i am trying to show
if we decrease distance or change ChannelSource than there is probability
for particular TrackID to have gain instead of loss.

Pardon me if it is not possible in sql

Data:

DECLARE @MYTABLE TABLE  
(  
ID	VARCHAR (50),  
distance INT,  
ChannelSource VARCHAR (50),	  
REMARKS VARCHAR (50)  
  
)  
  
INSERT @MYTABLE  
  
SELECT 'Z1G510A',719,'ThirdParty','Loss' UNION ALL  
SELECT 'B18EAE2',91,'website',' Gain' UNION ALL  
SELECT 'E116A40',451,'ThirdParty','Loss' UNION ALL  
SELECT '91H8B6C',113,'ThirdParty','Loss' UNION ALL  
SELECT 'S1Z165F',315,'ThirdParty','Loss' UNION ALL  
SELECT 'F1D1308',983,'website','Loss' UNION ALL  
SELECT '21577CA',1052,'website','Loss' UNION ALL  
SELECT 'X1FF3A9',904,'website','Gain' UNION ALL  
SELECT 'S1V7021',621,'website','Gain'   

Thank You so Much

Answer 1

Hi @Rosalina5
How about this query:

DECLARE @MYTABLE TABLE  
 (  
 ID    VARCHAR (50),  
 distance INT,  
 ChannelSource VARCHAR (50),      
 REMARKS VARCHAR (50)      
 )  
 INSERT @MYTABLE      
 SELECT 'Z1G510A',719,'ThirdParty','Loss' UNION ALL  
 SELECT 'B18EAE2',91,'website',' Gain' UNION ALL  
 SELECT 'E116A40',451,'ThirdParty','Loss' UNION ALL  
 SELECT '91H8B6C',113,'ThirdParty','Loss' UNION ALL  
 SELECT 'S1Z165F',315,'ThirdParty','Loss' UNION ALL  
 SELECT 'F1D1308',983,'website','Loss' UNION ALL  
 SELECT '21577CA',1052,'website','Loss' UNION ALL  
 SELECT 'X1FF3A9',904,'website','Gain' UNION ALL  
 SELECT 'S1V7021',621,'website','Gain'   
 SELECT * FROM @MYTABLE  
  
;WITH CTE AS   
(  
 SELECT *,SUM(CASE WHEN REMARKS='Loss' THEN 1 ELSE 0 END)OVER(PARTITION BY 1)*1.0/COUNT(REMARKS)OVER(PARTITION BY 1) AS Probability_of_Loss   
 FROM @MYTABLE  
)  
UPDATE CTE  
SET ChannelSource='website'  
   ,REMARKS = CASE WHEN Probability_of_Loss > 0.5 THEN 'Gain' ELSE 'Loss' END   
WHERE ID = 'Z1G510A'  
  
SELECT * FROM @MYTABLE  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.