44 lines
1.4 KiB
Transact-SQL
44 lines
1.4 KiB
Transact-SQL
CREATE FUNCTION [dbo].[API_KnokserRanks]
|
|
(
|
|
@userId bigint
|
|
)
|
|
RETURNS @rankFactors TABLE
|
|
(
|
|
RankId int,
|
|
Code nchar(10),
|
|
Measure DECIMAL (22, 8)
|
|
)
|
|
AS
|
|
BEGIN
|
|
DECLARE @HasTechnical int, @HasFundamental int, @Successes int, @Published int, @Purchasers int, @Reknoks int , @Followers int, @DistTarget DECIMAL (22, 8), @Profit DECIMAL (22, 8)
|
|
|
|
Select
|
|
@HasTechnical = Count(k.HasTechnical),
|
|
@HasFundamental = Count(k.HasFundamental),
|
|
@Successes = Count( s.HasResult),
|
|
@Published = Count(case when s.IsPublished = 1 then 1 ELSE NULL END),
|
|
@Purchasers = Count(distinct p.UserId ),
|
|
@Reknoks = 0,
|
|
@Followers = Count(follows.UserId),
|
|
@DistTarget = AVG(case when s.HasResult = 1 then k.DistanceFromTarget ELSE NULL END),
|
|
@Profit = AVG(k.PotentialProfitValue)
|
|
from Users u
|
|
left join Signals k on k.CreatorUserId = u.UserId
|
|
left join [dbo].[UserKnoks] p on p.KnokId=k.KnokId
|
|
left join [dbo].[UserFollows] follows on follows.FollowUserId=follows.UserId
|
|
left join [dbo]._KnokStatuses s on s.KnokStatusId = k.KnokStatusId
|
|
WHERE u.UserId = @userId
|
|
group by u.UserId
|
|
INSERT @rankFactors (RankId, Code, Measure) VALUES
|
|
(2, 'HasTA', @HasTechnical),
|
|
(2, 'HasFA', @HasFundamental),
|
|
(2, 'Successes', @Successes),
|
|
(2, 'Published', @Published),
|
|
(2, 'Purchasers', @Purchasers),
|
|
(2, 'Reknoks', @Reknoks),
|
|
(2, 'Followers', @Followers),
|
|
(2, 'DistTarget', @DistTarget),
|
|
(2, 'Profit', @Profit)
|
|
RETURN
|
|
END
|