# Dense_Rank with Gaps and Islands Problem–Another Solution

```

CREATE TABLE #CustomerOffer ( id INT, qid INT, Referralid INT, Offerid INT, OfferAmount INT)

INSERT INTO #CustomerOffer VALUES ( 1,421,513452,101,10000)
INSERT INTO #CustomerOffer VALUES ( 2,421,513452,102,10001)
INSERT INTO #CustomerOffer VALUES ( 3,421,513452,103,10002)
INSERT INTO #CustomerOffer VALUES ( 4,421,513452,104,10003)
INSERT INTO #CustomerOffer VALUES ( 5,421,513452,105,10004)

INSERT INTO #CustomerOffer VALUES ( 6,421,5290373,3234,10010)
INSERT INTO #CustomerOffer VALUES ( 7,421,5290373,3284,100011)
INSERT INTO #CustomerOffer VALUES ( 8,421,5290373,3923,100022)

INSERT INTO #CustomerOffer VALUES ( 9,422,513454,1111,100101)
INSERT INTO #CustomerOffer VALUES ( 10,422,513454,1112,1000111)
INSERT INTO #CustomerOffer VALUES ( 11,422,513454,1113,1000222)

select Id, qid,ReferralId, OfferId, OfferAmount,
dense_rank() over(order by Cast(qid as binary(4))+ Cast(ReferralId as binary(4))) as ReferralRnk
,ROW_NUMBER() over (partition by qid, referralId order by OfferId) as PgmNbr

From #CustomerOffer
order by Id

drop table #CustomerOffer
```