Client Data

Bid Data

Call Data

Tables Picture

Fiddle Link

Hello, apologizing in advance for struggling to articulate this. I have 3 tables with client data, bid data, and call data.

Client data contains the individual client and the account they belong to. Bid data is ranked by who I want to sell to and call data is who won the bid (not always rank 1).

I want to find the marginal difference in revenue from the winner of the bid (in the call table) and the rank below them (in the bid table) but I'm unsure of how to go about matching the record in the call data table to the bid data table record, finding the record below (or if there is none) and subtracting that revenue by the revenue won to get the margin. Any guidance would be greatly appreciated.

The desired output is as follows:

CodePudding user response：

i think something like this should work (didn't verify it on tables so there might be a little bit of name correction necessary). Hoping i got your problem right...

-- get rank of winners => Each row is winner per bet, that was engaged in (1:N)
WITH RankWinner AS (
    SELECT DISTINCT 
         Winner.ClientId
        ,Winner.call_attempt_id AS BidGroupId
        ,Bid.rank               AS Rank
        ,Winner.rev             AS Revenue
    FROM       ClientData AS Winner 
    INNER JOIN BidData    AS Bid    ON ClientData.call_attempt_id = BidData.call_attempt_id
    WHERE Winner.rev = Bid.rev_per_attempt
)

-- get "next best bets" per winner => Each row is bet per Winner
WITH MarginalRevenue AS (
    SELECT
         RankWinner.ClientId
        ,RankWinner.BidGroupId 
        ,RankWinner.Revenue
        ,RankWinner.Revenue - Bid.Revenue AS MarginalRevenue
    FROM      RankWinner
    LEFT JOIN (
        -- I'm using a Subquery here to get rid of duplicate ranks, just in case they're not unique.
        SELECT DISTINCT
            rank             AS Rank
            ,call_attempt_id AS GroupId
            ,rev_per_attempt AS Revenue
        FROM BidData)        AS Bid ON  RankWinner.Rank - 1   = Bid.Rank        -- Find the next lower rank from Winner rank
                                    AND RankWinner.BidGroupId = Bid.GroupId; 
)

-- aggregate on client
SELECT
     ClientId
    ,SUM(Revenue)         AS TotalRevenue
    ,SUM(MarginalRevenue) AS TotalMarginalRevenue
    ,AVG(MarginalRevenue) AS MarginalRevenue
    ,COUNT(BidGroupId)    AS NumberOfBids
FROM MarginalRevenue
GROUP BY ClientId