I'm working on this query and would like to use the cumulative distribution or percent rank function to provide me with cumulative distribution and the percent rank for field 'leads distribution'. however, it appears that when the decile value is populated it doesn't give me the right number for that decile for example the LeadGrouping record 'FLJeffersonCollateral' should have a decile of 1 but is showing a decile of 2. Can you help what am I doing wrong? Below is the query.
Select c.LeadGrouping
, c.StateID
, c.County
, c.Media_Type
, Sum(c.Leads) as 'Leads'
, Sum(isnull(c.CCPEnroll,0)) as 'CCPEnroll'
, c.CloseRate
--, row_number() over(partition by c.Field1 order by c.CloseRate desc) as line_number
, sum(mx.MaxLeads) as 'MaxLeads' --ALTER TO MAX LEADS OVERALL --sum(mx.MaxLeads)
, CONVERT(DECIMAL(10,10), isnull(Sum(c.leads),0) / convert(numeric, isnull(sum(mx.MaxLeads),0))) as 'LeadDistribution' --CONFIRM TOTAL SUMS TO 100%
, PERCENT_RANK () over (order by Convert (Decimal(10,10), (isnull(Sum(c.leads),0) / convert(numeric, isnull(sum(mx.MaxLeads),0))))) as 'percent rank'
From (Select 'A' as 'Field1'
, b.LeadGrouping
, b.StateID
, b.County
, b.Media_Type
, Sum(b.Leads) as 'Leads'
, Sum(b.CCPEnroll) as 'CCPEnroll'
, CONVERT(DECIMAL(5,2), isnull(Sum(b.CCPEnroll),0) / convert(numeric, isnull(Sum(b.Leads),0))) as 'CloseRate'
From (Select a.LeadGrouping
, a.StateID
, a.County
, a.Media_Type
, Sum(a.Lead) as 'Leads'
, Sum(a.CCPEnroll) as 'CCPEnroll'
From (Select distinct al.LeadID
, al.Interaction_ID
, al.createddate
, al.appointmentdate
, al.C2CExp
, Case When al.EnrollMonth is not null Then DateDiff(day, al.createddate, al.AppDate)
Else DateDiff(day, al.createddate, GETDATE()) End as 'LeadTenureInDays'
, Count(distinct i.interaction_id) as 'LeadInteractionCount'
, l.lead_interactionsource__c
, al.MarketID
, left(al.state_county_key,2) as 'StateID'
, al.County
, al.Media_Type
, al.SegmentName
, al.Correctcampaign
, c.name as 'CampaignName'
, c.lead_program__c
, l.lead_disposition__c
, al.CCPEnroll
, al.PDPEnroll
, al.AppDate
, al.EnrollMonth
, al.writingagentid
, p.ProducerType
, al.Lead
, case When f.fipscode is null Then 'Not In CCP Footprint'
When f.FIPSCode is not null Then 'In CCP Footprint'
else 'ERROR' End as 'Footprint'
, CONCAT(left(al.state_county_key,2),al.county, al.Media_Type) as 'LeadGrouping'
From rknow.dbo.allleadstemp2 al
Left Join rknow.dbo.campaign_sfdc_datalake c on al.correctcampaign = c.id
Left Join rknow.dbo.lead_sfdc_datalake l on al.leadid = l.id
Left Join rknow.dbo.producerstructure_datalake p on al.writingagentid = p.producerid and al.createddate >= p.effectivestartdate and al.createddate <= DateAdd(day, 1, p.effectiveenddate)
Left Join [RKnow].[rpt].[interaction] i on al.leadid = i.lead_id
Inner Join rknow.dbo.fipsspc f on al.fips = f.fipscode --2022 CCP County Level Footprint
Where al.C2CExp >= DateAdd(day,1,getdate())
and al.lead_type = 'ccp' --HD 12/04/2020: Added criteria filter to exclude CrossSell leads
Group by al.LeadID
, al.Interaction_ID
, al.C2CExp
, al.createddate
, al.MarketID
, al.state_county_key
, al.County
, al.Media_Type
, al.SegmentName
, al.Correctcampaign
, al.appointmentdate
, c.name
, c.lead_program__c
, l.lead_disposition__c
, al.CCPEnroll
, al.PDPEnroll
, al.EnrollMonth
, al.writingagentid
, p.ProducerType
, l.lead_interactionsource__c
, al.AppDate
, f.fipscode
, al.Lead) a
Group by a.LeadGrouping
, a.Media_Type
, a.StateID
, a.County) b
Group by b.LeadGrouping
, b.StateID
, b.County
, b.Media_Type) c
Left Join (Select distinct 'A' as 'Field1', sum(mx.lead) as 'MaxLeads'
From(Select distinct al.LeadID
, al.Interaction_ID
, al.createddate
, al.appointmentdate
, al.C2CExp
, Case When al.EnrollMonth is not null Then DateDiff(day, al.createddate, al.AppDate)
Else DateDiff(day, al.createddate, GETDATE()) End as 'LeadTenureInDays'
, Count(distinct i.interaction_id) as 'LeadInteractionCount'
, l.lead_interactionsource__c
, al.MarketID
, left(al.state_county_key,2) as 'StateID'
, al.County
, al.Media_Type
, al.SegmentName
, al.Correctcampaign
, c.name as 'CampaignName'
, c.lead_program__c
, l.lead_disposition__c
, al.CCPEnroll
, al.PDPEnroll
, al.AppDate
, al.EnrollMonth
, al.writingagentid
, p.ProducerType
, al.Lead
, case When f.fipscode is null Then 'Not In CCP Footprint'
When f.FIPSCode is not null Then 'In CCP Footprint'
else 'ERROR' End as 'Footprint'
, CONCAT(left(al.state_county_key,2),al.county, al.Media_Type) as 'LeadGrouping'
From rknow.dbo.allleadstemp2 al
Left Join rknow.dbo.campaign_sfdc_datalake c on al.correctcampaign = c.id
Left Join rknow.dbo.lead_sfdc_datalake l on al.leadid = l.id
Left Join rknow.dbo.producerstructure_datalake p on al.writingagentid = p.producerid and al.createddate >= p.effectivestartdate and al.createddate <= DateAdd(day, 1, p.effectiveenddate)
Left Join [RKnow].[rpt].[interaction] i on al.leadid = i.lead_id
Inner Join rknow.dbo.fipsspc f on al.fips = f.fipscode --2022 CCP County Level Footprint
Where al.C2CExp >= DateAdd(day,1,getdate())
and al.lead_type = 'ccp' --HD 12/04/2020: Added criteria filter to exclude CrossSell leads
Group by al.LeadID
, al.Interaction_ID
, al.C2CExp
, al.createddate
, al.MarketID
, al.state_county_key
, al.County
, al.Media_Type
, al.SegmentName
, al.Correctcampaign
, al.appointmentdate
, c.name
, c.lead_program__c
, l.lead_disposition__c
, al.CCPEnroll
, al.PDPEnroll
, al.EnrollMonth
, al.writingagentid
, p.ProducerType
, l.lead_interactionsource__c
, al.AppDate
, f.fipscode
, al.Lead) mx) mx on mx.Field1 = c.Field1
Group by c.LeadGrouping
, c.StateID
, c.County
, c.Media_Type
, c.CloseRate
, c.Field1
