Challenging SQL Query

1

I need a SQL query that returns those recipients that have received at least 1020 in at most any three amounts:

SENDER RECIPIENT DATE AMOUNT
Smith Macy's 10/12/2015 200
Smith Target 9/11/2010 1020
Smith TJ Maxx 2/15/2009 512
Williams TJ Maxx 4/5/2018 100
Williams TJ Maxx 5/28/2016 10
Brown TJ Maxx 7/30/2010 500
Johnson Macy's 12/8/2018 400
Johnson Macy's 3/14/2019 400
Johnson Macy's 9/5/2014 200

QUERY RESULT SHOULD BE:

RECIPIENT
TJ Maxx
Target

I am struggling to get this one accomplished.

Thanks for looking.

Kevin

2

why those two store names only?

3

I have more data, but TJ Maxx and Target only because those two are the only ones that meet the condition. Macy's doesn't hit the threshold for 1020 in at most three amounts (it would take four amounts).

4

Maybe this might be of help?

create table #sample(SENDER nvarchar(150), 
RECIPIENT nvarchar(150),DATE_ date, AMOUNT money)
insert into #sample	
select 'Smith', 'Macy''s', '10/12/2015', 200 union
select 'Smith', 'Target', '9/11/2010', 1020 union
select 'Smith', 'TJ Maxx', '2/15/2009', 512 union
select 'Williams', 'TJ Maxx', '4/5/2018', 100 union
select 'Williams', 'TJ Maxx', '5/28/2016', 10 union
select 'Brown', 'TJ Maxx', '7/30/2010', 500 union
select 'Johnson', 'Macy''s', '12/8/2018', 400 union
select 'Johnson', 'Macy''s', '3/14/2019', 400 union
select 'Johnson', 'Macy''s', '9/5/2014', 200 

;with src
as
(
select sum(s.AMOUNT) _sum, s.RECIPIENT
from #sample s
join (
		select  RECIPIENT
		  From #sample
  
		  group by RECIPIENT
		  having count(RECIPIENT) >= 3
  ) x on s.RECIPIENT = x.RECIPIENT
  group by s.RECIPIENT
)
select * 
from src
where _sum >= 1020

drop table #sample
5

Thanks yosiasz, but Macy's was returned when it cannot because of 4 amounts - I can use only 3 max.

6

I think this is what you want

Cribbing @ [yosiasz] set up code, I've come up with the following, it does three self joins, each join excluding previously joined rows to work out all the possible totals of three combinations, and then getting the distinct recipient

use tempdb
go

drop table if exists #sample

create table #sample(
	ID INTEGER IDENTITY(1,1)
	, SENDER nvarchar(150)
	, RECIPIENT nvarchar(150)
	, DATE_ date
	, AMOUNT money)
insert into #sample	
select 'Smith', 'Macy''s', '10/12/2015', 200 union
select 'Smith', 'Target', '9/11/2010', 1020 union
select 'Smith', 'TJ Maxx', '2/15/2009', 512 union
select 'Williams', 'TJ Maxx', '4/5/2018', 100 union
select 'Williams', 'TJ Maxx', '5/28/2016', 10 union
select 'Brown', 'TJ Maxx', '7/30/2010', 500 union
select 'Johnson', 'Macy''s', '12/8/2018', 400 union
select 'Johnson', 'Macy''s', '3/14/2019', 400 union
select 'Johnson', 'Macy''s', '9/5/2014', 200 

; with matrix as (
	SELECT
		one.id, one.RECIPIENT
		, one.amount as a1, two.amount as a2, three.amount as a3
		, ISNULL(ONE.AMOUNT, 0) + ISNULL(TWO.AMOUNT, 0) + ISNULL(THREE.AMOUNT, 0) AS TOTAL
	FROM #sample ONE
	LEFT OUTER JOIN #sample TWO 
		ON ONE.RECIPIENT = TWO.RECIPIENT
		AND ONE.ID <> TWO.ID
	LEFT OUTER JOIN #sample THREE
		ON TWO.RECIPIENT = THREE.RECIPIENT
		AND TWO.ID <> THREE.ID
		AND ONE.ID <> THREE.ID
)
--select * from matrix
select DISTINCT RECIPIENT
from matrix 
WHERE TOTAL >= 1020
7

Here is another alternative:

WITH CTE AS (
SELECT RECIPIENT
      , AMOUNT
      , ROW_NUMBER() OVER (PARTITION BY RECIPIENT ORDER BY AMOUNT DESC) as RowNum
FROM #sample
)
SELECT RECIPIENT, SUM(AMOUNT) as SumAmount
FROM CTE
WHERE RowNum <= 3
GROUP BY RECIPIENT
HAVING SUM(AMOUNT) >= 1020

Disclaimer: have no database at hand, may contain typos.

8

having count(RECIPIENT) <= 3

Or the other folks answers should do it

9

Yosiasz,

I don't think that solution in line with the requirement:

But maybe I misinterpreted the description.

1 Like
10

hi Wim

I was trying to do this .. and came up .....solution

I saw that it looks very similar to yours

please click arrow to the left for SQL 
;WITH cte AS
(
   SELECT *
          ,ROW_NUMBER() OVER (PARTITION BY recipient ORDER BY amount DESC) AS rn
   FROM data 
), cte_fin as 
(
    SELECT 
         *
    FROM 
       cte
    WHERE 
        rn  <= 3 
)
select 
    recipient
   ,sum(amount) 
from 
   cte_fin 
group by 
    recipient 
having 
   sum(amount) >= 1020

image

1 Like
11

Hi Harish,

That was my first idea :slight_smile:
It evolved while I was coding.

12

Hi Wim

At first i did not have any clue how to do this !!!!!
I thought about it and thought about it .. nothing .. so .. left it alone

After 2 hrs of doing others things ...this idea came to me ..

Happens all the time ..
:slight_smile: