complex calculation in sql server 2019

Question

I am trying to write sql code to find out which channelsource generate maximum profit
and based on the calculation (customer_price-vehicle_cost), Ranking for profit in the alias column Remarks
contain value like Top Highest profit , or 2nd highest or 3rd highest.

sample data

DECLARE @LOCAL_TABLEVARIABLE TABLE  
(  
TRACKID	VARCHAR (50),  
ChannelSource VARCHAR (50),	  
created_date VARCHAR (50),	  
delivered_date VARCHAR (50),	  
actual_arrival VARCHAR (50),  
requested_arrival VARCHAR (50),	  
customer_price FLOAT,	  
vehicle_cost FLOAT  
)  
  
INSERT @LOCAL_TABLEVARIABLE  
  
SELECT 'Z1G510A','sales', '2021-12-09 09:16:09+00','2021-12-15T02:40:39+01:00','2021-12-15 1:27:56','2021-12-15 0:45:00',286.4,320 UNION ALL  
SELECT 'B18EAE2','website', '2022-01-21 13:26:04+00','2022-01-26T12:47:39+01:00','2022-01-26 10:00:00','2022-01-26 10:00:00',1021,1000 UNION ALL  
SELECT 'E116A40','website','2022-01-07 13:33:21+00','2022-01-17T10:09:01+01:00','2022-01-14 7:00:00','2022-01-14 7:00:00',585,650 UNION ALL  
SELECT '91H8B6C','website','2022-02-09 11:47:09+00','2022-02-11T09:30:48+01:00','2022-02-11 8:03:21','2022-02-11 17:00:00',201,204 UNION ALL  
SELECT 'S1Z165F','sales', '2021-12-02 08:14:42+00','2021-12-10T10:28:19+01:00','2021-12-10 8:00:00','2021-12-10 13:00:00',500.8,650 UNION ALL  
SELECT 'F1D1308','website', '2021-12-16 11:05:12+00','2021-12-23T13:55:31+01:00','2021-12-23 8:54:14','2021-12-22 16:00:00',0.8,1 UNION ALL  
SELECT '21577CA','website', '2021-12-08 12:05:18+00','2021-12-22T15:01:05+01:00','2021-12-22 8:50:00','2021-12-23 16:00:00',960,1200 UNION ALL  
SELECT 'X1FF3A9','website','2022-01-27 13:44:48+00','2022-01-31T12:30:40+01:00','2022-01-31 12:09:20','2022-01-31 14:00:00',495,330 UNION ALL  
SELECT 'S1V7021','website','2022-02-18 13:04:56+00','2022-02-22T12:18:28+01:00','2022-02-22 10:52:39','2022-02-23 16:04:00',730,680 UNION ALL  
SELECT 'P1J9B33','website','2021-11-24 09:55:07+00','2021-12-07T10:33:04+01:00','2021-12-06 16:00:02','2021-12-06 16:00:00',0.8,1 UNION ALL  
SELECT 'U1Q436B','referral','2022-01-21 09:30:22+00','2022-01-24T16:01:33+01:00','2022-01-24 13:00:27','2022-01-24 12:00:00',2145,1626 UNION ALL  
SELECT 'U1R9C50', 'website', '2022-01-17 09:33:53+00','2022-02-04T14:41:36+01:00','2022-02-04 11:33:29','2022-02-04 15:00:00',3300,2200 UNION ALL  
SELECT '61C4EC4','sales', '2021-12-02 09:11:49+00','2021-12-10T02:01:06+01:00','2021-12-09 21:30:20','2021-12-10 0:15:00',254.4,310 UNION ALL  
SELECT 'N1W909D','website','2022-02-02 09:50:21+00','2022-02-21T15:03:51+01:00','2022-02-15 8:33:21','2022-02-16 16:00:00',1290,860 UNION ALL  
SELECT 'B1X5B6C','website','2021-12-09 09:46:06+00','2021-12-17T08:31:18+01:00','2021-12-17 7:14:12','2021-12-17 15:00:00',1096,1370 UNION ALL  
SELECT '61RC3D6','website','2021-12-13 14:49:32+00','2021-12-22T12:07:31+01:00','2021-12-16 7:00:48','2021-12-16 21:00:00',445.6,557 UNION ALL  
SELECT '91T1FDF','website','2022-02-08 08:40:12+00','2022-02-09T13:59:45+01:00','2022-02-09 13:57:13','2022-02-09 15:00:00',420,280 UNION ALL  
SELECT 'N1H4578','website','2021-12-07 09:28:58+00','2022-01-03T14:41:53+01:00','2021-12-28 12:00:20','2021-12-28 16:00:00',384,480 UNION ALL  
SELECT 'P156CE5','website','2022-02-16 08:38:44+00','2022-02-18T12:59:19+01:00','2022-02-18 7:30:05','2022-02-18 21:00:00',500,500 UNION ALL  
SELECT '61U06CE','website','2021-12-31 09:46:21+00','2022-01-04T14:10:22+01:00','2022-01-04 11:24:53','2022-01-04 16:00:00',336,195 UNION ALL  
SELECT 'Y1PA1FC','website','2021-12-01 11:54:30+00','2021-12-03T14:01:59+01:00','2021-12-03 7:01:47','2021-12-03 16:00:00',760,950 UNION ALL  
SELECT '316381B','sales','2022-01-25 12:00:20+00','2022-02-01T15:41:49+01:00','2022-02-01 15:00:27','2022-02-01 14:45:00',672,300 UNION ALL  
SELECT 'H1V067F','website','2022-01-19 12:22:11+00','2022-02-04T13:05:24+01:00','2022-02-03 13:11:06','2022-02-03 16:00:00',1117.5,745 UNION ALL  
SELECT 'X1LC768','website','2021-12-06 14:17:16+00','2021-12-09T12:57:54+01:00','2021-12-09 11:30:53','2021-12-08 18:00:00',480,670 UNION ALL  
SELECT '81P3D0D','website','2022-02-15 10:18:47+00','2022-02-17T08:43:16+01:00','2022-02-16 14:00:04','2022-02-16 15:00:00',3448.5,2299 UNION ALL  
SELECT '71J3EEA','website','2022-01-03 09:04:10+00','2022-01-05T11:30:29+01:00','2022-01-05 7:45:22','2022-01-05 8:00:00',336,195 UNION ALL  
SELECT 'K1QAB19','website','2022-02-04 14:58:06+00','2022-02-09T08:17:34+01:00','2022-02-08 15:32:29','2022-02-08 18:00:00',584,600 UNION ALL  
SELECT 'Y1YE019','website','2022-01-19 13:28:51+00','2022-01-22T07:35:27+01:00','2022-01-22 5:40:00','2022-01-22 6:00:00',730,800 UNION ALL  
SELECT 'A1M9DE2','website','2022-01-17 12:14:12+00','2022-01-28T13:11:58+01:00','2022-01-28 12:43:37','2022-01-27 16:00:00',5535,5535  
  
SELECT * FROM @LOCAL_TABLEVARIABLE  

Please help me how to do that using sql

I am using SQL SERVER 2019  

Answer 1

Check a complex query:

;  
with P as  
(  
    select ChannelSource, sum(customer_price - vehicle_cost) as profit  
    from @LOCAL_TABLEVARIABLE  
    group by ChannelSource  
),  
R as  
(  
    select ChannelSource, profit, rank() over (order by profit desc) r  
    from P  
)  
select ChannelSource, profit,   
    choose(r, 'Top Highest', '2nd highest', '3rd highest') as Remark  
from R  
where r <= 3  
order by r